How to export data from a SQL database into a CSV file and add some new data?

September 8, 2010 1:47 PM

How can I export data from a SQL database into a CSV file and add a new column at the same time?

This might be a really easy question for someone with SQL experience. I have none so far. So please help me out.

I want to do three things. First, I would like to have a query which lists all the records that match a specific ZIP Code. As far as I understood this should look something like this:

SELECT FirstName, LastName, Street, Place, ZIP, Email
FROM DATABASE
WHERE ZIP LIKE '2%'

Second, I want to write the results into a csv file. The rows must be separated by comma. And finally, I want the query to write a new column for each record called 'station' which will have the same value for all records.

The CSV should look something like this in the end:

Firstname, Lastname, Street, Place, ZIP, Email, Station
Thomas, Mueller, Main Street 2, Munich, 80938, [e-mail], Munich
Fritz, Mueller, Second Street 7, Erding, 87498, [e-mail], Munich

How can I do this with SQL?

posted by jfricke to computers & internet (8 answers total)

So the query would be like this:

SELECT FirstName, LastName, Street, Place, ZIP, Email, 'Munich' as Station
FROM DATABASE
WHERE ZIP LIKE '2%'

which adds a constant named Station to the query, with value Munich.

As far as getting this into a CSV, there is no generic SQL way to do this - how you would do it varies on the database you're using and/or the way you're querying it. Many database clients have some way to save to CSV or export to XLS, or copy and paste to excel, etc. Really depends on your database and client.
posted by RustyBrooks at 1:52 PM on September 8, 2010 [1 favorite]

I'm going to point out something obvious that you may not be aware of.

RustyBrooks has nailed it but correcting your initial query it should now read

SELECT FirstName, LastName, Street, Place, ZIP, Email, 'Munich' as Station
FROM DATABASE.TABLENAME
WHERE ZIP LIKE '2%'

You're selecting from a table, not from a database.

Also I don't know what sort of filter you want exactly, but the WHERE in your initial wll give you all Zips that begin with the figure 2. The percent sign is a wildcard. If you want a specific code you will need WHERE ZIP = '12345'
posted by jontyjago at 2:25 PM on September 8, 2010

The people answering above have the query part. To get the results in a file try SELECT ... INTO OUTFILE like so:

SELECT FirstName, LastName, Street, Place, ZIP, Email, 'Munich' as Station INTO OUTFILE "data.txt"
FROM DATABASE.TABLENAME
WHERE ZIP LIKE '2%'

This definitely works for mysql, YMMV with other databases.

If you are on linux with mysql you can also pipe mysql output to a file from the shell, the client is smart enough to format your data as a CSV, although there is no advantage over using SELECT INTO OUTFILE and you need to pass your select statement to the client somehow.
posted by Dr Dracator at 2:45 PM on September 8, 2010 [1 favorite]

> The rows must be separated by comma.

Did you really mean this, or did you mean the columns should be separated by commas? The rows will usually be separated by linebreaks.
posted by AmbroseChapel at 3:20 PM on September 8, 2010

I meant the columns
posted by jfricke at 3:24 PM on September 8, 2010

If you are on linux with mysql you can also pipe mysql output to a file from the shell, the client is smart enough to format your data as a CSV, although there is no advantage over using SELECT INTO OUTFILE and you need to pass your select statement to the client somehow.

You create a text file with your sql command and write it out like so:

# mysql -u username -p database <> output.txt

But that actually creates a tab delimited file, which is not what he asked for.
posted by empath at 10:11 PM on September 8, 2010

(err, mefi stripped out the stuff between brackets.. pretend there's a 'command.sql' between those angle brackets)
posted by empath at 10:14 PM on September 8, 2010

But that actually creates a tab delimited file, which is not what he asked for.

Yeah, I didn't even think about that - to me tab delimited and CSV register as equivalent.
posted by Dr Dracator at 11:15 PM on September 8, 2010