Re: Importing directly from BCP files

On 11/15/2015 07:31 PM, Tim Uckun wrote:
> Does anybody have any documentation on what a BCP file from SQL
> server looks like? I have a directory full of BCP files and I would
> like to import them into PG.
>
> Has anybody done anything like this before?

I moved a database from MS Sql Server 2000 to Postgresql a few years
ago via BCP files. I used a Python script to do some fixup on the
BCP files to make them importable as CSV files into Postgresql. I
don't know if quirks I ran into are still an issue with newer versions
of Sql Server but for what it's worth, here are the comments from that
script:

# This script fixes several problems in the csv data files exported
# from Sql Server by bcp from the SW database. Specifically:
#
# * Empty strings seem to be exported as a 0x00 byte. We change
# those to a pair of double quotes ("") that Postgresql's csv
# import machinery will treat as an empty string.
# * Some text fields contain an ellipsis character (a single
# character form of "..." encoded as "\201c".) We change this
# to utf-8.
# * Some text lines that had embedded newlines are reproduced
# literals in the bcp output: the \n starts a new line.
# We since all our data starts with an id number followed by
# a tab separator, we assume lines that don't start this way
# are part of the previous line.