Loading a large ascii file

Hi, I'm trying to load a large non-delimited ascii file (2million rows) into a table but get the message "Statement is too long - 4096." I'm using dbload to execute the following script: FILE "test.txt" ( PROV 1-1, INST 2-5, FY 6-9, ... ); INSERT INTO test VALUES ( PROV, INST, FY,... ); Is there an alternative that will work that will work with this size? Row Size 1036 Number of Columns 236 Thank you, Tony

check out the load faq - www.artentech.com/downloads.htm. I would be more apt to use HPL than dbload. cheers j. ----- Original Message ----- From: "Demeis, Tony" <Tony.Demeis@moh.gov.on.ca> To: <ids@iiug.org> Sent: Wednesday, April 28, 2004 4:01 PM Subject: Loading a large ascii file [2901] > Hi, > > I'm trying to load a large non-delimited ascii file (2million rows) into a > table but get the message "Statement is too long - 4096." I'm using dbload> to execute the following script: > > FILE "test.txt" > ( > PROV 1-1, > INST 2-5, > FY 6-9, > .. > ); > > INSERT INTO test > VALUES > ( > PROV, > INST, > FY,> .. > ); > > > Is there an alternative that will work that will work with this size? > Row Size 1036 > Number of Columns 236 > > > > Thank you, > Tony > >

Tony Assuming you are running on a variety of unix you could preprocess your input file to insert a delimiter. Your fields are obviously fixed position and fixed length, so try awk with substr to split the line up and then printf to recreate with delimiters. You already have most of the basis of this this (for chopping) in your existing script. Keith -> -----Original Message----- -> From: Demeis, Tony [mailto:Tony.Demeis@moh.gov.on.ca] -> Sent: Wednesday, April 28, 2004 8:02 PM -> To: ids@iiug.org -> Subject: Loading a large ascii file [2901] -> -> -> Hi, -> -> I'm trying to load a large non-delimited ascii file -> (2million rows) into a -> table but get the message "Statement is too long - 4096." -> I'm using dbload -> to execute the following script: -> -> FILE "test.txt" -> ( -> PROV 1-1, -> INST 2-5, -> FY 6-9, -> .. -> ); -> -> INSERT INTO test -> VALUES -> ( -> PROV, -> INST, -> FY, -> .. -> ); -> -> -> Is there an alternative that will work that will work with this size? -> Row Size 1036 -> Number of Columns 236 -> -> -> -> Thank you, -> Tony -> -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **

Investigate DBLDFMT from the IIUG archives - it is designed to convert fixed format data into delimited format. It does a number of other conversions that are occasionally useful, too (adding explicit decimal points to data with implied decimal points, reformatting date/time values, etc). -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" forum.subscriber@iiug.org wrote on 04/30/2004 02:29:25 AM: > Tony > > Assuming you are running on a variety of unix you could preprocess > your input file to insert a delimiter. Your fields are obviously > fixed position and fixed length, so try awk with substr to split the > line up and then printf to recreate with delimiters. You already have > most of the basis of this this (for chopping) in your existing script. > > Keith > > -> -----Original Message----- > -> From: Demeis, Tony [mailto:Tony.Demeis@moh.gov.on.ca] > -> Sent: Wednesday, April 28, 2004 8:02 PM > -> To: ids@iiug.org > -> Subject: Loading a large ascii file [2901] > -> > -> > -> Hi, > -> > -> I'm trying to load a large non-delimited ascii file > -> (2million rows) into a > -> table but get the message "Statement is too long - 4096." > -> I'm using dbload > -> to execute the following script: > -> > -> FILE "test.txt" > -> ( > -> PROV 1-1, > -> INST 2-5, > -> FY 6-9, > -> .. > -> ); > -> > -> INSERT INTO test > -> VALUES > -> ( > -> PROV, > -> INST, > -> FY, > -> .. > -> ); > -> > -> > -> Is there an alternative that will work that will work with this size? > -> Row Size 1036 > -> Number of Columns 236 > -> > -> > -> > -> Thank you, > -> Tony > -> > -> > > > ******************************************************************************** ** > This message is sent in strict confidence for the addressee only. It may > contain legally privileged information. The contents are not to be disclosed > to anyone other than the addressee. Unauthorised recipients are requested > to preserve this confidentiality and to advise the sender immediately of any > error in transmission. > This footnote also confirms that this email message has been swept for the > presence of computer viruses, however we cannot guarantee that this message > is free from such problems. > ******************************************************************************** ** > >