Insert filename into first column in CSV file

January 5, 2011 at 00:59:35
Specs: Windows 7
Hi There!

I have many output files (500+).

The file names reflect the username, for example: "firstname lastname.csv"

A snapshot of each file is as follows:

Application Name,Description,Version,Vendor,Copyright
" Crypto 1 Library","",,"",""

Each CSV contains upwards of 100 lines, but essentially follows the above formats.

So I'm trying to write a batch file that will take my list of files which I have in a text file (filelist.txt), insert the name of the file into each CSV as the first column.

I can them merge them myself and work with the information.

Advice apprecaited!

Thanks in advanced :) WM


See More: Insert filename into first column in CSV file

Report •

#1
January 5, 2011 at 02:14:15
@echo off & setLocal EnableDELAYedeXpansion

for /f "tokens=* delims= " %%a in (filelist.txt) do (
> # echo.%%a
for /f "tokens=* delims= " %%i in (%%a) do (
>> # echo.%%i
)
move /y # %%a
)


=====================================
Life is too important to be taken seriously.

M2


Report •

#2
January 5, 2011 at 02:24:10
Thanks for your reply.

That's not quite creating what I'm after.

The output is as follows:

"firstname lastname.csv"
firstname lastname.csv

The desired output is:

Filename,Application Name,Description,Version,Vendor,Copyright
"firstname lastname.csv","Crypto 1 Library","",,"",""


Report •

#3
January 5, 2011 at 03:07:52
oops Yes, you did say first column.

==========================================
@echo off & setLocal EnableDELAYedeXpansion

for /f "tokens=* delims= " %%a in (filelist.txt) do (
set N=
for /f "tokens=* delims= " %%i in (%%a) do (
set /a N+=1
if !N! equ 1 (
> # echo.%%a, %%i
) else (
>> # echo.%%i
)
)
move /y # %%a
)


=====================================
Life is too important to be taken seriously.

M2


Report •

Related Solutions

#4
January 5, 2011 at 03:12:49
Hi There. Progress is being made here :) The output that batch returns is:

"firstname lastname.txt", firstname lastname.txt

Problem is the OLD data in the CSV is lost. I just want to add a column to the existing data.

Thanks again :) WM


Report •

#5
January 5, 2011 at 03:20:58
Do you want the filename prepended to EACH row?


=====================================
Life is too important to be taken seriously.

M2


Report •

#6
January 5, 2011 at 03:28:39
Yes please.

Report •

#7
January 5, 2011 at 03:33:31
@echo off & setLocal EnableDELAYedeXpansion

for /f "tokens=* delims= " %%a in (filelist.txt) do (
@echo off > #
for /f "tokens=* delims= " %%i in (%%a) do (
>> # echo.%%a, %%i
)
move /y # %%a
)


=====================================
Life is too important to be taken seriously.

M2


Report •

#8
January 5, 2011 at 03:41:36
Sorry I keep asking again but still no joy. Output to each file is now:

"firstname lastname.txt",firstname lastname.txt

Just to be clear.

I have a directory full of CSV files. They are all named "firstname lastname.txt" - names assuming the names of the users.

Currentlly the content of each CSV is a output, sometimes 100+ lines but looking something like this:

Application Name,Description,Version,Vendor,Copyright
"CMS Library","",,"",""
"Certificate Key Store Browser Library","",,"",""
"Crypto 1 Library","",,"",""
"Crypto 2 Library","",,"",""
"Crypto 3 Library","",,"",""
"Crypto Applications API Library","",,"",""

I'm trying to sort out a batch file that will read a list of files from a text file and add to each CSV file 1 column which would be the file name.

So the above CSV example would become:


Firstname Lastname.txt,Application Name,Description,Version,Vendor,Copyright
"Firstname Lastname.txt""CMS Library","",,"",""
"Firstname Lastname.txt""Certificate Key Store Browser Library","",,"",""
"Firstname Lastname.txt""Crypto 1 Library","",,"",""
"Firstname Lastname.txt""Crypto 2 Library","",,"",""
"Firstname Lastname.txt""Crypto 3 Library","",,"",""
"Firstname Lastname.txt""Crypto Applications API Library","",,"",""


Report •

#9
January 5, 2011 at 05:09:39
I was thinking, maybe this could be easlier to append to the end of each line in the CSV?

Creating an output like this or similar

Application Name,Description,Version,Vendor,Copyright,Firstname Lastname.txt
"CMS Library","",,"","","Firstname Lastname.txt"
"Certificate Key Store Browser Library","",,"","","Firstname Lastname.txt"
"Crypto 1 Library","",,"","","Firstname Lastname.txt"
"Crypto 2 Library","",,"","","Firstname Lastname.txt"
"Crypto 3 Library","",,"","","Firstname Lastname.txt"
"Crypto Applications API Library","",,"","","Firstname Lastname.txt"

The end game here being to merge all the files, put them into excel and work with them there.

Thanks again for your help. Kind regards


Report •

#10
January 5, 2011 at 06:15:38
Thankyou for your help.

A slight tweek to your script has fixed the prblem.

USEBACKQ option for For fixed the problem.

All working pefectly.

thanks again! Have a great new year

Code below:


for /f "usebackq tokens=* delims=," %%G in (filelist.txt) do (
@echo off > #

for /f "usebackq tokens=* delims= " %%i in (%%G) do (
>> # echo."%%G", "%%i"
)
move /y # %%G
)


Report •

Ask Question