Convert a text file to a spreadsheet

[Tript99]

Hello,
I have a text file that is not delimited, it looks like this:

0001028976 I 07860 BINDER 3-RING EZD LH 1.5BK
0001111257 A 99923 BINDER 3-RING LZD HH HDAG

I need to use perl to convert this into a spreadsheet. I am not sure how to identify the fields without a delimiter. I have been looking into creating an array and splitting it up but I am having a lot of trouble. Any help would be greatly appreciated.
Thanks.

 

[MikeLacey]

hi,

is it fixed length then rather than delimitted?

Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

It's like this; even samurai have teddy bears, and even teddy bears get drunk.

 

[Tript99]

Hi Mike,
Yes it is a fixed length text file.
Tony.

 

[tviman]

If each field is fixed length then you can just count the nuumber of characters per field and assign values to variables using a series of substring statements.

There's always a better way. The fun is trying to find it!

 

[McleanWJM]

well awk might be better than perl to just add delimeters but a simple way for perl would be to split it based on spaces then join it with a delimter back into a string and print to std out or a log file.

 

[PaulTEG]

OPTION 1

open FH, &quot;<file.txt&quot;;
open FH2, &quot;>file.csv&quot;;
while (<FH>) {
  @ary=split (/ /, $_);
  foreach $item (@ary) {
    print FH2 &quot;$item,&quot;;
  }
  print FH2, &quot;\n&quot;;
}
close FH;
close FH2;

OR
OPTION2

open FH, &quot;<file.txt&quot;;
open FH2, &quot;>file.csv&quot;;
while (<FH>) {
   $_ =~ s/ /,/g;
   print FH2 &quot;$_&quot;;
}
close FH;
close FH2;

Option 1 is probaly best if you want to join a few fields together. Instead of printing out each field individually you can build up a string, and print the string
$line="$ary[0],$ary[1] $ary[2],$ary[3],$ary[4] ...

HTH
--Paul

 

[goBoating]

Here's another way to do it which gives an easy conversion route to putting the info in a database if that might be needed in the future.

#!perl
use strict;
use DBI;

# Connect to the CSV file to give SQL access to the data
my $dbh = DBI->connect("DBI:CSV:f_dir=./") or die "Cannot connect: " . $DBI::errstr;
$dbh->{'csv_tables'}->{'new_file'} = {'file' => './new_file.csv'} or die "DBI ERROR ". $DBI::errstr;

#             012345678901234567890123456789012345678901234567890
#                   1         2         3         4         5
my @lines = ('0001028976  I   07860  BINDER 3-RING EZD LH 1.5BK',
          '0001111257  A   99923  BINDER 3-RING LZA HH  HDAG',
          '0001111257  B   99924  BINDER 3-RING LZB HH  HDAH',
          '0001111257  C   99925  BINDER 3-RING LZC HH  HDAI',
          '0001111257  D   99926  BINDER 3-RING LZD HH  HDAJ');

foreach (@lines) {
    my @flds;
    $flds[0] = substr($_, 0, 10);
    $flds[1] = substr($_, 12, 1);
    $flds[2] = substr($_, 16, 5);
    $flds[3] = substr($_, 23, 6);
    $flds[4] = substr($_, 0, 10);
    $flds[5] = substr($_, 0, 10);
    $flds[6] = substr($_, 0, 10);
    $flds[7] = substr($_, 0, 10);

    $dbh->do("INSERT INTO new_file VALUES (?,?,?,?,?,?,?,?,?)", undef, @flds);
    }

Before you run this, you must create a text file named new_file.csv and put the header in it.
Here's a silly header line.

fld1, fld2, fld3, fld4, fld5, fld6, fld7, fld8

You'd probably want your field names to be something other than 'fldx'.

'hope this helps

If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.

 

[Tript99]

Thank you very much for all of your suggestions! I wound up breaking up the records into substrings like GoBoating just suggested.

Thanks Again!