Questions on data import
Hello Quickbase Community, I'm looking for some experienced opinions on how to approach a current requirement. I basically need to upsert 25.000 data rows each day. I have little to no control ...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendMartin,
This can be done natively without Pipelines.
1. Create a "scratch" temporary table with the same columns as your raw source data.
2. Create a set of fields by formula to massage the data into he format you need. Some fields may be OK as it, some may need to be replaced with formula fields.
3. Create a saved Table to Table import to map the field across to your real table. You say an Upsert, so that would be a "Merge" selection in the saved T2T import. The saved T2T may also have filters to strip out rows that are not to be imported
I suggest setting up a single record in a new table to create button to do actions like,
Clear the scratchpad,
Import the scratch pad.
Count the # of record in the scratch pad and count how may will be added vs how may will be Merged in to update existing records.
Feel free to post back if you have question.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
This can be done natively without Pipelines.
1. Create a "scratch" temporary table with the same columns as your raw source data.
2. Create a set of fields by formula to massage the data into he format you need. Some fields may be OK as it, some may need to be replaced with formula fields.
3. Create a saved Table to Table import to map the field across to your real table. You say an Upsert, so that would be a "Merge" selection in the saved T2T import. The saved T2T may also have filters to strip out rows that are not to be imported
I suggest setting up a single record in a new table to create button to do actions like,
Clear the scratchpad,
Import the scratch pad.
Count the # of record in the scratch pad and count how may will be added vs how may will be Merged in to update existing records.
Feel free to post back if you have question.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
- Hi Mark,
thanks for your answer. The T2T method sounds interesting and would solve the field mapping and transforming issues.
I wonder though what do you suggest for the initial import? I assume I'll have to deal with CSV files separated by semicolon, tab or comma. And I need to fill that temporary table in the first place with 25.000 rows. Which interface would provide the performance and flexibility in file structure?
I should have mentioned: In the end the whole import process should run automatically or based on some trigger.
Best regards,
Martin
------------------------------
Martin Suske
------------------------------- MarkShnier__YouFor fully automated you will want the scratch file to be a connected Sync Table. Your automated process would put the file there each day into an SFTP path or else in the cloud to Dropbox, Box, or Google Drive.
Then on a scheduled basis a Pipeline can run to do the T2T Copy and delete the scratch file records.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
