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!
chetan1981Asked:
Who is Participating?
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.

HainKurtSr. System AnalystCommented:
then to join all csv files into one

del destination_path\ConcatenatedFiles.csv
TYPE *.csv >> destination_path\ConcatenatedFiles.csv

or you can do some fancy things and create one per day with YYYYMMDD.csv (need some work here)
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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:
@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

Open in new window

0
chetan1981Author Commented:
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?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

HainKurtSr. System AnalystCommented:
or something like this

for %%F in (*load.xlsx) do (EXCEL_CVS.vbs %%F.csv)
del destination_path\ConcatenatedFiles.csv
TYPE *.csv >> destination_path\ConcatenatedFiles.csv
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
chetan1981Author Commented:
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.
0
Steve KnightIT ConsultancyCommented:
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
0
Steve KnightIT ConsultancyCommented:
Actually I see now you say the data is on a UNC path.  Use pushd and popd above and below your commands then it will change to that directory on a temporary drive

pushd \\server\share\directory


popd
0
chetan1981Author Commented:
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
0
chetan1981Author Commented:
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
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Please use
  FOR %%i IN (*.xlsx) DO XLSToCSV.vbs "%%~fi" "%%~dpni.csv"
if you want to follow that road. But I don't get why you stick on that, as it creates another bunch of files you will not want to keep.
0

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
chetan1981Author Commented:
Got you, dont hae a preference either way, will try your advice...thanks
0
chetan1981Author Commented:
Works! Thanks!.

really appreciate the help .
0
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
Windows Batch

From novice to tech pro — start learning today.