Re: LOAD DATA

\\r\\n is a MS-DOS eol marker is informix on a UNIX server? brett smithj48@my-deja.com wrote: > > Colin > > This is what I get on the tail: > > 116 174 174 174 174 174 174 174 174 174 174 156 157 156 055 162 > 0004060 u n n e r | | | | | \\r \\n > 165 156 156 145 162 174 174 174 174 174 015 012 > 0004074 > > I am also using version 7.30.UC1, but next week I will be doing it on > 9.x depending what is installed. > > Also there are no Blobs, it is all numeric or text based. > > Cheers > > James > > In article <954hbc$h3p$1@news.xmission.com>, > Colin McGrath <cmm@trac3000.ueci.com> wrote: > > > > Can you try: tail <unload-filename> | od -cb > > > > The last two characters should be a pipe and a new-line character, > like: > > > S c > > 123 > 143 > > 0001300 r e e n . M i n d . B o > t h > > 162 145 145 156 056 040 115 151 156 144 056 040 102 157 164 > 150 > > 0001320 a r e b l a n k . | \\n > > 040 141 162 145 040 142 154 141 156 153 056 174 012 > > 0001335 > > > > You may be mising the final linefeed? > > (In later versions, I think the last pipe is optional.) What version > of > > I-SQL are you using? (dbload -v) > > Does each row contain the same number of fields? If you don't have > > BLOBS in your data try: > > > > cat <filename> | tr -cd '|\\012' | uniq -c > > > > 16 |||| > > > > It should return a one line display showing the number of lines in > your > > file, and just the pipes from one line: > > But it sounds like you have character data in your Money field. > > Do some rows load with dbload? and the bad rows go to your error file? > > > > smithj48@my-deja.com wrote: > > > > > > It is pipe-delimited. I am having problems with dbload it keeps > > > compaining about a bad eof. There is nothing after the last peice > of > > > data (That I can find. The files are being produced from an Oracle > > > DB). > > > > > > I also have problems with one piece of data. It is that last entry > on > > > each row (money field), and is a defined as Money in the table but > when > > > I try loading it complains about unable to convert the type. I have > > > tried using decimal but I get the same error, but if I use Char it > > > loads!!! > > > > > > > > > In article <95439e$cj7$1@news.xmission.com>, > > > Colin McGrath <cmm@trac3000.ueci.com> wrote: > > > > > > > > Is it in CSV format or is it pipe-delimited? > > > > If CSV, do you want to get rid of the quotes around the character > > > fields > > > > before you load? > > > > > > > > What's wrong with dbload? > > > > > > > > smithj48@my-deja.com wrote: > > > > > > > > > > Does anyone know of another way to load tables other than using > > > DBLOAD? > > > > > > > > > > I have data from Oracle in CSV format ("|" delimited) being > loaded > > > into > > > > > IFX 9 > > > > > > > > > > Cheers > > > > > > > > > > James > > > > > > > > -- > > > > Colin > > > > > > > > > > > > > Sent via Deja.com > > > http://www.deja.com/ > > > > > > > > > > -- > > Colin McGrath colin.mcgrath@wgint.com > > Washington Group International, Inc. 010-1-215-422-4144 > > Philadelphia, PA, USA 19103 FAX: 010-1-215-422-1445 > > Any opinions I state are my own and not necessarily those of my > employer > > > > Sent via Deja.com > http://www.deja.com/ -- ----------------------------------------------------------------- Brett's 12th law of UNIX administration... People tend not to react well when they lose control over their computers. Typically, it brings out the worst in them ... ----------------------------------------------------------------- Brett Geer - UNIX Admin/Analyst/Programmer - Intratex Holdings. Tel. +27 31 717 4000 Direct. +27 31 717 4146 Fax. +27 31 717 4001 ----------------------------------------------------------------- The little voices are talking to me again, telling me to reach for a keyboard and type rm -rf /* last week they had me rm -rf `echo $MANPATH | sed 's/:/ /g'` now I fear I have no answers -----------------------------------------------------------------

Brett Geer wrote: > \\r\\n is a MS-DOS eol marker > > is informix on a UNIX server? That \\r will be treated as the data for an extra field on a Unix platform. Remove it. There's often a dos2unix or dtou command for dealing with that -- if you don't have one, I can supply source for one. Also, SQLCMD (available from the IIUG) loads CSV data directly: sqlcmd -R -d dbase -t table -F csv -i load.file [-N rows-per-tx ...] It doesn't yet have an ignore CR (\\r) option, but maybe it should -- I've occasionally had problems with data that's travelled via a DOS system acquiring them and it is a nuisance to have to do: dos2unix load.file | sqlcmd -R -d dbase -t table -F csv -i - Primarily because if load.file is a gigabyte or two, there's a whole lot of extra copying going on for the data file. > smithj48@my-deja.com wrote: > > This is what I get on the tail: > > > > 116 174 174 174 174 174 174 174 174 174 174 156 157 156 055 162 > > 0004060 u n n e r | | | | | \\r \\n > > 165 156 156 145 162 174 174 174 174 174 015 012 > > 0004074 > > > > I am also using version 7.30.UC1, but next week I will be doing it on > > 9.x depending what is installed. > > > > Colin McGrath <cmm@trac3000.ueci.com> wrote: > > > Can you try: tail <unload-filename> | od -cb > > > > > > The last two characters should be a pipe and a new-line character, > > > [...snip...] > > > > > > You may be mising the final linefeed? > > > (In later versions, I think the last pipe is optional.) What version > > > of I-SQL are you using? (dbload -v) > > > Does each row contain the same number of fields? If you don't have > > > BLOBS in your data try: > > > > > > cat <filename> | tr -cd '|\\012' | uniq -c > > > > > > 16 |||| > > > > > > It should return a one line display showing the number of lines in > > > your file, and just the pipes from one line: > > > But it sounds like you have character data in your Money field. > > > Do some rows load with dbload? and the bad rows go to your error file? > > > > > > smithj48@my-deja.com wrote: > > > > It is pipe-delimited. I am having problems with dbload it keeps > > > > compaining about a bad eof. There is nothing after the last piece of > > > > data (That I can find. The files are being produced from an Oracle > > > > DB). > > > > > > > > I also have problems with one piece of data. It is that last entry > > > > on each row (money field), and is a defined as Money in the table but > > > > when I try loading it complains about unable to convert the type. I > > > > have tried using decimal but I get the same error, but if I use Char > > > > it loads!!! > > > > > > > > Colin McGrath <cmm@trac3000.ueci.com> wrote: > > > > > Is it in CSV format or is it pipe-delimited? > > > > > If CSV, do you want to get rid of the quotes around the character > > > > > fields before you load? > > > > > > > > > > What's wrong with dbload? > > > > > > > > > > smithj48@my-deja.com wrote: > > > > > > Does anyone know of another way to load tables other than using > > > > > > DBLOAD? > > > > > > > > > > > > I have data from Oracle in CSV format ("|" delimited) being > > > > > > loaded into IFX 9 -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"