tab-delimited to csv
Can anyone suggest a way to convert a tab-delimited file to csv using standard unix utilities? I could whip up a Ruby script to do it, but I hate to reinvent the wheel. Thanks: John ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to freebsd-questions-unsubscr...@freebsd.org
Re: tab-delimited to csv
John Almberg wrote: Can anyone suggest a way to convert a tab-delimited file to csv using standard unix utilities? I could whip up a Ruby script to do it, but I hate to reinvent the wheel. Thanks: John ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to freebsd-questions-unsubscr...@freebsd.org if you just dealing with a few files, you could use thing like vi or and editor w/ regex capbilites to search and replace tabs w/ comma's. perl -pe ’s/\t/,/g’ table.tsv table.csv ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to freebsd-questions-unsubscr...@freebsd.org
Re: tab-delimited to csv
On Mon, 16 Feb 2009 11:55:50 -0500, John Almberg jalmb...@identry.com wrote:
Can anyone suggest a way to convert a tab-delimited file to csv using
standard unix utilities? I could whip up a Ruby script to do it, but
I hate to reinvent the wheel.
I think it's more simple with sed. Use the global substitution
function, such as
% sed s/\t/:/g
See that \t or maybe [ \t]* may be the appropriate field delimiter.
Instead of :, take , or . as separator, just as you need.
Another solution could be awk.
% awk '{ gsub([\t]*, :, $0); print $0; }'
If you need additional re-ordering, use -F or FS to specify
the field separator, and then printf %s:%s:%s\n, $2, $1, $3;.
These would be the easiest (I think) substitution approaches.
--
Polytropon
From Magdeburg, Germany
Happy FreeBSD user since 4.0
Andra moi ennepe, Mousa, ...
___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to freebsd-questions-unsubscr...@freebsd.org
Re: tab-delimited to csv
On Feb 16, 2009, at 12:16 PM, Adam Vande More wrote: John Almberg wrote: Can anyone suggest a way to convert a tab-delimited file to csv using standard unix utilities? I could whip up a Ruby script to do it, but I hate to reinvent the wheel. Thanks: John ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to freebsd-questions- unsubscr...@freebsd.org if you just dealing with a few files, you could use thing like vi or and editor w/ regex capbilites to search and replace tabs w/ comma's. perl -pe ’s/\t/,/g’ table.tsv table.csv There's more to csv than commas, though. For example, if one of the fields contains commas, you need to wrap the field with quotes. -- John ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to freebsd-questions-unsubscr...@freebsd.org
Re: tab-delimited to csv
On Mon, Feb 16, 2009 at 11:55:50AM -0500, John Almberg wrote: Can anyone suggest a way to convert a tab-delimited file to csv using standard unix utilities? I could whip up a Ruby script to do it, but As long as the files don't contain commas themselves, it is a straightforward sed or perl command: replace '\t' with ',' And any field has internal double quotes, it becomes more difficult. See http://en.wikipedia.org/wiki/Comma-separated_values I hate to reinvent the wheel. I'd whip up that script. There is a shareware tab2csv utility for windows for $49.95: http://www.download32.com/info-pack-com-tab2csv-i31827.html OTOH, if you have a spreadsheet program like Gnumeric or OpenOffice installed, you might be able to script those to import from tab-delimited and export to CSV. Admittedly that is like using a nuke to kill a fly. Roland -- R.F.Smith http://www.xs4all.nl/~rsmith/ [plain text _non-HTML_ PGP/GnuPG encrypted/signed email much appreciated] pgp: 1A2B 477F 9970 BA3C 2914 B7CE 1277 EFB0 C321 A725 (KeyID: C321A725) pgpGnYpU9WLBf.pgp Description: PGP signature
Re: tab-delimited to csv
On Feb 16, 2009, at 12:55 PM, Roland Smith wrote: On Mon, Feb 16, 2009 at 11:55:50AM -0500, John Almberg wrote: Can anyone suggest a way to convert a tab-delimited file to csv using standard unix utilities? I could whip up a Ruby script to do it, but As long as the files don't contain commas themselves, Right, that's the tricky bit. I could use tr otherwise. I hate to reinvent the wheel. I'd whip up that script. There is a shareware tab2csv utility for windows for $49.95: http://www.download32.com/info-pack-com-tab2csv- i31827.html I'm working on it, right now. I also saw that windows utility, but doesn't help me much. OTOH, if you have a spreadsheet program like Gnumeric or OpenOffice installed, you might be able to script those to import from tab- delimited and export to CSV. Admittedly that is like using a nuke to kill a fly. Actually, the problem arises because I have a client who is exporting a 'database' file from Excel 2000 (don't ask), to .csv, and Excel is so stupid that it is not putting quotes around a field that contains commas. Duh. Excel seems to export to tab-delimited format without making any fatal errors, but I need a real .csv file for import. Thus my need to convert from tab to (real) csv. -- John ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to freebsd-questions-unsubscr...@freebsd.org
Re: tab-delimited to csv
John Almberg wrote: On Feb 16, 2009, at 12:55 PM, Roland Smith wrote: On Mon, Feb 16, 2009 at 11:55:50AM -0500, John Almberg wrote: Can anyone suggest a way to convert a tab-delimited file to csv using standard unix utilities? I could whip up a Ruby script to do it, but As long as the files don't contain commas themselves, Right, that's the tricky bit. I could use tr otherwise. I hate to reinvent the wheel. I'd whip up that script. There is a shareware tab2csv utility for windows for $49.95: http://www.download32.com/info-pack-com-tab2csv-i31827.html I'm working on it, right now. I also saw that windows utility, but doesn't help me much. OTOH, if you have a spreadsheet program like Gnumeric or OpenOffice installed, you might be able to script those to import from tab-delimited and export to CSV. Admittedly that is like using a nuke to kill a fly. Actually, the problem arises because I have a client who is exporting a 'database' file from Excel 2000 (don't ask), to .csv, and Excel is so stupid that it is not putting quotes around a field that contains commas. Duh. Excel seems to export to tab-delimited format without making any fatal errors, but I need a real .csv file for import. Thus my need to convert from tab to (real) csv. -- John There is this: http://www.sat.dundee.ac.uk/arb/psion/ Have no idea if it complies or works as you want. But if you're dealing with just one so called database from Excel I would go with either checking the settings on the Excel export(in OO.org you can specify w/ or w/out quotes) as they may have missed the option. Or simply get the original file, open it in OO.org and do it from there as was basically suggested earlier. I would have thought something like would exist as it's certainly useful like dos2unix ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to freebsd-questions-unsubscr...@freebsd.org
Re: tab-delimited to csv
On Feb 16, 2009, at 1:25 PM, Adam Vande More wrote: John Almberg wrote: On Feb 16, 2009, at 12:55 PM, Roland Smith wrote: On Mon, Feb 16, 2009 at 11:55:50AM -0500, John Almberg wrote: Can anyone suggest a way to convert a tab-delimited file to csv using standard unix utilities? I could whip up a Ruby script to do it, but As long as the files don't contain commas themselves, Right, that's the tricky bit. I could use tr otherwise. I hate to reinvent the wheel. I'd whip up that script. There is a shareware tab2csv utility for windows for $49.95: http://www.download32.com/info-pack-com- tab2csv-i31827.html I'm working on it, right now. I also saw that windows utility, but doesn't help me much. OTOH, if you have a spreadsheet program like Gnumeric or OpenOffice installed, you might be able to script those to import from tab- delimited and export to CSV. Admittedly that is like using a nuke to kill a fly. Actually, the problem arises because I have a client who is exporting a 'database' file from Excel 2000 (don't ask), to .csv, and Excel is so stupid that it is not putting quotes around a field that contains commas. Duh. Excel seems to export to tab-delimited format without making any fatal errors, but I need a real .csv file for import. Thus my need to convert from tab to (real) csv. -- John There is this: http://www.sat.dundee.ac.uk/arb/psion/ Have no idea if it complies or works as you want. But if you're dealing with just one so called database from Excel I would go with either checking the settings on the Excel export(in OO.org you can specify w/ or w/out quotes) as they may have missed the option. That was my first hope, but there doesn't seem to be a quote option in Excel 2000, hard as that is to believe... Unfortunately, they are a remote client, so I can't look at the 'Save As' options myself, but I spent a long time on the phone with them, trying to get them to look for such an 'advanced' option. No luck. It's either not there, or they are blind. Or simply get the original file, open it in OO.org and do it from there as was basically suggested earlier. That would be easy, but they upload this file frequently, and I need an automated solution. I would have thought something like would exist as it's certainly useful like dos2unix Me too. Weird. I've got a prototype working, but now I've discovered that even the tab delimited file is malformed... the Ruby CSV Library chokes on one of the data lines. Illegal use of quotes. Bummer... -- John ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to freebsd-questions-unsubscr...@freebsd.org
