Does anyone have an example of code to process a CSV file submitted
via a file upload and parse it out in order to write its records to a
db?

Hopefully yours,

- Brian

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

> -----Original Message-----
> From: Brian Dunning [mailto:brian [at] briandunning.com]
> Sent: Monday, October 17, 2005 10:37 AM
> To: php-general [at] lists.php.net
> Subject: [PHP] Uploaded CSV -> database
>
>
> Does anyone have an example of code to process a CSV file submitted
> via a file upload and parse it out in order to write its
> records to a
> db?
>


With MYSQL (assuming permissions and such are in order) You would simply:

$sql="load data local infile '/path/to/csv' into table tablename fields
terminated by ','";


JM

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

> -----Original Message-----
> From: Jim Moseby [mailto:JMoseby [at] nrbindustries.com]
> Sent: Monday, October 17, 2005 10:41 AM
> To: 'Brian Dunning'; php-general [at] lists.php.net
> Subject: RE: [PHP] Uploaded CSV -> database
>
>
> > -----Original Message-----
> > From: Brian Dunning [mailto:brian [at] briandunning.com]
> > Sent: Monday, October 17, 2005 10:37 AM
> > To: php-general [at] lists.php.net
> > Subject: [PHP] Uploaded CSV -> database
> >
> >
> > Does anyone have an example of code to process a CSV file
> submitted
> > via a file upload and parse it out in order to write its
> > records to a
> > db?
> >
>
>
> With MYSQL (assuming permissions and such are in order) You
> would simply:
>
> $sql="load data local infile '/path/to/csv' into table
> tablename fields
> terminated by ','";
>

I should amend this to say that the columns in your CSV file, and in the
table must match for this to work.

If you wanted to parse through it line by line and do it all manually, check
out the fread() and explode() functions in the PHP manual.

JM

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

> > -----Original Message-----
> > From: Jim Moseby [mailto:JMoseby [at] nrbindustries.com]
> > Sent: Monday, October 17, 2005 10:41 AM
> > To: 'Brian Dunning'; php-general [at] lists.php.net
> > Subject: RE: [PHP] Uploaded CSV -> database
> >
> >
> > > -----Original Message-----
> > > From: Brian Dunning [mailto:brian [at] briandunning.com]
> > > Sent: Monday, October 17, 2005 10:37 AM
> > > To: php-general [at] lists.php.net
> > > Subject: [PHP] Uploaded CSV -> database
> > >
> > >
> > > Does anyone have an example of code to process a CSV file
> > submitted
> > > via a file upload and parse it out in order to write its
> > > records to a
> > > db?
> > >
> >
> >
> > With MYSQL (assuming permissions and such are in order) You
> > would simply:
> >
> > $sql="load data local infile '/path/to/csv' into table
> > tablename fields
> > terminated by ','";
> >
>
> I should amend this to say that the columns in your CSV file, and in the
> table must match for this to work.
>
> If you wanted to parse through it line by line and do it all manually,
check
> out the fread() and explode() functions in the PHP manual.

and don't forget fgetcsv()

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

> -----Original Message-----
> From: Mark Rees [mailto:mrees [at] itsagoodprice.com]
> Sent: Monday, October 17, 2005 11:11 AM
> To: php-general [at] lists.php.net
> Subject: Re: [PHP] Uploaded CSV -> database
>
>
> > > -----Original Message-----
> > > From: Jim Moseby [mailto:JMoseby [at] nrbindustries.com]
> > > Sent: Monday, October 17, 2005 10:41 AM
> > > To: 'Brian Dunning'; php-general [at] lists.php.net
> > > Subject: RE: [PHP] Uploaded CSV -> database
> > >
> > >
> > > > -----Original Message-----
> > > > From: Brian Dunning [mailto:brian [at] briandunning.com]
> > > > Sent: Monday, October 17, 2005 10:37 AM
> > > > To: php-general [at] lists.php.net
> > > > Subject: [PHP] Uploaded CSV -> database
> > > >
> > > >
> > > > Does anyone have an example of code to process a CSV file
> > > submitted
> > > > via a file upload and parse it out in order to write its
> > > > records to a
> > > > db?
> > > >
> > >
> > >
> > > With MYSQL (assuming permissions and such are in order) You
> > > would simply:
> > >
> > > $sql="load data local infile '/path/to/csv' into table
> > > tablename fields
> > > terminated by ','";
> > >
> >
> > I should amend this to say that the columns in your CSV
> file, and in the
> > table must match for this to work.
> >
> > If you wanted to parse through it line by line and do it
> all manually,
> check
> > out the fread() and explode() functions in the PHP manual.
>
> and don't forget fgetcsv()

Thanks! Good one. I didn't forget it, I just didn't know about it. :-)

JM

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

It looks like all of those tips will easily cover me for the latter
half of the operation. Any tips on how to get the uploaded CSV file
into memory in order to attack it with fgetcsv()? I'd rather not ever
have to actually write the file to the server's disk.

Thanks!

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[snip]
It looks like all of those tips will easily cover me for the latter
half of the operation. Any tips on how to get the uploaded CSV file
into memory in order to attack it with fgetcsv()? I'd rather not ever
have to actually write the file to the server's disk.
[/snip]

Once you have performed move_uploaded_file() you will have to fopen and loop
through the file to read and perform error checking. After you have
processed the file you could then unlink() it, removing it from the server's
disk. Here is an example where Excel files are uploaded as tab delimited
text;

/*real file name*/
$fileName = ($HTTP_POST_FILES['docfile']['name']);
$docWork = "/path/to/file/after/move/";

move_uploaded_file($HTTP_POST_FILES['docfile']['tmp_name'], $docWork .
$fileName) or die("File cannot be uploaded");

/*
** open uploaded file and do stuff to it
*/
$i = 0;
$fileTab = fopen($docWork.$fileName, "r");
while(!feof($fileTab)){
$fileLine = fgets($fileTab, 1024);

$arrLine = explode("\t", $fileLine);
$cmLength = strlen($arrLine[0]);

/* do other stuff here */
}
fclose($fileTab);

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

> -----Original Message-----
> From: Brian Dunning [mailto:brian [at] briandunning.com]
> Sent: Monday, October 17, 2005 11:39 AM
> To: php-general [at] lists.php.net
> Subject: Re: [PHP] Uploaded CSV -> database
>
>
> It looks like all of those tips will easily cover me for the latter
> half of the operation. Any tips on how to get the uploaded CSV file
> into memory in order to attack it with fgetcsv()? I'd rather
> not ever
> have to actually write the file to the server's disk.
>
> Thanks!
>

If you are using the "standard" file upload facilities, your file is being
written to disk when it is being uploaded. As far as I can tell, fgetcsv()
will only read a file from disk:

<?php // from the manual
$row = 1;
$handle = fopen ("test.csv","r");
while ($data = fgetcsv ($handle, 1000, ",")) {
$num = count ($data);
print "<p> $num fields in line $row: <br>\n";
$row++;
for ($c=0; $c < $num; $c++) {
print $data[$c] . "<br>\n";
}
}
fclose ($handle);
?>

If you are instead using a socket connection to receive the file in a stream
from the client, you could assign it to a string variable, and use
explode().

These are fairly uncharted territories for me, so others will likely have
better answers.

JM

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Actually I think fgetcsv will work with any valid file pointer and at
least in PHP 5, the streams implementation will allow you to use a variety
of protocols to create the stream.

http://us2.php.net/manual/en/wrappers.php

I understand that it isn't even too teribbly difficult to implement your
own stream if one isn't already to your liking, but I'm afraid I haven't
found need to go beyond the simple read-a-file-from disk style operation.

Ben

On Mon, 17 Oct 2005 11:45:04 -0400, Jim Moseby <JMoseby [at] nrbindustries.com>
wrote:

>> -----Original Message-----
>> From: Brian Dunning [mailto:brian [at] briandunning.com]
>> Sent: Monday, October 17, 2005 11:39 AM
>> To: php-general [at] lists.php.net
>> Subject: Re: [PHP] Uploaded CSV -> database
>>
>>
>> It looks like all of those tips will easily cover me for the latter
>> half of the operation. Any tips on how to get the uploaded CSV file
>> into memory in order to attack it with fgetcsv()? I'd rather
>> not ever
>> have to actually write the file to the server's disk.
>>
>> Thanks!
>>
>
> If you are using the "standard" file upload facilities, your file is
> being
> written to disk when it is being uploaded. As far as I can tell,
> fgetcsv()
> will only read a file from disk:
>
> <?php // from the manual
> $row = 1;
> $handle = fopen ("test.csv","r");
> while ($data = fgetcsv ($handle, 1000, ",")) {
> $num = count ($data);
> print "<p> $num fields in line $row: <br>\n";
> $row++;
> for ($c=0; $c < $num; $c++) {
> print $data[$c] . "<br>\n";
> }
> }
> fclose ($handle);
> ?>
>
> If you are instead using a socket connection to receive the file in a
> stream
> from the client, you could assign it to a string variable, and use
> explode().
>
> These are fairly uncharted territories for me, so others will likely have
> better answers.
>
> JM



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

This discussion is starting to go over my head, but fgetscv works with
uploaded files (in PHP5 anyway). Here's some sample code:

if(isset($_FILES['userfile']['tmp_name'])){
$csvfile=fopen($_FILES['userfile']['tmp_name'],"rb");
if($csvfile==FALSE){die('error opening file');};
while(($aryData=fgetcsv($csvfile))!==FALSE){
//etc
}

> Actually I think fgetcsv will work with any valid file pointer and at
> least in PHP 5, the streams implementation will allow you to use a variety
> of protocols to create the stream.
>
> http://us2.php.net/manual/en/wrappers.php
>
> I understand that it isn't even too teribbly difficult to implement your
> own stream if one isn't already to your liking, but I'm afraid I haven't
> found need to go beyond the simple read-a-file-from disk style operation.
>
> Ben
>
> On Mon, 17 Oct 2005 11:45:04 -0400, Jim Moseby <JMoseby [at] nrbindustries.com>
> wrote:
>
> >> -----Original Message-----
> >> From: Brian Dunning [mailto:brian [at] briandunning.com]
> >> Sent: Monday, October 17, 2005 11:39 AM
> >> To: php-general [at] lists.php.net
> >> Subject: Re: [PHP] Uploaded CSV -> database
> >>
> >>
> >> It looks like all of those tips will easily cover me for the latter
> >> half of the operation. Any tips on how to get the uploaded CSV file
> >> into memory in order to attack it with fgetcsv()? I'd rather
> >> not ever
> >> have to actually write the file to the server's disk.
> >>
> >> Thanks!
> >>
> >
> > If you are using the "standard" file upload facilities, your file is
> > being
> > written to disk when it is being uploaded. As far as I can tell,
> > fgetcsv()
> > will only read a file from disk:
> >
> > <?php // from the manual
> > $row = 1;
> > $handle = fopen ("test.csv","r");
> > while ($data = fgetcsv ($handle, 1000, ",")) {
> > $num = count ($data);
> > print "<p> $num fields in line $row: <br>\n";
> > $row++;
> > for ($c=0; $c < $num; $c++) {
> > print $data[$c] . "<br>\n";
> > }
> > }
> > fclose ($handle);
> > ?>
> >
> > If you are instead using a socket connection to receive the file in a
> > stream
> > from the client, you could assign it to a string variable, and use
> > explode().
> >
> > These are fairly uncharted territories for me, so others will likely
have
> > better answers.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Amazing - it works! The uploaded file is always stored in the temp
directory, so that issue was kind of moot. Here's my complete code,
if anyone else has this same need:

if(isset($_FILES['userfile']['tmp_name'])) {
$csvfile = fopen($_FILES['userfile']['tmp_name'], "r");
if($csvfile == FALSE) die('error opening file');
while(($datarow = fgetcsv($csvfile, 1000)) !== FALSE) {
$name = isset($datarow[0]) ? $datarow[0] : '';
$addr = isset($datarow[1]) ? $datarow[1] : '';
$city = isset($datarow[4]) ? $datarow[4] : '';
$state = isset($datarow[5]) ? $datarow[5] : '';
$zip = isset($datarow[6]) ? $datarow[6] : '';
$query = "insert into dealers
(account_id,name,addr,city,state) values
('$account_id','$name','$addr','$city','$state','$zip');";
$result = mysql_query($query) or die(mysql_error());
}
fclose($csvfile);
}

I was really surprised at how short & simple this turned out to be.
There is plenty of room for error-checking in the above snippet, but
what's there works when there are no problems.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php