There are more times than I can count that data needs to go from one format to another, two of the most popular being in a database and another being a text format. In my case, database equates to MySQL, and text format is something that can be derived easily from a CSV file. Thankfully, the whole process is quite simple.
The basics
CSV ( or Comma Separated Values ) is a very simple format for tabular data in text format. It's extremely simple: Each row is represented as one line of text. Each column is separated via a separator ( normall a comma, hence the name ), and textual data is enclosed whithin markers ( such as quotes ) to allow for separators in textual data.
What this means is, that you need to know what is used as a marker for the following three cases before actually using a CSV file :
- What marker encloses a field ( eg a quote )
- What marker separates two fields ( eg a comma )
- What marker separates two lines ( eg a simple newline )
Though the above are the default, each program can use any marker, which may lead to confusion. Visual inspection of the data makes them obvious though, so just open a file in a text editor if in doubt.
MySQL is flexible enough to accept the above as options, so you don't actually have to change anything in the file.
Use case 1: Exporting CSV from MySQL
Let's assume you want to export data from MySQL. First you need a query that builds the data you want to export. Something like
SELECT uid,name,mail FROM users WHERE created>1325376000
will create a list of users that have been created after 1/1/2012.
What we want now, is to instruct MySQL to export this instead of returning the query. This is accomplished just by tweaking the above query as such:
SELECT uid, name, mail INTO OUTFILE '/tmp/newusers.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ‘\\’ LINES TERMINATED BY '\n' FROM users WHERE created>1325376000
Though it looks bigger, it's quite simple. The only additions are definitions for the markers discussed above.
Use case 2: Importing CSV into MySQL
This is really the same case as exporting with a small syntax twist :
LOAD DATA LOCAL INFILE '/tmp/newusers.csv' INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BT '\n' (uid,name,mail)
Where the last line defines the target fields as is the order of columns in the CSV file.