Tuesday, January 7, 2014

Convert Tab Separated Values to Comma Separated Values

Using MySQL to output CSV files can be a bit of a pain.  You have two options depending on where you want the file to end up.  If you want to write the file to the server, you can append your query with a bunch of lines detailing what separator character to use, what file name to output to, and what text delimiter to use.  This is great if you want the file to be on the server.  However, if you're accessing MySQL remotely, you usually want the file to be saved locally.  This is fairly easy, but the simple method only outputs in a tab separated values file instead of comma separated.  Since I wanted CSV, I designed a short Perl script to go through a TSV and change all the tabs to commas.  This is very similar to the entities cleaner posted earlier.

Line 2 opens the file specified in the first argument.  Line 3 sets up the variables.  Lines 4-9 replace the \t character with a comma.  Lines 10-11 close the input file and open the same file as the output file (using clobber to overwrite the original contents).  Line 12 outputs the cleaned lines to the output file and line 13 closes the file.