Adding a column to a csv file and populating it

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Message
Author
stylishjm
Posts: 10
Joined: 10 Feb 2012 03:34

Adding a column to a csv file and populating it

#1 Post by stylishjm » 10 Feb 2012 03:42

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:
Image

foxidrive
Expert
Posts: 6031
Joined: 10 Feb 2012 02:20

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

#2 Post by foxidrive » 10 Feb 2012 04:26

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
Posts: 10
Joined: 10 Feb 2012 03:34

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

#3 Post by stylishjm » 10 Feb 2012 04:51

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
Expert
Posts: 6031
Joined: 10 Feb 2012 02:20

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

#4 Post by foxidrive » 10 Feb 2012 05:55

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
Posts: 10
Joined: 10 Feb 2012 03:34

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

#5 Post by stylishjm » 10 Feb 2012 06:58

foxidrive wrote: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
Posts: 2
Joined: 23 Apr 2012 08:06

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

#6 Post by nightster » 23 Apr 2012 09:00

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

foxidrive
Expert
Posts: 6031
Joined: 10 Feb 2012 02:20

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

#7 Post by foxidrive » 23 Apr 2012 09:53

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
Posts: 2
Joined: 23 Apr 2012 08:06

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

#8 Post by nightster » 23 Apr 2012 11:44

That did it - thanks for your help

MostWired
Posts: 3
Joined: 02 May 2012 16:05

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

#9 Post by MostWired » 02 May 2012 16:17

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
Expert
Posts: 6031
Joined: 10 Feb 2012 02:20

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

#10 Post by foxidrive » 02 May 2012 16:48

MostWired wrote: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
Posts: 3
Joined: 02 May 2012 16:05

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

#11 Post by MostWired » 03 May 2012 09:18

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
Expert
Posts: 6031
Joined: 10 Feb 2012 02:20

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

#12 Post by foxidrive » 03 May 2012 09:37

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
Posts: 3
Joined: 02 May 2012 16:05

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

#13 Post by MostWired » 09 May 2012 16:47

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
Expert
Posts: 6031
Joined: 10 Feb 2012 02:20

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

#14 Post by foxidrive » 09 May 2012 18:19

Give an example and describe what you need.

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

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

#15 Post by kfriedley » 14 May 2012 13:29

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.
Last edited by kfriedley on 15 May 2012 09:09, edited 1 time in total.

Post Reply