Export Data From Database to CSV File

Microsoft Access (2007, 2003) | MySQL | Postgres | MS SQL Server | Oracle | The Enterprise Data Publisher

SpatialKey requires data to be uploaded and imported by way of a CSV file before a dataset can be imported.  We have tried to make the import process as fun and simple as possible, but SpatialKey cannot help you get your data into CSV format.  In many cases that precious data you need access to will be locked away inside of a database.  There are many types of databases, and most of them have capabilities to help get your data exported into a CSV format but they are usually specific to the database in question.

This goal of this article is to examine how to get data into a CSV format from many of the more poplar databases and database management systems on the market today.  If you do not have access to the database where your data resides and have a database administrator or IT staff that generates your reports for you, pass on the link to this article when requesting data for SpatialKey as it may save them some time as well.

Please note that this article deals with usage of the Structured Query Language (SQL) commands needed to run against a database and is intended for users who are familiar with SQL and tools for the database they commonly use.

Microsoft Access:

Microsoft Access is a popular file based database used typically by small organizations with limited numbers of users and by individuals as well.  MS Access data can be accessed using the MS Access application in a visual way as well as through traditional SQL based approaches.  As MS Access has recently undergone a major user interface change, we will explore both the 2007 and 2003 versions of the application (if you are working with an older version, similar techniques should also be available).

Access 2007:

In the first figure (1.1) you can see I have opened up the standard example database that comes with MS Access (called Northwind).  I have also selected the query called “Top Ten Orders by Sales Amount”.  To this query, I have added customer address information since SpatialKey loves data that contains temporal (time and date) and geospatial (data that can be translated to a location) data.

Fig. 1.1 – MS Access 2007

From here I can easily export the results to a CSV file:

1. Click on the table or query to export from (in this example “Top Ten Orders by Sales Amount” on the left)
2. Click the “External Data” tab at the top of the window
3. In the “Export” section click “Text File” and a wizard will appear
4. Choose a location for the exported CSV and name your file (make sure the file ends with a .csv extension)
5. Click OK
6. On the next screen be sure the “Delimited” option is selected
7. Click the “Advanced…” button in the lower left of the window
8. SpatialKey stores its data as Unicode UTF-8, we need to ensure that the data is exported in this format
   1. Click the drop-down box next to Code-Page
   2. Choose Unicode (UTF-8) in the options list
   3. Click OK
9. Back at the Export Text window click “Next”
10. Be sure “Comma” is selected as the delimiter and Text Qualifier is a double quote: “
11. Click the checkbox “Include Field Names on First Row” (should be selected)
12. Click “Next”
13. Verify the file name and location and click “Finish”
14. The final screen of the wizard gives you the option to save the steps allowing for easy re-exporting of the data in the future. If you anticipate needing to update the data in SpatialKey go ahead and check the checkbox to save some time in the future. Close the window when finished.

If you open the file in a text editor, you should see something like this:

Fig. 1.2 – Exported Northwind Data

Now you are ready to upload the new CSV File to SpatialKey.

MS Access 2003:

The next figure shows the older MS Access 2003 version of the Northwind database. I will be exporting the saved query called “Orders Qry” on the left.

Fig. 1.3 – MS Access 2003 Northwind Database

Now you are ready to upload the new CSV File to SpatialKey.

MySQL:

MySQL is a popular open source enterprise ready database.  There are many tools available for interacting with this database, but as with most enterprise databases most access happens via Structured Query Language.  There are a few options to export data to CSV from MySQL.  Some require third part tools, while another uses a command line tool (useful on Unix machines) and a final option via SQL.  Lets take a look at the last two options:

MySQL command line tool (on Unix):

mysql -u exampleuser -p letmein exampledb -B -e "select * from
\'person\';" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

So what does all of that mean?

• mysql : the executable file (should be where MySQL is installed)
• -u exampleuser : a switch and option for the username to execute the SQL with
• -p letmein : a switch and option for the password of the user
• exampledb : the database to run the SQL against
• -B : instructs that the output should be tab delimited (we will convert this to commas later in the command)
• -e “the sql statement here” : the SQL statement to run returning your data
• | sed ’s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’ : sed is a Unix stream processor, essentially allow for transformations in this case. Here we have four sed commands that change the tabs to double quotes, adds double quotes to the beginning and end of each line and adds a new line marker at the end of each line.
• > filename.csv : outputs the results to the file named filename.csv

Now you are ready to upload the new CSV File to SpatialKey.

MySQL SQL Syntax:

SELECT column_a,column_b,column_c+column_d INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

The MySQL select statement is using an INTO OUTFILE command to output the results of the SQL select into a file (in this case /tmp/result.csv).

A couple of notes:

• In both cases the file is saved to a location local to where the database is being ran from (on the same machine, or to a mount/drive available to the machine)
• The header row will need to be added to both files (which can be done using any text editor or spreadsheet program)

Now you are ready to upload the new CSV File to SpatialKey.

Postgres:

Postgres is another popular open source enterprise ready database.  Similar in nature to MySQL, there are many third party tools (including PGAdmin from Postgres) for managing and administering the database.  The best bet for exporting CSV data from Postgres is by using a Postgres specific SQL command called COPY:

COPY (SELECT column_a,column_b FROM whatever) TO '/tmp/dump.csv' WITH CSV HEADER

This command is extremely fast and produces a properly formatted CSV file including the headers.

Now you are ready to upload the new CSV File to SpatialKey.

MS SQL Server:

SQL Server is a commercially available database from Microsoft (there is a smaller, less featured version called SQL Server Express as well that is available for download for free).  SQL Server is an enterprise ready database used by many companies around the world.  Over the years there have been many management tools both from third parties and from Microsoft (Enterprise Manager, Management Studio, and through MS Developer Studio, for example).  Two options for exporting as CSV from a MS SQL Server are use of the BPC (Bulk Copy Program) command line tool or through Data Transformation Services.

BCP:

Bulk Copy Program can be executed from a command line or through SQL.

• Command line:
  • BCP <table name> out <filename.csv> -c -t, -U <user name> -P <password> -S<server name>
  • Switches:
    • -c : export as ASCII with a tab delimiter and carriage return/line feed line terminator
    • -t, : override the tab delimiter with a comma
    • -U : username
    • -P : password
    • -S : server to connect to
• SQL:
  • MS SQL Server has a Transact-SQL command called xp_cmdshell, this command allow you to execute command line executables from in SQL
  • xp_cmdshell BCP <table name> out <filename.csv> -c -t, -T -S<server name>
    • Note that I changed the -U and – P to a -T which uses a trusted connection as we are probably authenticating to the SQL server machine already to run the SQL command.

DTS:
DTS packages can be created using SQL scripts or through a MS user interface.

1. From Enterprise Manager choose > Wizards > Data Transformation > Export Data
2. Choose your source database
3. For the destination select Text File and specify the destination file and filename with a .csv extension
4. Set the table or query to pull the data from
5. Set the option that first row contains column information
6. On the screen to select the delimiter, be sure the comma is selected
7. Run the package

Now you are ready to upload the new CSV File to SpatialKey.

Oracle:

Oracle is a commercially available database that has a large following in large companies.  Like SQL Server, Oracle is an enterprise ready database.  Options for extracting to CSV using Oracle include using Oracle SQL Developer (a free tool from Oracle) and directly using PL/SQL (generally by creating a stored procedure as it is a bit more code based than the other database solutions).

Oracle SQL Developer:

This is by far the easiest way for someone not familiar with complex PL/SQL stored procedures to export data.

One way to do this is to create a Report in SQL Developer and export the results

1. Once the report is created and ran, right click on the results grid and choose “Export” from the context menu
2. In the next screen select CSV as the format and enter in the filename.csv and location

You can also export a single table by:

1. Right-click the table name in the object tree view.
2. Select Export.
3. Select CSV. The Export Data window shows up.
4. Click Format tab.
5. Select Format as: CSV
6. Enter a file name and location.
7. Click Columns tab.
8. Check the columns you wish to export
9. Click Where tab and add any criteria needed to filter the data
10. Click Apply.

PL/SQL:

The best way to export data via PL/SQL is to create a stored procedure that uses the UTL_FILE command.

For a great example stored procedure for this, please see this article at oracle.com.  Note that there is a recommended change to the original stored procedure located in the comments (properly handles the quoting of data with reserved CSV characters).

Now you are ready to upload the new CSV File to SpatialKey.

The Enterprise Data Publisher:

For our enterprise customers that have large amounts of data to import or need frequently updated data into SpatialKey, we offer an additional product called the Enterprise Data Publisher.  This is a service based product that runs behind the scenes (and your firewall) that can be scheduled to push data automatically out to SpatialKey from your databases.  If you are interested in finding out more about the capabilities and cost of the Enterprise Data Publisher please send us an email by going to our Contact Us page.  We would be glad to help you with your data needs!

In conclusion, SpatialKey needs your data to shine!  Producing data from an existing database does not have to be a daunting task as the steps listed above show, and is an important first step to truly Unlocking your Data!