Adding values to CSV File via Terminal

users
#1

Hi All,

I have files that have a ton of info and what I want to do is add a new column that's "date pulled" that is based on the file name (which is of course the date). So for instance I have

2013_01_15.csv

I would like a column at the end of the data where every row in the file has 2013_01_15. Is there an easy easy to accomplish this?

Thanks all!

Best,
Joel

#2

Hi.
First thoughts I would do that with a bash or perl script.
Steve

#3

Suggestion on a quick script to do this? Would make some LibreOffice QA work quite a bit easier :slight_smile:

Best,
Joel

#4

If the file has less than about 1,000,000 rows an easy why is to import into Calc, add the column, and then save it as a csv file.

#5

Hi Joel,

A quick trawl reveals several possibilities :

http://stackoverflow.com/questions/4749658/inserting-filename-into-last-column-of-csv-file

http://stackoverflow.com/questions/9506810/add-column-to-end-of-csv-file-using-awk-in-bash-script

http://www.unix.com/shell-programming-scripting/213419-add-extra-column-csv-file.html

etc

Alex

#6

Hi Alex.
First example implements easily.
Converts all files with extension .csv
Not sure if the OP wants to append 2012_01_02.csv or just 2012_01_02

Assuming the latter, the following worked for me in a bash file. Copy the files to a new directory for conversion as the files will be altered, new files are not created. These strip the .csv.

#!/bin/bash
for file in *.csv; do
     file1=${file%.*}
     sed -i "s/$/,$file1/" "$file"
done

For tab separated files
for file in *.csv; do
     file1=${file%.*}
     sed -i "s/$/\t$file1/" "$file"
done

Steve

#7

Testing this out today Steve, thanks!

Also thanks Alex, always helpful :slight_smile:

Might start a new thread about this one but, have a bit of a trickier one now. In the file there are lines that have UNCONFIRMED on a specific line (this is again, a pull list from FDO). What I would like to do is merge all the files but only keep the rows that have "UNCONFIRMED" (or anything else, that's just one stat that we're really interested in). So, the list is much too long to merge everything (3 months worth at this point), but I think if I could just get the UNCONFIRMED lines and merge into a new file completely (unconfirmed bugs January - March.csv), I could use the data to do some quick number crunching. The idea is we want to make sure that the QA team is moving forward always, it's good to set goals but we need to be able to track the goals and what not.

Best,
Joel

#8

Hi Joel,

Might start a new thread about this one but, have a bit of a trickier
one now. In the file there are lines that have UNCONFIRMED on a specific
line (this is again, a pull list from FDO). What I would like to do is
merge all the files but only keep the rows that have "UNCONFIRMED" (or
anything else, that's just one stat that we're really interested in).
So, the list is much too long to merge everything (3 months worth at
this point), but I think if I could just get the UNCONFIRMED lines and
merge into a new file completely (unconfirmed bugs January - March.csv),
I could use the data to do some quick number crunching. The idea is we
want to make sure that the QA team is moving forward always, it's good
to set goals but we need to be able to track the goals and what not.

Wouldn't tailoring your query in bugzilla help with this one, i.e.
limiting the query to only those with unconfirmed status, or am I
missing something ?

Alex

#9

Hi :slight_smile:
Perhaps a command-line

filename.csv | grep Unconfirmed > new-filename.csv

or something like that? or some similar function in Calc, one of the something-if commands?
Regards from
Tom :slight_smile:

#10

Indeed Tom, that simple:) Thank you!

As for other request, I want all data because unconfirmed is just one of
the many stats I pay attention to. Easiest to pull everything and then make
custom accumulated files from the entire data set.

Best,
Joel