Re: Importing directly from BCP files

On 11/16/2015 12:15 PM, Tim Uckun wrote:
>
> On openSuSE 13.2
>
> sudo zypper install freetds-tools
>
> Ubuntu 14.04
>
> sudo apt-get install freetds-bin
>
> aklaver(at)killi:~> freebcp -h
> usage: freebcp [[database_name.]owner.]table_name {in | out} datafile
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n] [-c] [-t field_terminator] [-r row_terminator]
> [-U username] [-P password] [-I interfaces_file] [-S server]
> [-v] [-d] [-h "hint [,...]" [-O "set connection_option
> on|off, ...]"
> [-A packet size] [-T text or image size] [-E]
> [-i input_file] [-o output_file]
>
> example: freebcp testdb.dbo.inserttest in inserttest.txt -S mssql -U
> guest -P password -c
>
>
>
> From what I can make out this tool reads the BCP files and puts them
> into an SQL server. I need to either put them into a postgres server or
> to output as plain text CSV files.
>

Hmm, I read through the man page to fast. I thought it was possible to
do like pg_restore and 'restore' the binary file to a text file. That
was wrong thinking on my part.

From this thread:

https://social.msdn.microsoft.com/Forums/en-US/6525fece-cfc9-4920-8ec3-ff83899130fa/write-from-postgres-database-dump-files-in-bcp-native-data-format

the binary form of the BCP file output is undocumented. So if all the
BCP files you have are the binary(native) version you are up this creek
without a paddle.

So, moving to another creek. It depends on the amount of data you are
working with, but it might be worth it to spin up a VM in the cloud on
AWS, Azure, etc that has SQL Server on it and import the BCP files
there. You could then export the data using the character format instead
of the native format:

https://msdn.microsoft.com/en-us/library/ms190919.aspx

This should result in a CSV file that is(or can be made) suitable for
COPY into Postgres.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com