Bulk Data Import

[stdavis]

From SOW:

[stdavis]

I thought that we agreed to have DWR give us an example of the most common format and that would become the template. Then we would make an ETL tool that would ingest data from the template into the new DB. Then they would be in charge of converting all of their data (if it needed converting) into the template for upload.

[ToddVolkening]

There is enough variation among regional formats that using region based templates will prove difficult. A more efficient course of action would be for AGRC to create a flat table bulk upload tool. I envision an Excel-like format with tabbed worksheets representing data for each of the tabs depicted in the online data entry tool. (e.g. Location, Method, Catch, Habitat). Thanks. Craig and Todd

[brigsz]

Don't you already have data in spreadsheets? How will you be getting that data into a NEW spreadsheet? Or is this tool just for paper records that someone will type into a spreadsheet for upload to the database?

[ToddVolkening]

Regional biologist will be cutting and pasting from existing spread sheets OR transcribing from paper data sheets not already in electronic format. Hopefully that clarifies things some. Thanks. Craig and Todd

[steveoh]

Are we adding an extra hurdle then by creating yet another spreadsheet/template for a not so "bulk"/"automated" import? Would it be simpler to use the application to enter the data?

[steveoh]

@ToddVolkening any new thoughts on scrapping the bulk import tool?

[ericedgley]

@steveoh No, the bulk import tool can't be scrapped. It is our #2 priority item behind the admin functions. But, we (DWR) will provide a spreadsheet to use for an ETL/ bulk upload process. Thanks.

[brigsz]

@ericedgley

Eric, this seems to have many contradictory statements, How can we come to a consensus on the road ahead for this one?

[ericedgley]

Hi Matt, Craig is currently working on a spreadsheet for this. So hopefully we dont have confusion here anymore. This process for us helps standardize the data collected across our regions. Hows that?

[brigsz]

@toddVolkening Yes, okay, so who will take existing spreadsheets and put them in this new format to ingest into the DB?

On Wed, Apr 11, 2018 at 3:48 PM, ericedgley notifications@github.com wrote:

Hi Matt, Craig is currently working on a spreadsheet for this. So hopefully we dont have confusion here anymore. This process for us helps standardize the data collected across our regions. Hows that?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/agrc/electrofishing/issues/82#issuecomment-380606656, or mute the thread https://github.com/notifications/unsubscribe-auth/AGA6n2bkcwaUq3bpAh500MsetGhcYxTNks5tnnpCgaJpZM4RYCmH .

[ericedgley]

Craig Walker is working on this.

On Wed, Apr 11, 2018 at 4:04 PM Matt Peters notifications@github.com wrote:

@toddVolkening Yes, okay, so who will take existing spreadsheets and put them in this new format to ingest into the DB?

On Wed, Apr 11, 2018 at 3:48 PM, ericedgley notifications@github.com wrote:

Hi Matt, Craig is currently working on a spreadsheet for this. So hopefully we dont have confusion here anymore. This process for us helps standardize the data collected across our regions. Hows that?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82#issuecomment-380606656 , or mute the thread < https://github.com/notifications/unsubscribe-auth/AGA6n2bkcwaUq3bpAh500MsetGhcYxTNks5tnnpCgaJpZM4RYCmH

.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/agrc/electrofishing/issues/82#issuecomment-380610454, or mute the thread https://github.com/notifications/unsubscribe-auth/AkP4jxtc14XDh4HzvRNgssnrXFdGGWXpks5tnn3kgaJpZM4RYCmH .

--

Eric Edgley GIS Administrator Utah Division of Wildlife Resources 1594 West North Temple, Suite 2110 Salt Lake City, UT 84114-6301 801-538-4765 ericedgley@utah.gov

[brigsz]

The bigger question i am trying to work out is what is AGRCs role in this. Just trying to plan our time on the priorities

On Apr 11, 2018, at 6:16 PM, ericedgley notifications@github.com wrote:

Craig Walker is working on this.

On Wed, Apr 11, 2018 at 4:04 PM Matt Peters notifications@github.com wrote:

@toddVolkening Yes, okay, so who will take existing spreadsheets and put them in this new format to ingest into the DB?

On Wed, Apr 11, 2018 at 3:48 PM, ericedgley notifications@github.com wrote:

Hi Matt, Craig is currently working on a spreadsheet for this. So hopefully we dont have confusion here anymore. This process for us helps standardize the data collected across our regions. Hows that?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82#issuecomment-380606656 , or mute the thread <

https://github.com/notifications/unsubscribe-auth/AGA6n2bkcwaUq3bpAh500MsetGhcYxTNks5tnnpCgaJpZM4RYCmH

.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/agrc/electrofishing/issues/82#issuecomment-380610454, or mute the thread < https://github.com/notifications/unsubscribe-auth/AkP4jxtc14XDh4HzvRNgssnrXFdGGWXpks5tnn3kgaJpZM4RYCmH

.

--

Eric Edgley GIS Administrator Utah Division of Wildlife Resources 1594 West North Temple, Suite 2110 Salt Lake City, UT 84114-6301 801-538-4765 ericedgley@utah.gov

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/agrc/electrofishing/issues/82#issuecomment-380634724, or mute the thread https://github.com/notifications/unsubscribe-auth/AGA6n0-2gk99GlnfAfZhjfXIeXNH9S49ks5tnpzBgaJpZM4RYCmH .

[ericedgley]

I see that AGRC's role in this will be to facilitate an automated ETL process of large spreadsheets of legacy data. We plan have our staff format their data into THE template that Craig is working on, then when that is uploaded into the app, it can assign the right data pieces into the right tables in the DB.

Eric Edgley GIS Administrator Utah Division of Wildlife Resources 1594 West North Temple, Suite 2110 Salt Lake City, UT 84114-6301 801-538-4765 ericedgley@utah.gov

On Wed, Apr 11, 2018 at 6:39 PM, Matt Peters notifications@github.com wrote:

The bigger question i am trying to work out is what is AGRCs role in this. Just trying to plan our time on the priorities

On Apr 11, 2018, at 6:16 PM, ericedgley notifications@github.com wrote:

Craig Walker is working on this.

On Wed, Apr 11, 2018 at 4:04 PM Matt Peters notifications@github.com wrote:

@toddVolkening Yes, okay, so who will take existing spreadsheets and put them in this new format to ingest into the DB?

On Wed, Apr 11, 2018 at 3:48 PM, ericedgley notifications@github.com wrote:

Hi Matt, Craig is currently working on a spreadsheet for this. So hopefully we dont have confusion here anymore. This process for us helps standardize the data collected across our regions. Hows that?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82# issuecomment-380606656 , or mute the thread <

https://github.com/notifications/unsubscribe-auth/ AGA6n2bkcwaUq3bpAh500MsetGhcYxTNks5tnnpCgaJpZM4RYCmH

.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82#issuecomment-380610454 , or mute the thread < https://github.com/notifications/unsubscribe-auth/ AkP4jxtc14XDh4HzvRNgssnrXFdGGWXpks5tnn3kgaJpZM4RYCmH

.

--

Eric Edgley GIS Administrator Utah Division of Wildlife Resources 1594 West North Temple, Suite 2110 https://maps.google.com/?q=1594+West+North+Temple,+Suite+2110+%0D%0ASalt+Lake+City,+UT+84114&entry=gmail&source=g Salt Lake City, UT 84114-6301 801-538-4765 ericedgley@utah.gov

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/agrc/electrofishing/issues/82#issuecomment-380634724, or mute the thread https://github.com/notifications/unsubscribe-auth/AGA6n0- 2gk99GlnfAfZhjfXIeXNH9S49ks5tnpzBgaJpZM4RYCmH .

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/agrc/electrofishing/issues/82#issuecomment-380638302, or mute the thread https://github.com/notifications/unsubscribe-auth/AkP4j223qfQaPYEET0wi9qhB4Lfuz8ijks5tnqIzgaJpZM4RYCmH .

[brigsz]

okay, so AGRC is in charge of taking the spreadheet template created by Craig that users have loaded their data into, and programming an ETL process to put the data in the Database.

On Thu, Apr 12, 2018 at 3:40 PM, ericedgley notifications@github.com wrote:

I see that AGRC's role in this will be to facilitate an automated ETL process of large spreadsheets of legacy data. We plan have our staff format their data into THE template that Craig is working on, then when that is uploaded into the app, it can assign the right data pieces into the right tables in the DB.

Eric Edgley GIS Administrator Utah Division of Wildlife Resources 1594 West North Temple, Suite 2110 https://maps.google.com/?q=1594+West+North+Temple,+Suite+2110+%0D%0ASalt+Lake+City,+UT++84114&entry=gmail&source=g Salt Lake City, UT 84114-6301 801-538-4765 ericedgley@utah.gov

On Wed, Apr 11, 2018 at 6:39 PM, Matt Peters notifications@github.com wrote:

The bigger question i am trying to work out is what is AGRCs role in this. Just trying to plan our time on the priorities

On Apr 11, 2018, at 6:16 PM, ericedgley notifications@github.com wrote:

Craig Walker is working on this.

On Wed, Apr 11, 2018 at 4:04 PM Matt Peters notifications@github.com wrote:

@toddVolkening Yes, okay, so who will take existing spreadsheets and put them in this new format to ingest into the DB?

On Wed, Apr 11, 2018 at 3:48 PM, ericedgley notifications@github.com wrote:

Hi Matt, Craig is currently working on a spreadsheet for this. So hopefully we dont have confusion here anymore. This process for us helps standardize the data collected across our regions. Hows that?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82# issuecomment-380606656 , or mute the thread <

https://github.com/notifications/unsubscribe-auth/ AGA6n2bkcwaUq3bpAh500MsetGhcYxTNks5tnnpCgaJpZM4RYCmH

.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82# issuecomment-380610454 , or mute the thread < https://github.com/notifications/unsubscribe-auth/ AkP4jxtc14XDh4HzvRNgssnrXFdGGWXpks5tnn3kgaJpZM4RYCmH

.

--

Eric Edgley GIS Administrator Utah Division of Wildlife Resources 1594 West North Temple, Suite 2110 https://maps.google.com/?q=1594+West+North+Temple,+Suite+2110&entry=gmail&source=g https://maps.google.com/?q=1594+West+North+Temple,+Suite+ 2110+%0D%0ASalt+Lake+City,+UT+84114&entry=gmail&source=g Salt Lake City, UT 84114-6301 801-538-4765 ericedgley@utah.gov

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82#issuecomment-380634724 , or mute the thread https://github.com/notifications/unsubscribe-auth/AGA6n0- 2gk99GlnfAfZhjfXIeXNH9S49ks5tnpzBgaJpZM4RYCmH .

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82#issuecomment-380638302 , or mute the thread https://github.com/notifications/unsubscribe-auth/ AkP4j223qfQaPYEET0wi9qhB4Lfuz8ijks5tnqIzgaJpZM4RYCmH .

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/agrc/electrofishing/issues/82#issuecomment-380952744, or mute the thread https://github.com/notifications/unsubscribe-auth/AGA6n8a-Do4Ch5DLZKz1sQh7ngY87VX4ks5tn8m5gaJpZM4RYCmH .

[ericedgley]

Yes

Eric Edgley GIS Administrator Utah Division of Wildlife Resources 1594 West North Temple, Suite 2110 Salt Lake City, UT 84114-6301 801-538-4765 ericedgley@utah.gov

On Thu, Apr 12, 2018 at 3:48 PM, Matt Peters notifications@github.com wrote:

okay, so AGRC is in charge of taking the spreadheet template created by Craig that users have loaded their data into, and programming an ETL process to put the data in the Database.

On Thu, Apr 12, 2018 at 3:40 PM, ericedgley notifications@github.com wrote:

I see that AGRC's role in this will be to facilitate an automated ETL process of large spreadsheets of legacy data. We plan have our staff format their data into THE template that Craig is working on, then when that is uploaded into the app, it can assign the right data pieces into the right tables in the DB.

Eric Edgley GIS Administrator Utah Division of Wildlife Resources 1594 West North Temple, Suite 2110 https://maps.google.com/?q=1594+West+North+Temple,+Suite+2110&entry=gmail&source=g https://maps.google.com/?q=1594+West+North+Temple,+Suite+ 2110+%0D%0ASalt+Lake+City,+UT++84114&entry=gmail&source=g Salt Lake City, UT 84114-6301 801-538-4765 ericedgley@utah.gov

On Wed, Apr 11, 2018 at 6:39 PM, Matt Peters notifications@github.com wrote:

The bigger question i am trying to work out is what is AGRCs role in this. Just trying to plan our time on the priorities

On Apr 11, 2018, at 6:16 PM, ericedgley notifications@github.com wrote:

Craig Walker is working on this.

On Wed, Apr 11, 2018 at 4:04 PM Matt Peters notifications@github.com wrote:

@toddVolkening Yes, okay, so who will take existing spreadsheets and put them in this new format to ingest into the DB?

On Wed, Apr 11, 2018 at 3:48 PM, ericedgley < notifications@github.com> wrote:

Hi Matt, Craig is currently working on a spreadsheet for this. So hopefully we dont have confusion here anymore. This process for us helps standardize the data collected across our regions. Hows that?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82# issuecomment-380606656 , or mute the thread <

https://github.com/notifications/unsubscribe-auth/ AGA6n2bkcwaUq3bpAh500MsetGhcYxTNks5tnnpCgaJpZM4RYCmH

.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82# issuecomment-380610454 , or mute the thread < https://github.com/notifications/unsubscribe-auth/ AkP4jxtc14XDh4HzvRNgssnrXFdGGWXpks5tnn3kgaJpZM4RYCmH

.

--

Eric Edgley GIS Administrator Utah Division of Wildlife Resources 1594 West North Temple, Suite 2110 https://maps.google.com/?q=1594+West+North+Temple,+Suite+2110&entry=gmail&source=g https://maps.google.com/?q=1594+West+North+Temple,+Suite+ 2110&entry=gmail&source=g https://maps.google.com/?q=1594+West+North+Temple,+Suite+ 2110+%0D%0ASalt+Lake+City,+UT+84114&entry=gmail&source=g Salt Lake City, UT 84114-6301 801-538-4765 ericedgley@utah.gov

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82# issuecomment-380634724 , or mute the thread https://github.com/notifications/unsubscribe-auth/AGA6n0- 2gk99GlnfAfZhjfXIeXNH9S49ks5tnpzBgaJpZM4RYCmH .

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82# issuecomment-380638302 , or mute the thread https://github.com/notifications/unsubscribe-auth/ AkP4j223qfQaPYEET0wi9qhB4Lfuz8ijks5tnqIzgaJpZM4RYCmH .

— You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://github.com/agrc/electrofishing/issues/82#issuecomment-380952744 , or mute the thread https://github.com/notifications/unsubscribe-auth/AGA6n8a- Do4Ch5DLZKz1sQh7ngY87VX4ks5tn8m5gaJpZM4RYCmH .

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/agrc/electrofishing/issues/82#issuecomment-380954590, or mute the thread https://github.com/notifications/unsubscribe-auth/AkP4j1ZO7AX9KJ35Snj1REVX7_01soV2ks5tn8uZgaJpZM4RYCmH .

[stdavis]

@ToddVolkening: I've finished with all of the database changes that we have issues for. Note that I haven't closed any of those issues yet since I've only implemented the changes on the database side and not in the app. This is because this issue was the first priority that your group gave us in our last meeting (see #132).

Today, I wrote a script that exports all of the tables and domains from the database into a blank excel spreadsheet. It also builds excel drop downs (using data validation) for each field that has a domain. Here's the result: ElectrofishingBulkUpload.xlsx.

Please take a look and let me know if it will work for your needs.

[stdavis]

A new direction for this issue. Add a button to the catch tab to allow users to upload a spreadsheet that will populate the catch grid only. All other data will be entered by hand via the controls in the web app.

[stdavis]

Which means we'll have to make all of the updates to the app to accommodate the recent database changes.