Re: Urgent: LOAD problem #2

David Williams wrote: > > In article <349aa04e.29161837@netnews.upenn.edu>, Al Wang > <alwang@NOSPAMdoubt.com> writes > >Hi, > > > >More questions from a desparate Informix novice: > > > >If you're reading in a textfile with records that have a LOT of > >fields(100+, many in nested and collection datatypes) is there any way > >to signal the end of a record with a delimiter, and have the parser > >ignore carriage returns? Carriage returns seem like a very inaccurate > >way to process an ASCII file, and most other databse products I've > >worked with allow other options... > > > Nope carraige return delimits records in a load file. Remember this > is UNIX where text files are lines of test and end in '\\n' the way > C like them!! > One solution to this is to make the field a BLOB TEXT field. The bummer is that although the text will load, carriage-returns and all, it will be unsearchable. Your retrieval software will have to LOCATE the text upon retrieval. Maybe in the future BLOB TEXT fields will be searchable. Also be sure with this or any method that no pipe character exists in the text or it'll throw the load off. You can specify any delimiter, even a control-char, besides pipe, if you think a pipe will cause problems. An example would be a table that stores C-language source code in a TEXT field, such as a problem reporting system for software developers. In C, a "||" is used for the "OR" in an IF statement, and thusly poisoning the load. I speak from experience here. :-) I ended up using a control-char as a delimiter just to get the data to load. One other alternative is to strip out the carriage-returns with a simple C-program to replace them with either nothing or a space. You could use the tr program as well, or do this in the source data base upon extraction. Another alternative is to chop up your text into managable sections that could be loaded into VARCHAR fields, i.e.: mytext: When will Informix get a new marketing strategy the current one sucks. would be converted into: field1: When will Informix get a new marketing field2: strategy the current one sucks. This way you would retain the ability to search on those fields. Perhaps your dump routines from the other data base product could leave out a carriage return as well if you go VARCHAR. Formatting would then be at the mercy of the data retrieval software, and you don't store a lot of CRs. For a large database, this would save significant amounts of space. VARCHARs would also be limited to a certain length whereas the TEXT field would not. I'd opt for the searchability and the extra effort chopping up the field into smaller varchar fields. Just my two cents... :-) Tim > > >Thanks, > >Al > >Al Wang > >http://www.seas.upenn.edu/~alwang > >remove NOSPAM to reply > > -- > David Williams -- Tim Schaefer \\\\|// tschaefe@mindspring.com 6 6 -------------------------------oOOo---( )---o00o---------------------- http://www.inxutil.com http://www.informix.com http://www.iiug.org news://comp.databases.informix mailto:majordomo@iiug.org no subject body: subscribe linux-informix ======================================================================