Tuesday, August 19, 2008

Python: the csv module and mysqlimport

Here's one way to get Python's csv module and mysqlimport to play nicely with one another.

When exporting something with the csv module, use:
csv.writer(fileobj, dialect='excel-tab', lineterminator='\n')
When importing with mysqlimport, use:
mysqlimport \
--user=USERNAME \
--password \
--columns=COLUMNS \
--compress \
--fields-optionally-enclosed-by='"' \
--fields-terminated-by='\t' \
--fields-escaped-by='' \
--lines-terminated-by='\n' \
--local \
--lock-tables \
--verbose \
DATABASE INPUT.tsv
In particular, the "--fields-escaped-by=''" took me a while to figure out. Hence, the csv module and mysqlimport will agree that '"' is escaped via '""' rather than '\"'.

4 comments:

Masklinn said...

Or you could have done it the other way around by changing the `quotechar` kwarg (I think) of csv.writer.

Shannon -jj Behrens said...

> Or you could have done it the other way around by changing the `quotechar` kwarg (I think) of csv.writer.

Right you are.

Catherine said...

Thanks much for a very sanity-saving post!

Shannon -jj Behrens said...

> Thanks much for a very sanity-saving post!

Glad I could help!