Forum Discussion
How to automate Get & Transform from new CSV files
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.
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.
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.
I guess that's added automatically by system
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.
