Re: Kindly help. - Mailing list pgsql-general

The obvious answer is that PSQL uses \N as null, whereas sql server just doesn't output them unless you specify the -k
flagto 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
doingthat is that you have a text field with carriage returns in it which are leading psql to believe it has hit the
endof a record.  I think the only way to fix this is to replace them with \\n (as opposed to \n).  The problem with
thatis that you have real line breaks to specify the end of a record.  What I did was to specify something other than a
CRas a record delimiter (in my case ~) then replace all \n with \\n, then replace all ~ with \n.  There may be a
simplerway, 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)
itis 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@tpchd.org

>>> "Chetan" <chetan_a@rediffmail.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
improviseda 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
atthe 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:
errorin "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
AmbulanceService.": 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.