Convert SQL Server database to PostgreSQL via CSV

December 2nd, 2011

Needed to convert a client Microsoft SQLSERVER database to our PostgreSQL database server.
Lots of tables, severall Gigabytes of data.

In Windows:

  • open the SQL Server administration console for the databae and do a DIR > tables.txt to get a file with all the table names;
  • remove all the tables you will not be needing from the file;
  • now to export all the tables data to their files with a one-liner:

    FOR /F %A IN ('type tables.txt') DO bcp "dbo.%A" out "d:\export\path\%A.csv" -S SERVERNAME\SQLSERVEREXPRESS -d "databasename" -U SERVERNAME\username -C RAW -T -c -t "\t" -r "\n"

  • Notice the column separator set to TAB \t, and row separator to newline \n;
  • Codepage set to RAW (this is an ISO8859-1);
  • This is done on a local machine so using a trusted connection;
  • This line is executed in the Windows console (terminal);
  • Mind that the tables.txt needs to have only the table names per line. I removed the “dbo.” prefix in the file, so had to include it in the parameter in “dbo.%A” parameter, because I want the CSV files not to have the dbo. prefix;
  • So in short, the tables.txt file is iterated, each line containing a table name being passed in the %A variables, so that bcp command creates the CSV file.

In Linux:

  • copy the previously exported data files folder to the linux machine;
  • verify the file encoding $ file -bi filename.csv
  • convert all the folder CSV files from ISO8859-1 charset to UTF-8 if needed: $ recode ISO8859-1..UTF-8 *.csv
  • now because I need to have the data validated and reassociated, I have Ruby scripts to perform the import, using Datamapper ORM. One can use the CSV class to proceed and then do whatever needs with the data. In this example I get the data into an Hash:
  • fx_tip = "./../exported_data/some_table.csv"
    h_tip = Hash.new
    CSV.foreach(fx_tip, {:col_sep => "\t", :headers => false}) do |f|
      h_tip.merge!(f[0] => f[1])
    end
    puts h_tip.to_yaml

You can even specify a manual Query to export the SQL Server data, using:
FOR /F %A IN ('type tables.txt') DO sqlcmd -E -S SERVERNAME\SQLSERVEREXPRESS -d database -o %A -s ";" -W -Q "SELECT TOP 100 * FROM dbo.%A WHERE someting"

If your case is simpler and you need to make a straight port, you can export the SQL statements from SQL Server and then on Postgres something like:
COPY tablename FROM '/tmp/table.data' WITH DELIMITER ',';

Used Tools:
Microsoft SQL SERVER
Microsoft Windows 7
PostgreSQL
Ubuntu Linux
Ruby Language

(blogged from Vim)