Re: Kindly help. - Mailing list pgsql-general

Hi Ian and Chetan,

There is a new HOw-TO type guide on the site which might also be useful,
if you've got SQL Server 7.  It uses the tools which come with SQL
Server 7 and seems very straightforward, but I haven't had any feedback
regarding it.

Regards and best wishes,

Justin Clift


Ian Harding wrote:
>
> The obvious answer is that PSQL uses \N as null, whereas sql server just doesn't output them unless you specify the
-kflag 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
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)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@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
lookat 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:
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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi