Arch Linux

mrjwalsh

Member

Registered: 2007-01-08

Posts: 52

replacing csv commas with tabs

Has anyone come accross a command, preferably in vi to replace comma's seperated fields with tab delimited fields? I know about the substitution command but as people who work with csv files might know that fields of data with commas in them have quotation marks around them, I need to keep these commas in the file.

gradgrind

Member

From: Germany

Registered: 2005-10-06

Posts: 921

Re: replacing csv commas with tabs

You could use a spreadsheet program - I expect most of them can do this transformation. Or else you could write a little script in python using its 'csv' module.

---

larch: http://larch.berlios.de

mrjwalsh

Member

Registered: 2007-01-08

Posts: 52

Re: replacing csv commas with tabs

thanks for the reply gradgrind, I can't use a spreadsheet because of the amount of rows in the data and I don't want to taint the data any further with any spreadsheet quirks that might happen. the python csv thing seems interesting, however I have never touched python scripting, anyone know where a script like this might be?

sabooky

Member

Registered: 2006-11-02

Posts: 89

Re: replacing csv commas with tabs

Not sure if I understand this 100%
But if I do, then this should work:
`fixcsv.py inputfile outputfile`
Things that are quoted because they contain a comma will no longer be quoted since thats no longer the delimiter (tab is). Also, you can uncomment the commented out line to have every field surrounded in quotes.

#!/usr/bin/python
#fixcsv.py
import csv
import sys

reader = csv.reader(open(sys.argv[1], "rb"))
writer = csv.writer(open(sys.argv[2], "wb"), delimiter='\t')
#uncomment next line if you want everything quoted
#writer = csv.writer(open(sys.argv[2], "wb"), delimiter='\t', quoting=csv.QUOTE_ALL)
writer.writerows(reader)

Be sure to backup your data just in case.

Last edited by sabooky (2007-06-06 05:00:14)

mucknert

Member

From: Berlin // Germany

Registered: 2006-06-27

Posts: 510

Re: replacing csv commas with tabs

Open vim and enter

%s/,/\t/g

Pretty easy, eh? Shouldn't touch the quotation marks at all.

---

Todays mistakes are tomorrows catastrophes.

sabooky

Member

Registered: 2006-11-02

Posts: 89

Re: replacing csv commas with tabs

Open vim and enter

%s/,/\t/g

Pretty easy, eh? Shouldn't touch the quotation marks at all.

Its not the quotation marks that he's worried about. It's the commas that are in quoted strings.

ex:
need, sleep, "tired, sleepy",thud
need [tab] sleep [tab] "tired, sleepy" [tab] thud

mrjwalsh

Member

Registered: 2007-01-08

Posts: 52

Re: replacing csv commas with tabs

sabooky, you have just made my day. I have not found a site that answers this question properly and yet I come back to the tried and true Arch forums to see if someone can work it out and bam, you've done it.

Thankyou, thankyou, thankyou

Last edited by mrjwalsh (2007-06-06 06:05:56)