Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this tutorial I will show you how to make a simple HTML bar chart with the usage of WhizBase, If you want more information about WhizBase please read my previous articles at http://www.experts-exchange.com/ARTH_5123186.html (http://www.experts-ex…
This article will show, step by step, how to integrate R code into a R Sweave document
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

618 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