Solved

merge excel files into csv

Posted on 2014-11-20
14
135 Views
Last Modified: 2014-11-20
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!
0
Comment
Question by:chetan1981
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
14 Comments
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 40455406
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 40455435
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
 

Author Comment

by:chetan1981
ID: 40455454
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 51

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 150 total points
ID: 40455472
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 40455494
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
 

Author Comment

by:chetan1981
ID: 40455505
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
 
LVL 43

Expert Comment

by:Steve Knight
ID: 40455529
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
 
LVL 43

Assisted Solution

by:Steve Knight
Steve Knight earned 100 total points
ID: 40455533
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
 

Author Comment

by:chetan1981
ID: 40455570
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
 

Author Comment

by:chetan1981
ID: 40455598
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
 
LVL 69

Accepted Solution

by:
Qlemo earned 250 total points
ID: 40455621
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
 

Author Comment

by:chetan1981
ID: 40455792
Got you, dont hae a preference either way, will try your advice...thanks
0
 

Author Comment

by:chetan1981
ID: 40455815
Works! Thanks!.

really appreciate the help .
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to add SLEEP  to my runner.bat script?? 4 61
Need some help in Batch file assigning variable value ? 4 77
regex to extract ip:john 17 77
Windows Batch File - Read CSV 9 58
AutoHotkey is an excellent, free, open source programming/scripting language for Windows. It started out as a keyboard/mouse macros product, but has expanded into a robust language. This article provides an introduction to it, with links to addition…
Introduction: Recently, I got a requirement to zip all files individually with batch file script in Windows OS. I don't know much about scripting, but I searched Google and found a lot of examples and websites to complete my task. Finally, I was ab…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question