Adding a column to a csv file and populating it

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Message
Author
foxidrive
Expert
Posts: 6033
Joined: 10 Feb 2012 02:20

Re: Adding a column to a csv file and populating it

#16 Post by foxidrive » 14 May 2012 16:54

This case works. You should be able to figure out the second case - can you?

6,11253,GIFT SHOP,0.05,05/08/2012,
but the columns need to be rearranged like this:
6,11253,05/08/2012/GIFT SHOP,0.05,


Code: Select all

@echo off
for /f "tokens=1-5 delims=," %%a in ('type "file.csv"') do (
>>"newfile.csv" echo %%a,%%b,%%e,%%c,%%d,
)

kfriedley
Posts: 3
Joined: 02 May 2012 15:49

Re: Adding a column to a csv file and populating it

#17 Post by kfriedley » 15 May 2012 16:57

I was able to create a bcp to create a format file and map the fields of the data file to the columns of the table.
:)

kfriedley
Posts: 3
Joined: 02 May 2012 15:49

Re: Adding a column to a csv file and populating it

#18 Post by kfriedley » 15 May 2012 17:00

Thanks for your post!!! I'll try that too and see which one works best for me.

ecnerwal72
Posts: 1
Joined: 11 Mar 2014 11:03

Re: Adding a column to a csv file and populating it

#19 Post by ecnerwal72 » 11 Mar 2014 11:17

My database schema has 23 columns/fields, however when I export it has a csv file some of the lines in my csv file has less than 23 fields therefore when I import the file, it complains about miss match field counts.
With your batch file how can I go about adding a NULL field at the end of the line only for those lines with less than 23 fields?

haishi
Posts: 4
Joined: 23 May 2018 12:58

Re: Adding a column to a csv file and populating it

#20 Post by haishi » 04 Jun 2018 09:02

Though this is an old thread, I would like to know how the first code ...

Code: Select all

@echo off
:: %1 is the input filename
:: %2 is the new column header
:: %3 is the dummy number to fill every cell in the new column

setlocal
set "file=%~1"
set "fileout=newfile.csv"

set /p "var="<"%file%" >nul

>"%fileout%" echo.%var%,%2

for /f "skip=1 delims=" %%a in ('type "%file%"') do (
>>%fileout% echo.%%a,%3
)
... can be modified, to process all files within a folder or files, that are dropped onto the batch file? It would be sufficient save over the old files or - if that's not possible - create new files in a subfolder. That would be great.

tecc
Posts: 1
Joined: 09 Dec 2018 02:57

Re: Adding a column to a csv file and populating it

#21 Post by tecc » 09 Dec 2018 03:03

I'd also like to chime in with my problem, maybe you can help a total newbie out: I have a dir with several csv files, each named after a company. Now I want to add that filename (minus the .csv) as the first colum inside the csv (which can have different number of rows, from 4 to 600+). The delimiter inside the csv is a ; instead of a comma. Any idea how to do this?

Squashman
Expert
Posts: 4106
Joined: 23 Dec 2011 13:59

Re: Adding a column to a csv file and populating it

#22 Post by Squashman » 09 Dec 2018 16:41

tecc wrote:
09 Dec 2018 03:03
I'd also like to chime in with my problem, maybe you can help a total newbie out: I have a dir with several csv files, each named after a company. Now I want to add that filename (minus the .csv) as the first colum inside the csv (which can have different number of rows, from 4 to 600+). The delimiter inside the csv is a ; instead of a comma. Any idea how to do this?
The base code is already in this thread a couple of times to edit a single csv file. The amount of rows in your files is irrelevant for the most part. It would only matter if you were dealing with files larger then the amount of memory you have installed in your computer.

Code: Select all

@echo off

FOR %%G IN (*.csv) DO (
	(FOR /F "usebackq delims=" %%H IN ("%%~G") do echo %%~nG;%%H)>"%%~nG.tmp"
	del "%%~G"
	rename "%%~nG.tmp" "%%~G"
)

Post Reply