Excel - batch convert 22,000 xlsx files to csv files

gregfthompson
gregfthompson used Ask the Experts™
on
I have a large number of xlsx files that I want to merge.
And I thought the easiest way would be to convert them to csv and use DOS to merge the csv files.

Is it possible to batch convert 22,000 xlsx files to csv?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Máté FarkasDatabase Developer and Administrator

Commented:
You don't need to convert first into intermediate separated csv files. You can convert them directly to one large csv file. This way you don't need to use DOS.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Commented:
I'm not sure for this solution.
1. Press Shift and right-click on the folder of your xlsx files.
2. Select "Open Command Window Here".
3. Type Command

ren *.xlsx *.csv
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thank you.
Quite brilliant!!
Máté FarkasDatabase Developer and Administrator

Commented:
What???
AlanConsultant

Commented:
Hi Greg,

You have marked as 'best' a solution that *seems* very unlikely to work - please can you confirm?

Thanks,

Alan.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Hi Greg,

Can you please explain how that works for you ?

Author

Commented:
It appeared to work. In that the suffix changed to .csv. And the csv file opened correctly within excel.
But when I attempted to use DOS to copy them i.e. copy *.csv Combined.csv - the result was symbols.
So it would appear it only changes the suffix and the resulting combined csv is unreadable in excel.
AlanConsultant

Commented:
Hi Greg,

That is because Windows / Excel recognised that the file was still an Excel file even though you had changed the extension (Excel was protecting you from doing something that that makes no sense).

You should ask the moderators to reopen this question as the answer marked as correct is completely wrong and very misleading to anyone who comes along later and sees this.

Alan.

Author

Commented:
Hi Alan,

Thanks for your note.

I will atempt to contact the moderators and ask them to change the answer selected.

I found a solution here:
http://www.rondebruin.nl/win/addins/rdbmerge.htm 
It has worked perfectly.
Greg
AlanConsultant

Commented:
Good find - Mark your answer as 'best' and any others that helped as 'assisted' to close the question.


Thanks,

Alan.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial