Handling Imported Data

[padlocked17]

Good Afternoon,

I've got a DB that will require importing JSON and CSV data on a recurring basis. I will need to deal with additions, deletions, and modifications to the data every time I import it to make decisions based on how the currently imported data is being used.

Is is a better practice to keep the data that is imported completely separate via tables in the DB design or merge the data with my current structure and simply have a Key or UID that identifies the record as being from an imported source so that I can deal with changes every time I import and reconcile differences?

 

[June7]

I vote to merge, with field to identify source. Otherwise, will likely have to resort to UNION queries for output queries/reports.

 

[padlocked17]

That's exactly what I was looking at. Merge into a common table with a key back to the UID for the data or Union two tables with a possible additional table for a Master PK to reference.

 

[theDBguy]

Hmm, not sure I understand the question, but I usually import external JSON/CSV data into a temporary table for processing before committing them into a permanent table. Hope that helps give you an idea for your situation.

 

[padlocked17]

That's my workflow as well. Since I'll be importing data and managing changes to it on a daily basis, wasn't sure if a best practice was to keep the imported data in a separate table, then union it for use, or import it to a permanent structure and have a field to reference that it will ID'd as such for management on each import.

 

[theDBguy]

That's my workflow as well. Since I'll be importing data and managing changes to it on a daily basis, wasn't sure if a best practice was to keep the imported data in a separate table, then union it for use, or import it to a permanent structure and have a field to reference that it will ID'd as such for management on each import.

In my case, I import JSON data from a Financial Company server. So, the data received end up in either the Orders or Payments table. New customer info included in the data end up in the Customers table.

 

[GPGeorge]

That's my workflow as well. Since I'll be importing data and managing changes to it on a daily basis, wasn't sure if a best practice was to keep the imported data in a separate table, then union it for use, or import it to a permanent structure and have a field to reference that it will ID'd as such for management on each import.

In my career, I came to prefer importing data into "holding" or "staging" tables in Access and from there manipulating it as needed to append to the permanent tables.

 

[arnelgp]

an advertisement is on the way...

 

[padlocked17]

If the data goes only one place and the validation is straightforward, link to the external file and use an append query to decide what gets added to your table and then an update query to decide what gets updated.

If the validation is complex or the input could end up in different tables, then you may need to import the data first. HOWEVER, you don't want to import the data into either your FE or especially your BE because that just causes bloat. To get around this, I create a template BE named InputData_BE. In this db, I define the tables and any indexes I need. I open my main app and link to the tables in that db. Then I compact the db and save it as "Template_InputData_BE" and put it in a separate directory where it won't get overwritten. In the import process, the first step is to copy Template_InputData_BE and rename it to InputData_BE in my local directory so it overlays any old copy of the file that was there previously. Now I import into InputData_BE. Then since my links are all active, none of my queries will break and I can just run the automatic process.

Great advice. I was working through the bloat issue and landed on a separate "Import" FE that would handle all import/appends, etc. via local tables before pushing ultimate changes to the BE. This is a good idea on the approach that I might consider / shift to.