TEXT to CSV using Perl

Shell Programming and Scripting
1

Hi Folks

Need some help with this and my Perl isnt the hottest I also have text::csv installed on my perl install
The large text with a few million entries is in a format below

example text file

Fig Leafs
Cake No: 0000001
Author: King s.
Record No: 995-34343-232-232
Comments:SSDFGDFGDFFGDFGDFGDFGDFGDFGDFGFGDFGDFGDFGFDFDASSFDSFSDFSDFDSFSD
SDFSDFSDFSDFSDFDFDFDFDDFDSFDFDFDFDFSD SDFGSDFFGE RFGSDFGFDG DFGDFGFDG
Food Source: MY mUMS house
Ingredients: dsfsdfsd dsfsdfsdf sdfsdfsdf dfsdf dsfsdf sdfsdfdsf sdfsdfsd sdf sd
sdfsdsdfsd sdfsdfsdf sdsfsdfsdf sdfsdfdf sdfsdfd sdf dsdfsd fr.
Another No: 999999
Location: The kitchen

Insane batman

Cake No: 0000002
"Author: nannao y.y.,carloff t.t.,mello.i."
Record No: 123-83412-111-666
Comment: sdfjhfgfgkmfbf djkndfdjfnd dfjkndjf dfjndfdfdf dfjdfn DFGDFGDFGDFGFGDFGDFGDFGFDFDASSFDSFSDFSDFDSFSD
SDFSDFSDFSDFSDFDFDFDFDDFDSFDFDFDFDFSD SDFGSDFFGE RFGSDFGFDG DFGDFGFDG sdsdfsffsd sdsdsdsd xxxxx
Food Source: MY Dads house over there
Ingredients: dsfsdfsd dsfsdfsdf sdfsdfsdf dfsdf dsfsdf sdfsdfdsf sdfsdfsd sdf sd
sdfsdsdfsd sdfsdfsdf (sdsfsdfsdf) sdfsdfdf sdfsdfd sdf dsdfsd fr.
Another No: 002234
Location: The Hallway

From the file above i want the output like
Cake No,Author,Record No,Comments,Food Source,Ingredients,Another No,Location

Notice the formatting isnt that good theres a " infront of author on the second record and also at the end
The words like Fig leafs and Insane batman i want ignored and not picked up.
Other thing im worried about is the processing on this the text file is like 2mb around a million entries
Thanks in advance for any help on this! I will repay you !
Cheers

---------- Post updated at 12:02 PM ---------- Previous update was at 10:15 AM ----------

Could this be acheived with a Kshell Script?!!

2

CSV has just a few rules:

  1. Separate columns with commas.
  2. Separate rows with ^M^J (carriage-return + line-feed)
  3. For any " in a column, expand to two: ""
  4. For any column containing a comma, carriage-return or line-feed, enclose in ". (Other columns can be " enclosed if desired.)

So, CSV columns can contain any character! Of course, if you are going to have utf-8 multibyte characters, you need a tool that knows and respects that!

You need to parse your file, store each column value after detecting and compensating for embedded metacharacters: ',', '"', carriage-return and line feed, detect row boundaries at new cake number or end of file, and spit out your columns in the desired order with commas between and the row separator after.

You can write it in any language, but I would prefer C or PERL. I am not sure which of the PERL functions encodes data into CSV, as opposed to decodimg from. Like many tools, you can play with it and see what results. Some options for data typing, white space interpretation and binary are only relevant to some receiving systems.

3

Hi.

Here is a sample of the basic operation for combining with Text::CSV:

#!/usr/bin/env perl

# @(#) p1	Demonstrate creation of csv with Text/CSV.
# See perldoc Text/CSV

use warnings;
use strict;

use Text::CSV;

my ( $csv, @columns, $status, $line );

$csv = Text::CSV->new();    # create a new object

@columns = qw/ Now is the time /;
$status  = $csv->combine(@columns);    # combine columns into a string
$line    = $csv->string();
print " csv line is: ", $line, "\n";

@columns = qw/ Now "is the" time /;
$status  = $csv->combine(@columns);    # combine columns into a string
$line    = $csv->string();
print " csv line is: ", $line, "\n";

exit(0);

producing:

% ./p1
 csv line is: Now,is,the,time
 csv line is: Now,"""is","the""",time

Best wishes ... cheers, drl

4 
awk '
BEGIN {
  read_comment=0;
  fl=0;
  t="Cake No,Author,Record No,Comments,Food Source,Ingredients,Another No,Location";
  c=split(t,b,",");
}
{sub("^[ \"]*","");
 sub("[ \"]*$","");
 sub("Comment* *: *","Comments:");
}
$0 !~ /:/ && w ~ /./ {
  a[w]=a[w] $0;
}
/:/ {
  w=$0;
  sub(" *:.*", "", w);
  v=$0;
  sub(".*: *", "", v);
  a[w]=v;
}
/^Location:/ {
  sub("Comments *: *","",a["Comments"]);
  sub("^ *","",a["Comments"]);
  if (fl++<1) {
    printf "\"" b[1] "\"";
    for (i=2; i<=c; i++) {
      printf ","  "\"" b "\"";
    }
    print "";
  }
  printf "\"" a[b[1]] "\"";
  a[b[1]]="";
  for (i=2; i<=c; i++) {
    printf "," "\"" a [b]"\"";
    a[b]="";
  }
  print "";
  w="";
}' infile > outfile.csv
5

Guys thanks for all your help so far, its been good learning this and ill make sure to share the solution
RDRTX1 Thank you for your time and the script
Do i run it just like

cat cairstest.txt |
awk '
BEGIN {
read_comment=0;
fl=0; blah blah

I got a syntax error at line 8 !

6

Try replacing the lines:

sub("^[ \"]*","");
 sub("[ \"]*$","");

with:

sub("^[ '\"']*","");
 sub("[ '\"']*$","");