1

I'm a newbie to batch scripting and need help.

I want to copy the contents of a csv file A.csv to the first worksheet named "GenData" in a existing XLS file B.xls.

B.xls contains some macros in it's second worksheet for graph generation from the data copied to it's first worksheet.

Both the files A.csv and B.xls are in the same folder. Also, I want to repeat this for about 40 folders containing these two files.

C:\Desktop\Abby\Folder1\A.csv

C:\Desktop\Abby\Folder40\A.csv

PS: I do not want to convert the A.csv to B.xls as B.xls has other worksheets which I wish to retain.I want to copy the contents of A.csv to the existing B.xls file's first worksheet.

I've tried a few scripts but the existing file B.xls is being over-written and the graph generation in the second worksheet is not working.

Abby
  • 11
  • 2
  • 5
  • 1
    Looks like you are trying to convert from CSV to XLS and XLS is not a trivial format, if you look at an XLS file as plain text you see it's not a trivial file format. You may need a third party program to do it via command line(if it even exists) though there must be some way that is probably out there.. So I looked up convert CSV to XLS via command line http://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line That way involves scripting.. not batch scripting but scripting. – barlop Jul 02 '15 at 05:00
  • you could also get a programmer to write you a little command line program to do it http://stackoverflow.com/questions/26648641/c-sharp-convert-csv-to-xls-using-existing-csv-file then put the program on sourceforge so it's available to all – barlop Jul 02 '15 at 05:01
  • Well, I do not want to convert the A.csv to B.xls.I want to copy the contents of A.csv to the existing B.xls file as B.xls has other worksheets which I wish to retain. – Abby Jul 02 '15 at 05:03
  • For managing xls files native tool is excel, probably it would be easier to do it through VBA, instead of batch script. As already commented batch script is can't change excel file content. There are several useful tutorials how to use VBA. – Máté Juhász Jul 02 '15 at 06:34
  • there seem to be solutions in vbscript, VBA, powershell.. By the way, copying the contents of a CSV into an existing XLS, does involve converting. It involves making a copy of the CSV, / reading the CSV into memory. Converting it to XLS, and appending that XLS to the existing XLS – barlop Jul 02 '15 at 07:16

3 Answers3

0

You can use PowerShell to read CSV and save to Excel ...

Check out this guys post ... Converting CSV file or files into an Excel workbook

SteveC
  • 2,115
  • 11
  • 32
  • 47
0

I would use Microsoft's Power Query add-in for this. In can import data from files or even folders of files. It can also take care of the inevitable transformations needed along the way.

Here's a nice blog post showing Power Query shredding a folder of csv files:

http://devinknightsql.com/2013/08/13/extracting-data-from-multiple-files-with-power-query/

Mike Honey
  • 2,512
  • 2
  • 11
  • 12
0

Just spitballing here, but someone more knowledgeable than me on VBA scripts, is there a way to invoke the Excel feature "import from text" in a macro?

It seems that, bar being triggered from a script, the "import from text" feature does exactly what OP wants. Put all the csv data in a new sheet and leaves everything else along.

OP, I know it isn't exactly what you're after, but if you don't find a script invoked solution, this might be what you're looking for?

enter image description here

Some_Guy
  • 744
  • 3
  • 12
  • 36