Re: Kindly help.

The obvious answer is that PSQL uses \N as null, whereas sql server just doesn't output them unless you specify the -k flag to bcp then specify WITH NULL AS '' in your copy statement. Of course, that might not be it.

The error is due to it interpreting one or more too many record or field delimiters. Another possible cause for it doing that is that you have a text field with carriage returns in it which are leading psql to believe it has hit the end of a record. I think the only way to fix this is to replace them with \\n (as opposed to \n). The problem with that is that you have real line breaks to specify the end of a record. What I did was to specify something other than a CR as a record delimiter (in my case ~) then replace all \n with \\n, then replace all ~ with \n. There may be a simpler way, but I don't know what it is.

Here is the tcl script I used.

#!/usr/pkg/bin/tclsh
set file [open ../tblnames r]
set flist [read -nonewline $file]
close $file
set flist [split $flist \n]
foreach f $flist {
set file [open $f r]
set data [read -nonewline $file]
close $file
regsub -all {\000} $data {} data
regsub -all {\n} $data \\\n data
regsub -all {~} $data \n data
set file [open $f w]
puts -nonewline $file $data
close $file
}

The line replacing the \000 with {} is because I had stray NULLs in my data.

The script I used to dump data is here

set file [open "C:\\tablelist.txt" r]
while {![eof $file]} {
set table [gets $file]
exec bcp planning..$table out $table -c -k -S192.168.100.1-Usa -Ppassword -r ~
}

Try that and let me know if it works....

PS It is important to fix the CR/LF issue as well, I suspect if you downloaded to the Linux box via FTP (in ascii mode) it is already fixed, but if not you will need to account for that too. It can lead to obscure problems down the road.

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: iharding(at)tpchd(dot)org

>>> "Chetan" <chetan_a(at)rediffmail(dot)com> 01/16/02 10:41PM >>>

Sir,

I am trying to migrate a MS SQL Database to PostgreSQL. I read the HOW TO published on PostgreSQL site. I improvised a bit. This is what I did ...

Some Details...
=>>Table has 22 Columns with
=>>6 Columns/Field NO NULLLs rest allows NULLs

0. Using MS DTS I dumped the Database in a flat file
Row Delimiters => LF
Column Delimiter => Vertical Bar.
Test Qualifire None => NONE.
1. I downloaded the file to Linux box.
2. Loged in using 'psql'
3. Issued following command ...
COPY table_name FROM 'file' USING DELIMITERS '|';

It is returning Err stating...
ERROR: copy: line 2, CopyFrom: Fail to add null value in not null attribute city

I am attaching the file containing couple of records (in real database there are 51,208 records in all) you may look at the format MS DTC has generated.

I tried using 'bcp' and specifing various Delimiters but COPY command gives following err...
========================================================
COPY bc_organizations FROM '/var/lib/pgsql/org2.txt' USING DELIMITERS '|'; ERROR: copy: line 2, pg_atoi: error in "MEDICAL Surgical & OBDS & OBS & Gynaeco. Accidents & Emergency SVC Round The Clock. Painless & Scarless Surgery (LaproScopic-Surgery). X-Ray,ECG, Ultra Sonogram, Computerised Lab. Health Checkup, Packages, 24 hour Ambulance Service.": can't parse "MEDICAL Surgical & OBDS & OBS & Gynaeco. Accidents & Emergency SVC Round The Clock. Painless & Scarless Surgery (LaproScopic-Surgery). X-Ray,ECG, Ultra Sonogram, Computerised Lab. Health Checkup, Packages, 24 hour Ambulance Service."
=========================================================
Please help me.

Regards,
Chetan A.