Parse text file for inserting into SQL table 2

[barbola]

I have some Perl stuff that was started up by an expert who is unavaible at this time and left me to finish it. I can't get it to work and I don't know Perl very much other than sort of being able to follow what it is supposed to be doing.

I have text files that need to be put into fields in a SQL table. The sql part I understand.

Each record in the text file has a "header" and the line begins with '100','field1','field2', etc. The next several lines are the detail '200','field2','field2', etc and I only want the header info to go into SQL.

I'm not sure what parts of my code I need to include as an example, but if anyone knows the basic code that would do this I'm open to suggestions.

 

[raklet]

What you need to do is paste some sample data from your text file - exactly like you see it in the file. Paste any relevant code that you think might be related to parsing the file and sticking the result in the DB. Explain what should be parsed from the file, how it should be formatted, etc.

 

[barbola]

I think mainly I just need help with my syntax and all the /\/\ thingies. I'm a fast learner and examples help me alot more than reading a book (slight comprehension problem I guess!)

Here is some code - I know it's kind of incomplete:

$filename=@ARGV[0];
$state = "START";

while(<>)
{
# $record=0;
if(/(.+),(.*)/){
$fieldno=$1;
$data=$2;

# convert to an sql date if it is a date field
if($fieldno == 5 || $fieldno == 7)
{
$data=string2date($data);
}

($data = $data) =~ s/^\s+//;
($data = $data) =~ s/\s+$//;
($data = $data) =~ s/\'/\'\'/g;
$data = &quot;'&quot; . $data . &quot;'&quot;;

# change state depending on field number
if($fieldno = 100){
$nextstate=&quot;RUN&quot;;
}

if( $state ne $nextstate){
($record = $record) =~ s/^.//;
print &quot;changing state: $record&quot;;
if($state eq &quot;START&quot{
}
elsif($state eq &quot;RUN&quot{
dosql(&quot;insert into RRmix (mixnum,ordnumbr,ordseqnu,mixdate,nSerial,mixtime) values ($record)&quot
}
)
$record=&quot;&quot;;
$state=$nextstate;
}
# print &quot;$fieldno | $2&quot;;
# print;
$record=&quot;$record,$data&quot;;
print $record . &quot;\r\n&quot;;
# print;
}

 

[barbola]

Oops here is some sample data as well:

100,0000000008,0000017538,2000,20030929,00005,083721
200,100,1201
200,120,639
200,214,30
200,201,23
200,200,22
200,202,10.06
200,151,5.04
200,205,2.96
100,0000000009,0000017538,3000,20030929,00006,084239
200,100,1199
200,120,639
200,214,29
200,201,23
200,200,22
200,202,10.06
100,0000000010,0000017538,4000,20030929,00007,085452
200,100,2401
200,120,1277
200,214,58
200,201,48
200,200,44
200,202,20.02

Please help if you can...you will find I am most appreciative. thank-you thank-you thank-you!!!

Thanks!
Barb E.

 

[raklet]

What do you want to do with the data. Which lines should be extracted and put in the DB? I think you mean the lines that start with 100. Right? If so, do all the lines you want in the DB start with 100? What do the various fields in the 100 line stand for?

 

[barbola]

That is right. All the lines I want start with 100. I do not want the lines starting with 200.

There is a function that was set up for us that will run a sql statement as follows:

dosql(&quot;insert into RRmix (mixnum,ordnumbr,ordseqnu,mixdate,nSerial,mixtime)

These are the field names for the data in the '100' rows.

thanks


Thanks!
Barb E.

 

[raklet]

Okay, I see what the script is trying to do, and in my opinion, it is made to be much more difficult than really necessary. Here is my modifcation:

open (IN, &quot;c:/temp/test.txt&quot || die $!;
while (<IN>) {
if (/^100/) {
($mixnum,$ordnumbr,$ordseqnu,$mixdate,$nSerial,$mixtime) = split(/,/);
if ($mixdate) {
$mixdate = string2date($mixdate);
}
if ($mixtime) {
$mixtime = string2date($mixtime);
}
print &quot;$mixnum $ordnumbr $ordseqnu $mixdate $nSerial $mixtime\n&quot;;
#place your sql insert statements here and you are done
}
}

I stripped out all the crap about &quot;nextstate&quot;. The simplest way to do this is just see if a line starts with 100. If it does, split the line at the commas and assign each field to its respective variable. Run string2date and whatever other processing you want to clean the text up and then stick it in the database. Go to the next line in the file. If the line doesn't start with 100, everything will be skipped and you will go to the next line in the file.

Hope that helps,

Raklet

 

[barbola]

Thank-you very very much for your help. It works great.

I don't have the right formatting for date and time but I think I can figure it out.

The state stuff was from another one where we needed the other data - I don't think I would have ever figured this out.

Thanks again!!!

barb.

 

[raklet]

You are welcome.