Regex the data and insert to mysql

Posted on 2004-10-23
Last Modified: 2013-12-25
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


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


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.
Question by:hoster19
    LVL 48

    Expert Comment

    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;

    Author Comment

    Ok how about the loop and sql portion for this?
    LVL 48

    Accepted Solution

    Well, you've got the loop, to add the DBI/Mysql stuff, use (untested)

    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;

    Author Comment

    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?

    LVL 51

    Assisted Solution

    > 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;

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Course: Foundations of Front-End Development

    Jump-start a lucrative career in front-end web development, with zero previous coding experience required. This course covers the basic programming concepts and languages required for creating engaging websites from scratch.

    If you get a (Blue Screen of Death), your system writes a small file called a minidump. Your first step is to make certain your computer is setup to record memory dumps. Right click My Computer, choose properties. Click on the advanced tab, an…
    Making a simple AJAX shopping cart Couple years ago I made my first shopping cart, I used iframe and JavaScript, it was very good at that time, there were no sessions or AJAX, I used cookies on clients machine. Today we have more advanced techno…
    Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now