Batch to add Filename as First Column

Microsoft Windows 7 ultimate 64-bit
March 20, 2011 at 19:40:38
Specs: Windows 7 x64 Ultimate, i7 920 / 9 GB

I am in desperate need of assistance. I have been browsing the forums here and have tried modifying several examples for nearly 4 hours, I'm not entirely sure of the syntax and what I am doing, thus I am not able to get the results I want.

I need to add the filename of a CSV file as the first column of the CSV, with a first row header name of "ID". I would like to be able to run this batch on a collection of CSV files contained within a directory and have it insert the column with the respective filename in each file.

Here is a link to a unaltered CSV for reference:

I'm obviously out of my element, and I would sincerely appreciate any assistance that someone may be able to provide.

See More: Batch to add Filename as First Column

Report •

March 21, 2011 at 12:45:25
for %%a in (*.csv) do call :addId "%%~Na" "%%a"
goto :EOF

@echo off
for /f "usebackq delims=" %%b in (%2) do (
  > #.csv echo ID,%%b
  goto :next
for /f "usebackq skip=1 delims=" %%b in (%2) do (
  >> #.csv echo %~1,%%b
move #.csv %2

How To Ask Questions The Smart Way

Report •

March 21, 2011 at 13:30:58

Thanks for taking the time to answer my question. The code you provided does do exactly what I wanted. Awesome! However, I receive a strange error messages upon attempting to open the CSV, after batch execution, in Excel.

The first dialog box I get is:

When I click "Yes", I receive a second dialog box:

If I then click "OK" to open in a different format, the file opens and displays fine, like a normal CSV.

Here is the modified file for reference:

Since adding the column is one of the first steps in my intended workflow for these files, I want to ensure that I will not have any problems bringing these files into MS Access or other DB solution. I'm not sure why the file is attempting to be recognized as an SYLK file and no longer as a normal CSV after batch execution. Any ideas? Thanks again for your help.

Report •

March 21, 2011 at 13:54:29
No idea. If I had to guess, I'd say Excel sees it as an ANSI file that starts with "ID," and just assumes it's a SYLK file. (Apparently SYLK's are ANSI files that start with "ID.") When the SYLK processing fails (because it's not a SYLK file), it proceeds to open it as a CSV file (like it should).

If you were so inclined, you could file a bug report with Microsoft, but I'm not sure how you'd go about that.

Wikipedia says it's an Excel specific file type, so I'd try it in Access, or other DB solution, and see if it's "smart" enough to realize your properly formated CSV file is really a horribly mangled SYLK file. If your work flow involves opening the file in Excel, you could add the column in Excel, or you could name the column something other than "ID."

How To Ask Questions The Smart Way

Report •

Related Solutions

March 22, 2011 at 07:22:55
Nice explanation. Seems like it should work out fine. I appreciate your assistance with this issue.

On a related note, do you happen to have a suggestion for learning materials on how to code these batch scripts?

Report •

March 22, 2011 at 08:11:17
I don't know of any books offhand; I imagine any language filled with more gotchas than rules doesn't make for good reading material. Books that even acknowledge the Command Prompt are geared towards administering Windows boxes.

Personally, I learned from reading the command's help text (<command> /?), tricks I picked up from sites like this, old DOS knowledge, some UNIX knowledge, and lots of trial and error. (Forum participation is largely an excuse to practice these skills.) Most of the functionality in this script is explained in for /?, call /?, and goto /?.

I should also mention this site's How-to's, which contain some tricks of the trade.

How To Ask Questions The Smart Way

Report •

March 22, 2011 at 13:46:54
Agreed. The language is appears counterintuitive, with rigid syntax rules. My initial search results for learning materials was sparse, when I was first attempting to tackle this task. I'll look into the resources you referenced.

BTW, you were spot on about the Excel interpreting the "ID" in the first row/column as an SYLK file header. I changed it to "FileID" and it opens fine, no problems.

I have to admit I was damn frustrated after trying to modify the other scripts I found here to work for my purpose. I'm seriously impressed by the knowledge and grasp of this language that you have and I sincerely appreciate your willingness to participate in the forums, certainly others do too. You helped me out tremendously. Thanks again.

Report •

Ask Question