Importing text document to database

PHP
1

Hi all,

I’m having some trouble with getting my data to play nice with MySQL.

Here is a line from my text doc:

“VISTORTA”,“MERLOT\”,“WINE-IMPORTED”,“”,13.9900

Here’s my php that reads the text and prepares the sql statement.

$handle = fopen($dir, "r");
			$sql = "INSERT INTO inventory (name, description, type, size, price) VALUES ";
			while(($data = fgetcsv($handle)) !== FALSE)
      		{
				 list($name, $description, $type, $size, $price) = $data;
				 $name = addslashes(ucwords(strtolower($name)));
				 $description = addslashes(ucwords(strtolower($description)));
				 $sql .= "('$name', '$description', '$type', '$size', $price), ";
			}
			$sql = substr_replace($sql, '', -2, 2);
			fclose($handle);
			$insertInv = $dbh->prepare($sql);
			$invQuery = $insertInv->execute();

It throws an error, so when I echo out the sql statement, the original line in the text is changed to this in the sql.

('Vistorta', 'Merlot\\",wine-imported\\"', '', '13.9900', )

Instead of :


('Vistorta', 'Merlot\\', 'WINE-IMPORTED', '', 13.9900)

That slash is doing it and I’m having trouble figuring out how to allow it. I’ve tried adding parameters to the fgetcsv function

$data = fgetcsv($handle, '', ',','','')

To try to allow the backslash instead of using it as an escape character, but that seems to freeze the process.

Could I get some assistance please? Thanks!

2

Try setting a proper integer value for the length arg to fgetcsv.

3

Ok, I have

$data = fgetcsv($handle, 1000, ',','','')

It just refreshes to a blank white page after a minute or so.

4

Sounds like php is hitting max_execution_time and you have disabled display_errors.

How big is the csv file?

5

I don’t think errors are off. I have this at the top of my script

ini_set('display_errors',1);
error_reporting(E_ALL|E_STRICT);

The file size is 352kb

6

ok, guess they were disabled.

it’s saying it expects only 4 params for fgetcsv. I must not have 5.3 on my MAMP station.

7

Ok, I need a 5.2.6 solution as my hosting provider does not run 5.3.

Could i get some pointers please?

Thanks.

8

Probably the simplest would be to just str_replace the backslashes into something else and write the data back to a file. Like


$s = str_replace('\\\\', '__BACKSLASH__', file_get_contents('foo.csv'));

Write $s to a new file, then read it back like you’re doing now. You need to translate back though


list($name, $description, $type, $size, $price) = str_replace('__BACKSLASH__', '\\\\', $data);

Another option could be to seek a more capable csv parser that lets you specify the escape char. Maybe PEAR or phpclasses has something.

9

Thanks for your help crmalibu!