How to automate Get & Transform from new CSV files

[Marius Du Toit]

I need advise from the Get & Transform experts to automate the collection of data from CSV files - Using Excel 2016

The client uses a 'non-standard' ERP system which has no ODBC or other method of connecting to it's database.  The only current method is to manually export reports into CSV files.

They would like to use Excel for analysis and report on historical data and add new data monthly (by exporting monthly CSV reports)

Each report generated will have a different name e.g. PrintCurrentTable_280220181122

This is the ideal process:

1) Generate a CSV report at end of each month and save it to a dedicated folder

2) Look for generated CSV file/s in said folder and import data (append to previous data)

3) Delete (or move to another folder) CSV file after successful import

1) Can this import process be accomplished with Excel Get & Transform (only)?

2) Or should other tools be used for the import process?

3) How would you approach this problem?

Any advice will be appreciated.

Thanks

Marius

[#2 · Accepted solution]

Bugger!  First time ever I answered my own question.

After some experimentation with Excel 2016 and the Get & Transform feature I discovered how to accomplish the required outcome.

Without going into too much detail, this is how it works.

1) I created a folder where the user can dump their monthly CSV files.

To start with, there is no need to remove the CSV files after the data has been imported into Excel, as long as new files added don't contain duplicate information.  This creates added advantages

(a) accumulation of historical data (b) other queries could access the same data for different purposes.

2) In Excel use (on the Data tab) GET DATA > FROM FILE > FROM FOLDER

Follow the prompts to complete the query building process

3) Drop new CSV files in the folder and refresh the data in Excel (Data Tab - Refresh all)

This solution works perfectly for my client, but your requirement may differ.

[#3]

Regarding not removing files - there is a not insignificant overhead to constantly reprocessing the same input files.

An alternative approach is to capture folder lists (also using G & T) and then you'll know which have or have not been previously processed - and only reprocess the ones you've not seen before.

[#4]

@Marius Du Toit Off topic here, but how did you get the two images in your self-answered post's reply?(the one that reads "bugger" with a check mark.

[#5]

@LoudCloudDragon 

I guess that's added automatically by system