I am looking for a way to load CSV files directly into a staging table via an application engine process.
It works via the administration application. But there all steps have to be done manually.
Has anyone already done this in the MT Cloud?
Use an Application Studio report to upload the CSV file to a repository attachment. Then run the Application Engine process to read the attachment, parse it, and write the data to the table in the staging database.
Have the report delete the attachment when finished.
We have already thought about this way. However, since the files are large (about 50,000 lines), we fear that the transfer will take a very long time and we were therefore looking for a direct way.
Hello Daniel, in our project we used to load more than 270k lines in txt.
I've uploaded a template for you to build on.
It has the Engine and reports.
Sorry for being in Portuguese, but that's how it is...
Any doubt I am available.
https://1drv.ms/u/s!AhoK3wwNwbKJhORMTzIBWbwj4I6Spg?e=mZ8PlH
Regards
Thank you very much. We will definitely try that. Google will help me with the Portuguese for sure
Hi Alexandr
How long does your upload take for the 270 k lines and do you work in a MT Cloud or onpremise?
Hello, I use this functionality in MT and On-premises.The processing time, upload, temp and write to the cube takes around 15 min in MT and 7 min in on-premises.
Using INSERT statements from app engine is always going to be quite slow. Please look into bulk write functions if you want to make this much much faster !
SQLBulkInserData etc
thanks for the tip. Do you have another hint where I could find an example for this?
does this also work when processing a CSV file from the repository ?
well its just an alternative way how to write the data to SQL. You can still take the data from CSV or anywhere else.
I hope someone else has some example. Or maybe Alexandre will want to rewrite his script to use the bulkd write and then share it
I obtained good performance in the past using a combination of In-memory tables and Bulk Insert.
Create an in-memory table first (SQLCreateInMemoryTable, SQLDataReadTableDefinition) and use this table to insert the parsed lines (SQLDataInsertRow).
Then use SQLBulkInsertData to copy the in-memory table to the table in staging.
Hope this helps...
Good idea, I will try that
It works from 420 seconds to 10 seconds!!!Thanks all for the inputs
