Adding a column to a csv file and populating it

[stylishjm]

Hello, I wish to run a batch file which will add a column at the end (usually "AL") to a CSV file and populate each row in that column with a static number.

For example adding 816 to each row on AL:

[foxidrive]

Do you mean you want to add a new column header and 816 to the end of each row?

If so then try this: BATCH.BAT file.csv AL 816

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
)

[stylishjm]

Hello, sorry that image is a bit deceiving, its a screen shot off of Excel. Also my description was a bit misleading as well!
There is no column header.
Just need to insert 816 to each row on the last empty column which will be the same for each CSV file.

For example here is a section of the CSV, I just need 816 added to the last empty column:

215725,2012-02-02 14:51:16.000,1101,1,NULL ,1,2,TO GO,816
215726,2012-02-02 14:51:19.000,1104,1,NULL ,1,2,TO GO,816
215727,2012-02-02 14:51:23.000,1108,1,NULL ,1,2,TO GO,816
215728,2012-02-02 14:51:44.000,1106,1,NULL ,1,2,TO GO,816
215729,2012-02-02 14:51:52.000,1100,0,NULL ,1,2,TO GO,816

[foxidrive]

When you say 'last empty column' I assume that no other columns with data appear after the last empty one.

Try this:

Code: Select all

@echo off
for /f "delims=" %%a in ('type "file.csv"') do (
>>"fileout.csv" echo.%%a,816
)

[stylishjm]

When you say 'last empty column' I assume that no other columns with data appear after the last empty one.

Try this:

Code: Select all

@echo off
for /f "delims=" %%a in ('type "file.csv"') do (
>>"fileout.csv" echo.%%a,816
)

That worked perfectly, thank you very much for your help!

[nightster]

Can that new column of data be inserted as a new 'column 1' or can an existing empty column be populated?

[foxidrive]

To add the data to column one just put the 816, before the %%a
see below:

Code: Select all

@echo off
for /f "delims=" %%a in ('type "file.csv"') do (
>>"fileout.csv" echo.816,%%a
)

[nightster]

That did it - thanks for your help

[MostWired]

this worked for me too.
But also wondering how can an existing empty column be populated with data?
or how do I add data to column 4?
I also need to strip the beginning and ending zeros from a field. example: 0123450, it's always 7 char long with zero at beginning and end.

[foxidrive]

this worked for me too.
But also wondering how can an existing empty column be populated with data?
or how do I add data to column 4?
I also need to strip the beginning and ending zeros from a field. example: 0123450, it's always 7 char long with zero at beginning and end.

For the first task - is the data purely numerical?

For the second task - is it one field or the entire column?

[MostWired]

Thanks for your reply!!

First Task - Here's what I have:
6,0112530,05022012,,GIFT SHOP,0.05
I need to get the "system time" to populate 4th column. Is this possible?

Second Task - In the second column, I have id numbers that start and end with zeros. I need to remove first and last zero/char. These numbers are NOT static but they are all 7 characters long.

Any help you can give would be greatly appreciated!!

[foxidrive]

Task 1: insert a column containing system time in position 5
drawbacks: empty columns before position 5 are removed.

Code: Select all

@echo off
del "newfile.csv" 2>nul
for /f "tokens=1,2,3,4,* delims=," %%a in ('type "file.csv"') do (
>>"newfile.csv" echo.%%a,%%b,%%c,%%d,%time%,%%e
)

Task 2: remove leading and trailing character in column 2
Drawbacks: some poison characters can break the batch, such as the ! character.
If column 1 is empty it will probably screw this code up.

Code: Select all

@echo off
setlocal EnableExtensions EnableDelayedExpansion
del "newfile.csv" 2>nul
for /f "tokens=1,2,* delims=," %%a in ('type "file.csv"') do (
set var=%%b
set var=!var:~1,-1!
>>"newfile.csv" echo.%%a,!var!,%%c
)

[MostWired]

Thanks for all your help. Now I just have to figure out how to code a batch file to rearrange the columns in my comma delimited file for my import. Any suggestions?

[foxidrive]

Give an example and describe what you need.

[kfriedley]

I have two comma delimited files that I need to import into two different tables.

The first file has rows with columns like this: 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,

The second file has rows with columns like this: 1,11253,GIFT SHOP,0.05,05/08/2012,14:14 ,
and needs to be like this: 1,11253,05/08/2012,14:14,GIFT SHOP,0.05,

There are no columns headers.

Any help you can provide is appreciated and many thanks for all your help so far.