chetan1981
asked on
merge excel files into csv
I have a bunch of xlsx files in a directory, I was able to get a vb utility that is able to convert them one at a time into a csv file.
I need help to find a way to loo[p through the directory and convert all files of a similar kind- say *.load.xlsx into load.csv
Any help will be appreciated, here's what the vbscript takes as its input
EXCEL_CVS.vbs load1.xlsx load1.csv
I am guessing once I have the individual files converted i could do a copy to merge the CSV files
Thanks!
I need help to find a way to loo[p through the directory and convert all files of a similar kind- say *.load.xlsx into load.csv
Any help will be appreciated, here's what the vbscript takes as its input
EXCEL_CVS.vbs load1.xlsx load1.csv
I am guessing once I have the individual files converted i could do a copy to merge the CSV files
Thanks!
Not really. Each CSV will contain header info, and that should be removed but for the very first file when combining them.
The trivial, but time consuming approach as a .cmd batch not changing anything is:
The trivial, but time consuming approach as a .cmd batch not changing anything is:
@echo off
del load.csv
for %%F in (*load.xlsx) do (
cscript //nologo excel_cvs.vbs %%F temp.csv
if not exist load.csv ( ren temp.csv load.csv ) else ( more +1 <temp.csv >>load.csv )
)
del temp.csv
ASKER
Thanks, I tried FOR "/f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%i.csv""
I am getting a syntax error -
%%i was unexpected at this time.
Sorry Batch scripting is not my strong suite, any pointers?
I am getting a syntax error -
%%i was unexpected at this time.
Sorry Batch scripting is not my strong suite, any pointers?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Noone told you to use FOR /F. You can do, but that is oversized, a simple FOR does the job well.
The error is that you added a double quote infront of /F.
The error is that you added a double quote infront of /F.
ASKER
Thanks for your suggestion, I was able to get it to run, but the EXCEL_CVS reports the file is not found.
The file name is correct. I am guessing this might be because the vbs and the excel file are in different folders. Also the source for the excel is a UNC path.
Thanks so much for your help and your patience.
The file name is correct. I am guessing this might be because the vbs and the excel file are in different folders. Also the source for the excel is a UNC path.
Thanks so much for your help and your patience.
From Qlemo's suggestion you may just need " "around the %%F variable... instead of just %%F try "%%~F" depends what is in this VBScript and what it reads from the commandline
It would also be quite possible to adjust the VBScript to pickup all the XLSX files in the subdir, or indeed use another XLSX file to do the work.... e.g. my script here will take a number of other Excel files, open them and print them. That could soon be used to save-as to CSV instead etc. if wanted I can change.
http://scripts.dragon-it.co.uk/links/excel-batch-print
Steve
It would also be quite possible to adjust the VBScript to pickup all the XLSX files in the subdir, or indeed use another XLSX file to do the work.... e.g. my script here will take a number of other Excel files, open them and print them. That could soon be used to save-as to CSV instead etc. if wanted I can change.
http://scripts.dragon-it.co.uk/links/excel-batch-print
Steve
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried it, it maps a drive to the source. Just to be sure i put the VBS on the share as well.
Still getting the same error, The error now has the mapped drive as the location of the vbs though.
Thanks
Here's how I tried it, PUSHD mapped the share to the W:\-
pushd \\Stage\Source Data\incoming
FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO XLSToCSV.vbs "%%i" "%%i.csv"
popd
Still getting the same error, The error now has the mapped drive as the location of the vbs though.
Thanks
Here's how I tried it, PUSHD mapped the share to the W:\-
pushd \\Stage\Source Data\incoming
FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO XLSToCSV.vbs "%%i" "%%i.csv"
popd
ASKER
Did some research, and looks like the message is from excel. Any ideas why excel is not able to locate the source?
Thanks.
Untitled.png
Thanks.
Untitled.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got you, dont hae a preference either way, will try your advice...thanks
ASKER
Works! Thanks!.
really appreciate the help .
really appreciate the help .
del destination_path\Concatena
TYPE *.csv >> destination_path\Concatena
or you can do some fancy things and create one per day with YYYYMMDD.csv (need some work here)