Processing 2,000 CSV files

[shophoney]

Hi,

I have an excel file that is 80mb in size. I have daily files for several years that I upload each day. I would like to see if I could save time by storing prior years already. They are csv file format 365 files x 4 years and take 30 minutes to upload each day. Each morning I need to open the file and have it upload the folder from 2019 till today. I'm wondering if their is a better way to have it upload 2019 till a point and keep it stored and just upload newer files.

Currently I append the 2,000 csv files and have it all stored in one table.

I do this for a file that processes all sales from start of 2019 and another file I do basicly the same thing review all store history for the last 4 years. Each day my staff have to wait while it uploads the folder and appends each day into one large table.

Thanks for reading my post.

 

[Matt Allington]

They way I handle this is as follows
1. Install DAX Studio
2. After loading all the CSV files, use DAX studio to export all the data to a new CSV file. Call it history
3. Load the history file to PQ
4. Archive the 2000 CSV files into a zip, and file. Actually, I recommend keeping at least 1 CSV and don’t include it in the steps above, otherwise your regular query may fail. Ip just make sure you only load it once.
5. Append the history file to the regular file combine result table.

You can repeat the process every year

 

[shophoney]

They way I handle this is as follows
1. Install DAX Studio
2. After loading all the CSV files, use DAX studio to export all the data to a new CSV file. Call it history
3. Load the history file to PQ
4. Archive the 2000 CSV files into a zip, and file. Actually, I recommend keeping at least 1 CSV and don’t include it in the steps above, otherwise your regular query may fail. Ip just make sure you only load it once.
5. Append the history file to the regular file combine result table.

You can repeat the process every year

Hi Matt,

Thanks for replying to my post.

What is the advantage of this approach? Is it better to have 1 master file with the prior years than uploading 2,000 individual files?

Are there any trick to having Excel use more of the system resources? I have a top of the line i9, 64gb ram, liquid cooled... But it never uses enough of what the system has. I manually have to set the priority to real time every time.