Best way to parse CSV data?

[Gilles Ganault]

Hello

I googled for samples, but I'd like to make sure there's no simpler
way to parse tab-separated data read from a web page, before I can
save them into a database:

<?php
$input = file_get_contents ("http://server/data.php");

//item1<TAB>item2<CRLF>
$contents = explode("\r\n", $input);

foreach ($contents as $row) {
$line = explode("\t",$row);
foreach($line as $col) {
//Here, will save each col into database
print $col;
}
}
?>

Thanks for any tip.

[Jerry Stuckle]

Gilles Ganault wrote:

Hello

I googled for samples, but I'd like to make sure there's no simpler
way to parse tab-separated data read from a web page, before I can
save them into a database:

<?php
$input = file_get_contents ("http://server/data.php");

//item1<TAB>item2<CRLF>
$contents = explode("\r\n", $input);

You can use
$contents = file("http://server/data.php");

to get the data into an array

foreach ($contents as $row) {
$line = explode("\t",$row);
foreach($line as $col) {
//Here, will save each col into database
print $col;
}
}
?>

Thanks for any tip.

Works as well as anything else.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Gilles Ganault]

On Wed, 14 May 2008 17:29:17 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:

> foreach ($contents as $row) {
$line = explode("\t",$row);
foreach($line as $col) {
//Here, will save each col into database
print $col;
}
}
?>

Works as well as anything else.

I ended up simplifying it a bit:

$input = file("http://server/data.php");
foreach ($input as $row) {
//Final CRLF is INDISPENSABLE!
preg_match("|(.+?)\t(.+?)\r\n|",$row,$matches);

//Any way to display the output of prepare() just to check?
$sql = "INSERT INTO mytable (id, name) VALUES (?,?)";
$insert = $dbh->prepare($sql);
$insert->execute(array($matches[1],$matches[2]));
}

I did have a problem with regexing data from file(): Without "\r\n",
the regex doesn't work as planned.

Also, I went through the PDO documentation, but didn't find a method
to display the output of prepare(), which would be convenient for
debugging purposes.

Thanks.

[Jerry Stuckle]

Gilles Ganault wrote:

On Wed, 14 May 2008 17:29:17 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:

>> foreach ($contents as $row) {
$line = explode("\t",$row);
foreach($line as $col) {
//Here, will save each col into database
print $col;
}
}
?>

Works as well as anything else.

I ended up simplifying it a bit:

$input = file("http://server/data.php");
foreach ($input as $row) {
//Final CRLF is INDISPENSABLE!
preg_match("|(.+?)\t(.+?)\r\n|",$row,$matches);

//Any way to display the output of prepare() just to check?
$sql = "INSERT INTO mytable (id, name) VALUES (?,?)";
$insert = $dbh->prepare($sql);
$insert->execute(array($matches[1],$matches[2]));
}

I did have a problem with regexing data from file(): Without "\r\n",
the regex doesn't work as planned.

Also, I went through the PDO documentation, but didn't find a method
to display the output of prepare(), which would be convenient for
debugging purposes.

Thanks.

Yep, if you're going to use a regex, you need the \r\n. But personally
I prefer explode().

And sorry, there's no way to display the output of prepare(). It's out
in the database.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Michael Austin]

Gilles Ganault wrote:

Hello

I googled for samples, but I'd like to make sure there's no simpler
way to parse tab-separated data read from a web page, before I can
save them into a database:

<?php
$input = file_get_contents ("http://server/data.php");

//item1<TAB>item2<CRLF>
$contents = explode("\r\n", $input);

foreach ($contents as $row) {
$line = explode("\t",$row);
foreach($line as $col) {
//Here, will save each col into database
print $col;
}
}
?>

Thanks for any tip.

Since your goal is to save it to a db (mysql? oracle?) why not use the
tools provided with the db engine and save yourself a ton of headaches
trying to reinvent the wheel...

mysql - search for mysql load file
oracle - search for sql*loader
Access - add it as an external table.

[Rik Wasmus]

On Wed, 14 May 2008 22:46:58 +0200, Gilles Ganault <no****@nospam.com
wrote:

Hello

I googled for samples, but I'd like to make sure there's no simpler
way to parse tab-separated data read from a web page, before I can
save them into a database:

<?php
$input = file_get_contents ("http://server/data.php");

//item1<TAB>item2<CRLF>
$contents = explode("\r\n", $input);

foreach ($contents as $row) {
$line = explode("\t",$row);
foreach($line as $col) {
//Here, will save each col into database
print $col;
}
}
?>

Thanks for any tip.

<?php
$input = fopen("http://server/data.php",'r');
$result = array();
while($roo = fgetcsv($result,0,"\t")) $result[] = $row;
var_dump($result)
?>

Or, for a database like MySQL, look into LOAD DATA INFILE syntax.
--
Rik Wasmus
[SPAM] Now temporarily looking for some smaller PHP/MySQL projects/work to
fund a self developed bigger project, mail me at rik at rwasmus.nl. [/SPAM]

[Gilles Ganault]

On Wed, 14 May 2008 20:14:09 -0400, Jerry Stuckle
<js*******@attglobal.netwrote:

>And sorry, there's no way to display the output of prepare(). It's out
in the database.

Too bad. Thanks.

[Gilles Ganault]

On Wed, 14 May 2008 22:35:11 -0500, Michael Austin
<ma*****@firstdbasource.comwrote:

>Since your goal is to save it to a db (mysql? oracle?) why not use the
tools provided with the db engine and save yourself a ton of headaches
trying to reinvent the wheel...

Forgot to mention the source data is off on a remote server to which I
have no access, hence my need to read data from its web page output,
parse it, before putting data into a second database. Thanks.

[Gilles Ganault]

On Thu, 15 May 2008 07:46:10 +0200, "Rik Wasmus"
<lu************@hotmail.comwrote:
(snip)

Thanks!

[C. (http://symcbean.blogspot.com/)]

On May 15, 1:21 pm, Gilles Ganault <[e-mail]:

On Wed, 14 May 2008 22:35:11 -0500, Michael Austin

<[e-mail]:

Since your goal is to save it to a db (mysql? oracle?) why not use the
tools provided with the db engine and save yourself a ton of headaches
trying to reinvent the wheel...

Forgot to mention the source data is off on a remote server to which I
have no access, hence my need to read data from its web page output,
parse it, before putting data into a second database. Thanks.

There is no documented standard for 'CSV' (even though it has a
registered mime type) - so each solution needs to be tailored to the
source.

I'm no regex guru but your approach does not seem to allow for escaped
or embedded newlines within field data.

C.