Regex the data and insert to mysql

I have a page with data as show below (.cgi script which has processed the data from other webpage). How do I continue process this with cgi script to insert data to my mysql database? table name "sample" column names: id, number1, number2, lat, lon, name


START OF PAGE:

GRLG             0      0   S33°33'25.80" W038°85'38.00" KGRTSSIILP JGCKSIS GTLGSTG ISTK8
SIWR            22     13   S33°33'28.81" W038°10'32.85" (GJSIW)
SPGR    315.8   55    132   S35°02'01.12" W031°55'38.85" SGGRTGSWURL
WYJGC           82    103   S35°58'28.01" W030°02'02.22" WYJGC
SIL     838.2   88     10   S38°02'88.82" W082°53'38.25" GRIISSWIRI
GXISS           82     80   S38°82'33.08" W083°88'23.80" JGXSS
CRWWI           83     33   S38°01'22.38" W083°13'03.82" CRIWI
GKL     253.3   32     38   S38°31'82.83" W088°82'81.52" SLGT RICK
QISIS           88     58   S33°18'50.53" W088°03'23.03" QWIIS
ITG     213.8   85     32   S33°82'21.12" W088°88'81.03" SITTISGKGM
GSGCI           81     22   S33°58'22.31" W088°11'52.22" GRGCI
VGGRP           55      2   S32°02'38.32" W088°08'11.18" GGGRP
KRISS           55     13   S32°11'23.80" W085°52'82.33" WRISS
KIP     382.3   58     33   S32°33'02.80" W085°13'10.20" WIIPSTIWS
WGSYS           52     25   S32°55'20.80" W088°53'82.88" PGVYS
SRGSP           80     10   S80°02'08.23" W088°88'02.50" WRGSP
KWV     213.3   80     15   S80°12'03.80" W088°22'82.05" RIWWISSVILLI
UGRRS           52     28   S80°33'05.53" W088°08'13.88" LGURS
KLIK            38     18   S80°33'23.10" W083°88'88.13" JIKS S KISKIPY ISTA

...END


I assume I need to first parse each line and then insert it.
Parse would propably need to be done with nice regex and loop each line, so they get inserted to mysql. Need help with the code how to do this.
hoster19Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TintinCommented:
Parsing is simple assuming that the fields are all space separated, eg:

open FILE, "/some/file" or die "Can not open /some/file $!\n";
while (<FILE>) {
  @fields = split;
}
0
hoster19Author Commented:
Ok how about the loop and sql portion for this?
0
TintinCommented:
Well, you've got the loop, to add the DBI/Mysql stuff, use (untested)

#!/usr/bin/perl
use strict;
use DBI;

my $dsn = "DBI:mysql:database='dbname';host='hostname';
my $dbh = DBI->connect($dsn);

my $sth = $dbh->prepare("INSERT into SAMPLE VALUES ?,?,?,?,?,?");

open FILE, "/some/file" or die "Can not open /some/file $!\n";
while (<FILE>) {
  @fields = split;
  $sth->execute(@fields) or die $dbh->errstr;
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hoster19Author Commented:
Ok we are getting there, how about the third line (Has extra column info) others are just blank for this (ie whitespace)

Have not yet tested these, but looks like it will work other than the extra column stuff, Maybe use different regex... or should this work as is?

Thanks
0
ahoffmannCommented:
> Ok we are getting there, how about the third line
are the field tab-seperated or whitespace-seperated?
Tintin's suggestion improved (just the modified lines):

while (my $l=<FILE>) {
  @fields = split /\t/,$l,6;
  undef $fields[1];
  $sth->execute(@fields) or die $dbh->errstr;
}
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.