manipulating data files

Hi, I have just basic knowledge of perl. The task I need to accomplish is as follows: source file needs to be converted to target file. source file will be a fixed column length ASCII file of data. target file will be a PIPE ('|') seperated ASCII file of data. For eg:- source file will be as follows: John Smith 745-678-089011-10-1955 Robert IhaveAlongName345-567-453219-04-1967 Here the first 22 char is fixed length for name Starting from 23rd char next 12 char is for SSN Starting from 35th char next 10 char is for DOB. Now this needs to be changed to John Smith|745-678-0890|11-10-1955| Robert IhaveAlongName|345-567-4532|19-04-1967| The reason for this is that informix dbload or high performance loader needs a field delimiter. How can this be achieved in perl. thanks.

triggerfish2001@hotmail.com writes: > source file will be as follows: > > John Smith 745-678-089011-10-1955 > Robert IhaveAlongName345-567-453219-04-1967 > > Here the first 22 char is fixed length for name > Starting from 23rd char next 12 char is for SSN > Starting from 35th char next 10 char is for DOB. > > Now this needs to be changed to > John Smith|745-678-0890|11-10-1955| > Robert IhaveAlongName|345-567-4532|19-04-1967| The most common way to handle this is probably to split the data into fields using substr or unpack, and then combine them back together with the delimiter. However, an alternative method is so simply insert the delimiter into the existing string at the correct positions. perl -pe 'foreach $field (21, 34, 45) { substr $_, $field, 0, "|" }' -- Ren Maddox ren@tivoli.com

In article <m3n1g0hgpa.fsf@dhcp11-177.support.tivoli.com> on 19 Oct 2000 12:41:05 -0500, Ren Maddox <ren.maddox@tivoli.com> says... + triggerfish2001@hotmail.com writes: + + > source file will be as follows: + > + > John Smith 745-678-089011-10-1955 + > Robert IhaveAlongName345-567-453219-04-1967 + > + > Here the first 22 char is fixed length for name + > Starting from 23rd char next 12 char is for SSN + > Starting from 35th char next 10 char is for DOB. + > + > Now this needs to be changed to + > John Smith|745-678-0890|11-10-1955| + > Robert IhaveAlongName|345-567-4532|19-04-1967| + + The most common way to handle this is probably to split the data into + fields using substr or unpack, and then combine them back together + with the delimiter. + + However, an alternative method is so simply insert the delimiter into + the existing string at the correct positions. + + perl -pe 'foreach $field (21, 34, 45) { substr $_, $field, 0, "|" }' 1. That doesn't handle the first example, which requires stripping the trailing spaces from the first field. 2. To avoid brain warp, it might be better to do those substitutions right-to-left, so columns can be counted from the original, not the successive copies: (43, 33, 21) This is a common pattern for, for example, editing a file at specific line numbers -- work back to front. -- (Just Another Larry) Rosler Hewlett-Packard Laboratories http://www.hpl.hp.com/personal/Larry_Rosler/ lr@hpl.hp.com

Larry Rosler <lr@hpl.hp.com> writes: > In article <m3n1g0hgpa.fsf@dhcp11-177.support.tivoli.com> on 19 Oct 2000 > > 12:41:05 -0500, Ren Maddox <ren.maddox@tivoli.com> says... > + triggerfish2001@hotmail.com writes: > + > + > source file will be as follows: > + > > + > John Smith 745-678-089011-10-1955 > + > Robert IhaveAlongName345-567-453219-04-1967 > + > > + > Here the first 22 char is fixed length for name > + > Starting from 23rd char next 12 char is for SSN > + > Starting from 35th char next 10 char is for DOB. > + > > + > Now this needs to be changed to > + > John Smith|745-678-0890|11-10-1955| > + > Robert IhaveAlongName|345-567-4532|19-04-1967| > + [snip] > + perl -pe 'foreach $field (21, 34, 45) { substr $_, $field, 0, "|" }' > > 1. That doesn't handle the first example, which requires stripping the > trailing spaces from the first field. It's amazing the things that your brain can simply filter out. I never even noticed that the spaces were to be stripped. Doh! > 2. To avoid brain warp, it might be better to do those substitutions > right-to-left, so columns can be counted from the original, not the > successive copies: > > (43, 33, 21) You aren't kidding about the brain warp... I was certainly confused when I was testing it, but I just thought I was being mathematically obtuse. Proceeding backwards as you suggest is *so* much cleaner... thanks! -- Ren Maddox ren@tivoli.com

triggerfish2001@hotmail.com wrote: > I have just basic knowledge of perl. The task I need to accomplish is as > follows: > > source file needs to be converted to target file. > source file will be a fixed column length ASCII file of data. > target file will be a PIPE ('|') seperated ASCII file of data. > > For eg:- > source file will be as follows: > > John Smith 745-678-089011-10-1955 > Robert IhaveAlongName345-567-453219-04-1967 > > Here the first 22 char is fixed length for name > Starting from 23rd char next 12 char is for SSN > Starting from 35th char next 10 char is for DOB. > > Now this needs to be changed to > John Smith|745-678-0890|11-10-1955| > Robert IhaveAlongName|345-567-4532|19-04-1967| > > The reason for this is that informix dbload or high performance loader needs > a field delimiter. DB-Load accepts fixed format input without delimiters - RTFM. You could also get DBLDFMT from the IIUG software archive. This can do a number of transformations on the fixed-width data which DB-Load cannot (such as insert explicit decimal points when the data only has implicit decimal points, and add literal values or combine compound sets of columns with literals). And SQLCMD (also from the IIUG) allows you to avoid creating an intermediate file (target file) -- you can run the output of the transformer directly into "sqlreload -d dbase -t table". Hannes Visagie suggested a way to do it with a shell script. It would more or less work (the '| tee dest_file.unl' as written would clobber the output file on every row and should be moved outside the loop, after the 'done'), but would be very slow. > How can this be achieved in perl. TMTOWTDI - there's more than one way to do it. Using unpack is an option. A regex with counted patterns would be another. -- 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!"