consolidate data from multiple csv files in excel

Hello
I need to consolidate in an Excel data in multiple csv files.
I need to filter some columns to calculate the sum in other columns. Right now I do this manually in the source file and the copy paste the results in the main Excel file. This takes a lot of time and is prone to error.

What I want:
* I do want to do this when the csv files are closed
* I want to generate the path and filename dynamically with the indirect() function
* I want to able to use functions like SUMIF() or SUMIFS()

Problems:
* The preliminary functions like using a matrix to make SUMIF() / SUMIFS() work with closed files seems not to working with csv files
* INDIRECT() does not work with closed files even when the formula in a matrix would work with closed files

Does anybody have an idea how to accomplish such a task?
Regards
Sacha
sachakunAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
CSV files are text so will not support a link when they are closed - there is no excel 'wrapper' to respond to the query. You can use a VBA macro to open them and extract the data into your summary sheet or copy them into their own tab in an excel workbook.

Alternatively you can create a data query (under data, import from other sources) using MS data query wizard and the CSV/Txt driver - when refreshed this will extract the relevant data without loading the csv into excel
sachakunAuthor Commented:
Hello

Interesting suggestion. I see 2 drawbacks with this solution.

* I need to add the source files manually every month. With multiple files each month this is tedious
* My main file gets bigger every month when adding a lot of data just for a sumif() oder a sumifs()

Question: Could I implement the query in a way, that the sumif() is the result of the query?
Question: What if all the files where in the excel format? I found a software that is capable to convert csv to xlsx trough a batch process. Would then another solution present itself?
regmigrantCommented:
If the CSV are in excel format then you can link them so they don't need to be open however I assume your reference to 'indirect' not working indicates that you are trying to generate the filenames within the summary sheet and then using indirect to try and get to the data in them.
I think (I would have to do some testing to be sure) that for this approach to work the file would have to be open the first time the indirect is triggered so the link can be established.

The query is volatile so you wouldn't be able to rely on its output in a formula unless the source data were always present



Without knowing the details of the requirement I suggest you need a slightly more complex approach and this would depend on how regularly the files arrive and need to be processed (eg: one or two each day and processed at end of month or all arriving together on the first of the month, etc)
you could use a combination of VBA and batch script to:-

1. intake Pre-processing
take the source files from a landing folder
convert to excel if needed
modify filenames (to make them standard for a query for example)
consider concatenating the source files for processing if they are similar formats
log the intake
remove from the landing folder
2. monthly processing

Generate a list of source files for this month in a summary spreadsheet
Open each file, or use a standardised data query to extract the necessary data and build an intermediate summary
Analyse the summary and generate the input for the Sumif
Move just what is needed to the final summary
Close the temporary sheet
3. Close down
Move the temp sheet and the input files to a 'processed' folder

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sachakunAuthor Commented:
The answer was a bit high level and I lack the time to go into the details. Just having multiple text files open over an odbc connection is a daunting task in itself.

My solution is to have a summary csv programmed along the input csv that I receive every month.
regmigrantCommented:
fair enough - good luck with it!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.