←Back to Beyond Market Intelligence
•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Automating data import from multiple files

Our take

Automating the import of multiple CSV files into Excel can streamline your workflow significantly, especially when dealing with numerous files organized by a consistent naming convention. By creating a structured approach, you can efficiently import data from 35 different CSV files for each BASE variable, consolidating all relevant dates and variable names into individual sheets. This method not only saves time but also enhances data management and accessibility, allowing you to focus on analysis rather than manual data entry.

I have a program (third party, I can't change) that produces 35 different csv files. The naming convention for these files is BASE_VAR.csv where BASE is the same for all the files and VAR is different for each file. I'll end up doing this 100 times, with different BASE so I want to automate it.

Each csv file has 2 columns, the first column is a list of dates that is the same for each file and the second column is the variable name specified by VAR.

The goal is to import all of these into an Excel file where sheet name is BASE and then the first column is dates (shared by all files) and then columns B-? correspond to the 35 different VARs. There would be 100 sheets, each named for the value in BASE.

So, in my mind, the workflow would be:

For a given BASE (so 35 csv files),

  1. create new sheet. name it base

  2. import both columns from the first csv file so now we have dates and VAR1

  3. import the next 34 file but only the second column of each one (the csv files have a column listing for each different VAR so importing the second column will already have identifying info).

Then repeat for the other 99 BASE values.

Anyone have ideas on how to automate this?

Thanks for any help!

submitted by /u/yevquest
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article →

Tagged with

#workflow automation