Skip to main content
Question

Questions on data import

  • October 25, 2021
  • 0 replies
  • 112 views
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 over the output format of the source systems, so my main concern is:
Will I be able to perform the necessary transformations within Quickbase or should I rely on an additional tool sitting between the source and Quickbase and transforming the data into appropriate formats for Quickbase to handle it.

I already worked with connected tables and found it pretty smart for the task.
But I found it processes only comma separated files with the headers matching the Quickbase table fields.

Then I tested the pipeline CSV Handler with 25 rows of testdata and had to wait almost 5 Minutes to load them. Doesn't seem to be the way to go with 25.000 rows. Although I could flexibly change the separator and match source to target fields.

I had a look into the pipeline "Bulk Record Set - Import with CSV" which seems to be suited for big loads of data and read about a 10.000 rows limit per pipeline execution what would lead to further preparation of my source data too.

My question is: 
Is there a way of dealing with data transformation and mass data imports in Quickbase or should I definitely use an ETL framework upfront? 

Any input is highly appreciated.

Thanks and best regards,
Martin



------------------------------
Martin Suske
------------------------------
This topic has been closed for replies.

RaziD
Forum|alt.badge.img+3
  • RaziD
  • Quickbase Alumni
  • October 25, 2021
Hi Martin;
You may want to check EZ File Importer from Juiced Tech.
I know it is easy to use and powerful.
Here is the link for this add-on: https://www.juicedtech.com/ez-file-importer
I hope it helps

------------------------------
Razi D.
Desta Tech LLC
------------------------------
Razi D. [WebsiteUrl]
MarkShnierYou
Forum|alt.badge.img+22
  • MarkShnierYou
  • Quickbase Alumni
  • October 25, 2021
Martin,
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
------------------------------
Mark Shnier. Your Quickbase Coach
Forum|alt.badge.img+15
  • DonLarson
  • Quickbase Alumni
  • October 26, 2021
Martin,

Is the output format consistent?  I have got a client that gets data from 50 or so suppliers and everyone of them formats it differently AND will change the format without and warning.   The last part makes normalizing the data particularly challenging.  

We built a Normalization app where every Supplier has their own table so there are unique formulas to get the data the way it is needed.  Included is some robust error checking to look for surprises each month.

In hindsight I wish we had used a full ETL tool like Mule.

https://www.mulesoft.com/

The process would have been slower but the tools are much more sophisticated.


------------------------------
Don Larson
------------------------------
Terms & ConditionsAccessibility statement