Re: Text Data File

Try TEXTools, (see description at end). Following is an example: ============================================================================== Converting a comma/quote delimited file into a fixed length one using TEXTools ============================================================================== The Problem ----------- Given a comma/quote delimited text file, you want to convert it to a file containing fixed-length fields and write it to OUTPUT.TXT. Following is the text file, (INPUT.TXT) to convert: "now is the",25,"time for all",46,"good men" "to come to",6,"the aid of",12,"their country" ... The Solution ------------ Normally, you would use TEXTools SHIFT filter but as it is not available in the Trial version, here is a work-around that does not use the SHIFT filter: type input.txt | t repl ',' '#0d#0a' '#22' '' | t padr 15 ' ' | t join to 75 >output.txt What this "pipe" does is it simply replaces all commas with a carriage return linefeed pair and removes all double quotes. Converting the commas to CR/LFs causes each record to be split onto multiple lines, one field per line. Then, each line, (field) is padded to a width of 15 with blank characters and finally, the multiple line records are again re-combined into one (fixed- length) line per record using the JOIN TO filter. Note that the JOIN TO value (75) must be evenly divisible by the PADR value (15). The assumption here is that no field is longer than 15 characters wide and also that commas do not exist within the quoted strings. The first assumption can easily be handled by increasing the PADR value, (15) to a larger value, (being sure to increase the JOIN TO value, (75) so that it remains evenly divisible by the PADR value). The second assumption, (commas cannot exist within the quoted strings) is a bit trickier to get around and requires additions to the pipe as follows: type input.txt | t repl ', ' '~ ' ',' '#0d#0a' '#22' '' | t padr 15 ' ' >t$.txt type t$.txt | t join to 75 | t repl '~ ' ', ' >output.txt What the pipe does is it replaces all commas that are followed by a blank with a tilde character followed by a blank. Then, it proceeds as before to convert the delimited fields to fixed-length and finally, it then converts the tilde- blank combinations back to their original comma-blank combinations. Note that now the commas within the quoted strings are left unchanged so long as they are followed by a blank character, (which is generally the case). Here is the modified data to run this revision of the batch file against: "now, is the",25,"time for all",46,"good men" "to come to",6,"the aid, of",12,"their country" NOTE: Use of the SHIFT filter, (available only in the registered version of TEXTools) not only reduces the number of filters, (insignificant in this case) but also means that you can specify in exactly what columns the data resides without needing additional filters: type input.txt | t repl ', ' '~ ' '#22' '' | t shift 15 ',' 30 ',' 45 ',' 60 ',' >t$.txt type t$.txt | t repl ',' '' '~ ' ', ' >output.txt The Result ---------- now, is the 25 time for all 46 good men to come to 6 the aid, of 12 their country - - - - - - - - - - NOTE: A similar solution can be easily constructed using TEXTools to convert data that is string-delimited without quotes, (as opposed to comma-delimited with quotes). For example, data that looks like the following, (where <del> can be any unique multi-character string delimiter): now, is the<del>25<del>time for all<del>46<del>good men to come to<del>6<del>the aid, of<del>12<del>their country Barry Block barry@fireflysoftware.com http://www.fireflysoftware.com extra keywords: awk, perl - - - - - - - - - - TEXTools is a powerful integrated collection of 50+ DOS filters that you can easily combine together like building blocks to form "mini-programs", (called pipes), that process text for achieving various goals. It can be used interactively from the command prompt to quickly handle ad hoc requests, or it can be called from batch file processes, (even other programs) to handle more complex tasks. TEXTools can help you generate reports, interface incompatible software systems, extract data from cumbersome logfiles, automate manual processes, customize text output from other programs, perform text searches, format program listings, convert exported text for use by other software packages, perform base conversions, search for and/or remove duplicate data, convert tabular data to delimited and vice versa, format mailing lists for printing etc. - all by simply combining filters together! You can even create your own user-defined filters from other TEXTools filters. TEXTools includes a built-in debugging tool that enables you to view intermediate text results between filters. TEXTools can be run from native DOS or Windows 95. Download a trial version of TEXTools at: ftp://www.fireflysoftware.com/pub/TXTLS1V1.ZIP ============================================================== In article <36D9F0A1.2D35A4E8@telekom.com.my>, Ahmad Ramzi Salleh <ramzi@telekom.com.my> wrote: > Hello, > > What is the best way to get a text data file form Informix database so > that the output has a fixed column length i.e column 1 - 5 for > office_code, column 6 - 25 for office_desc, column 26 - 27 for > office_type etc.... > I also need to eliminate any delimiter and blank rows. > > Thank you in advanced. > > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own