[Webinar] Streamline your web hosting managementRegister Today


Regex the data and insert to mysql

Posted on 2004-10-23
Medium Priority
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
  • 2
  • 2
LVL 48

Expert Comment

ID: 12392030
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

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

Accepted Solution

Tintin earned 1400 total points
ID: 12397914
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

ID: 12400707
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

ahoffmann earned 600 total points
ID: 12456377
> 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;

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is a general practice to get rid of old user profiles on a computer  in a LAN environment. As I have been working with a company in a LAN environment where users move from one place to some other place at times. This will make many user profil…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

590 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