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_conten ts ("http://server/data.php");

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

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

Thanks for any tip.

[Jerry Stuckle]

Re: Best way to parse CSV data?

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_conten ts ("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",$r ow);
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.
jstucklex@attgl obal.net
=============== ===

[Gilles Ganault]

Re: Best way to parse CSV data?

On Wed, 14 May 2008 17:29:17 -0400, Jerry Stuckle
<jstucklex@attg lobal.netwrote:

> foreach ($contents as $row) {
> $line = explode("\t",$r ow);
> 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]

Re: Best way to parse CSV data?

Gilles Ganault wrote:

On Wed, 14 May 2008 17:29:17 -0400, Jerry Stuckle
<jstucklex@attg lobal.netwrote:

>> foreach ($contents as $row) {
>> $line = explode("\t",$r ow);
>> 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.
jstucklex@attgl obal.net
=============== ===

[Michael Austin]

Re: Best way to parse CSV data?

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_conten ts ("http://server/data.php");
>
//item1<TAB>item2 <CRLF>
$contents = explode("\r\n", $input);
>
foreach ($contents as $row) {
$line = explode("\t",$r ow);
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]

Re: Best way to parse CSV data?

On Wed, 14 May 2008 22:46:58 +0200, Gilles Ganault <nospam@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_conten ts ("http://server/data.php");
>
//item1<TAB>item2 <CRLF>
$contents = explode("\r\n", $input);
>
foreach ($contents as $row) {
$line = explode("\t",$r ow);
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($resul t)
?>

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]

Re: Best way to parse CSV data?

On Wed, 14 May 2008 20:14:09 -0400, Jerry Stuckle
<jstucklex@attg lobal.netwrote:

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

Too bad. Thanks.

[Gilles Ganault]

Re: Best way to parse CSV data?

On Wed, 14 May 2008 22:35:11 -0500, Michael Austin
<maustin@firstd basource.comwro te:

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

Re: Best way to parse CSV data?

On Thu, 15 May 2008 07:46:10 +0200, "Rik Wasmus"
<luiheidsgoeroe @hotmail.comwro te:
(snip)

Thanks!

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

Re: Best way to parse CSV data?

On May 15, 1:21 pm, Gilles Ganault <nos...@nospam. comwrote:

On Wed, 14 May 2008 22:35:11 -0500, Michael Austin
>
<maus...@firstd basource.comwro te:

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.