0

Is there an easy way to create multiple spreadsheets from a single excel sheet?

For example, if I had an excel file in the following format:

spreadsheet

could a batch file be used to create separate files for every Number in the list? each file would contain all rows for that Number, and be named using a format with text around the Number, like "Number_123_rows.xls".

kmfdm
  • 79
  • 1
  • 5
  • 12
  • Is it an option for you to save that XLS as an CSV-file? This is pure text and easy to process with batch – Stephan Jul 15 '13 at 17:04
  • That would work, sure. I don't suppose it's possible to add that step to a batch file as well? – kmfdm Jul 15 '13 at 17:45

2 Answers2

0

This should do, what you want. It works with a comma delimited csv-file, but you can change that to your needs (delims=):

@echo off
for /F "tokens=1* delims=," %%i in (file.csv) do (
    echo %%i,%%j >>%%i.csv
)

(you may want to delete the "Number.csv")

For converting XLS to CSV, this may help (I did not try that): file

EDIT if "numbers" is in another column a single line consists of several "tokens", delimited by one or more delimiters (in this case the comma) You can take token 1, token 2, token 3 etc. "*" means "the rest of the line" So, if Numbers is in Column 2, write

for /F "tokens=1,2,* delims=," %%a in (file.csv) do (echo %%a,%%b,%%c >>%%b.csv )

(note that it writes to the file %%b.csv (number is column 2)

or if you want to change the order:

for /F "tokens=1,2,* delims=," %%a in (file.csv) do echo %%b,%%a,%%c

(note the order of a, b and c) if you would use 26 tokens, they will be named %%a to %%z

you can expand it to "tokens=1,2,3,4,5,6,7" etc, when you need it to rearange the order.

Edit2: for the headers, simply add one line before the "for"-line:

echo Number,Title,Third Column >file.csv

Thee > creates a new file (deleting a existing file), while >>appends to the file

Community
  • 1
  • 1
Stephan
  • 53,940
  • 10
  • 58
  • 91
  • That works, thank you. A couple of questions: 1) Is it possible to specify the column used, instead of defaulting to Column A? Say my "Numbers" field was in another column, for example. 2) Is it possible to also enter the field headings into row1 of the newly created files? – kmfdm Jul 15 '13 at 19:34
  • your question 2: are the headings fix/predefined, or will they have to be read from the file? – Stephan Jul 15 '13 at 20:53
  • I see...so that handles the column ordering. Unless I missed it, it does not address the column labels in row1, correct? – kmfdm Jul 15 '13 at 21:45
  • Sorry, I should have specified. They don't change (often) so using a predefined set would work fine. – kmfdm Jul 15 '13 at 22:53
  • I must be missing something. Your 2nd edit resulted in my original file.csv being overwritten instead of inserting headers into all of the output files. – kmfdm Jul 16 '13 at 19:45
  • you missed my last line? ;-) – Stephan Jul 16 '13 at 20:34
  • I guess I wasn't following. putting it "before the "for"-line:" as you stated would append those headers to...what, exactly? since the multiple output files aren't created until that specified "for" line, right? – kmfdm Jul 16 '13 at 20:39
  • you missed my last line? `echo one,two,three >file.csv` will create the file, not append. `>>`will append (or create, if not existing). – Stephan Jul 16 '13 at 20:45
  • right. but as I understand it, placing it before your "for" line means there are no files yet to append to. it's just an echo command to one file name...which is exactly what happened. not sure why i would want to create a single file with headers? placing it after the "for" line and changing file.csv to %%i.csv means it's appended to the bottom of the output files instead of the first row. – kmfdm Jul 16 '13 at 21:00
  • i'm obviously missing something easy, sorry. i asked how to add the column headers to the first row of every output file, but your command seems to create a new file with the headers before the output files are created. i'm not following exactly how that accomplishes it. – kmfdm Jul 16 '13 at 21:07
  • you asked "Creating multiple spreadsheets from a single excel file". "Creating files" means "New files", or is my english that bad? Nevertheless: To add as first line on an *existing* file, echo your headers line to a new (temporary) file, type your existing file to that temprorary file, delete (or rename) your original file, and rename the temporary file to the original file's name. – Stephan Jul 16 '13 at 21:20
  • I thought your 2nd edit was directly in response to my question: "2) Is it possible to also enter the field headings into row1 of the newly created files?". The goal is to have the column headings in row 1 of every single output file. The creation of all my output files works great, I just need a way to get the headers into all of them. – kmfdm Jul 16 '13 at 21:32
0

(new answer to avoid more confusion - obviously I should avoid working late in the night)

This assumes, you have only your starting "file.csv", the column, you use for the new filenames is column 2 and you want to reorder "a,b,c,d,,e,..." to "b,a,d,c,e,..."

It checks, if the needed file (e.g. "123.csv") already exists, if not it creates it with the desired Header (instead of first creating the file, then add the header afterwords).

@echo off
set "header=Title,Number,Whatever,Something,Anotherthing"
for /F "tokens=1-4* delims=," %%a in (file.csv) do (
    if not exist "%%b.csv" (
        rem when the file does not exist, create a new one with the header:
        echo %header% >"%%b.csv"
    )
    rem append next line: 
    echo %%b,%%a,%%d,%%c,%%e >>%%i.csv
)

(you may want to delete the "Number.csv")

Stephan
  • 53,940
  • 10
  • 58
  • 91