[Resolved] csv import not working - how can I test code?

ejwf

I have created a file that (hopefully) will import data from a csv file into my database but it isn't working.

require_once ('../mysql_connect.php');		//connect to the database

$filename = "Client.csv"; 
$fp = fopen($filename,"r"); 

while (!feof($fp)){ 

$data = fgetcsv($fp,5000); 

$ClientId = $data[0]; 
$ConcesRef = $data[1]; 
$CoName = $data[2]; 
$Addr1 = $data[3]; 
$Addr2 = $data[4]; 
$Addr3 = $data[5]; 
$Town = $data[6]; 
$County = $data[7]; 
$Postcode = $data[8]; 
$Phone = $data[9]; 
$Fax = $data[10]; 

$sql = "INSERT INTO tblClient (ClientId, ConcesRef, CoName, Addr1, Addr2, Addr3, Town, County, Postcode, Phone, Fax) 
VALUES ('$ClientId', '$ConcesRef', '$CoName', '$Addr1', '$Addr2', '$Addr3', '$Town', '$County', '$Postcode', '$Phone', '$Fax')";
$result = @mysql_query($sql) 
   or die(mysql_error());
   }

When I first ran the code it seemed to have worked but on checking the database it had added over 18000 new records (the client.csv file has only got 25 records for testing purposes) and they were all empty apart from the first one (so it added one new record correctly but that was it).

Firstly can anyone see anything obviously wrong with the code above?

Secondly I would like to test the whole thing by echoing out $data to see whether this it can be read / recognised but I can't get it to work - any ideas how I can test this first.

thanks in advance

dopey

Hi,

You can use var_dump() to view the content of $data e.g.

while (!feof($fp)){  

$data = fgetcsv($fp,5000);  

var_dump($data);
.
.

You may also want to leave the '@' off the front of mysql_query() so it will echo out any error messages.

ejwf

thanks
I have tried this and it has echo'd out the information from $data. But it has only picked up the first record, not the full list. Is this normal?
What can I do to make it recognise all of the records?

dopey

You could try

while ($data = fgetcsv($fp,5000)) {  

$ClientId = $data[0];  

.
.
.

It might also be worth posting a sample of the csv data to see if there's any problems there...

ejwf

tried adding the while loop.......
got the error message bool(false)

Any ideas?

dopey

fgetcsv() returns false on EOF or error. Could you add

$fp = fopen($filename,"r") or die('could not open file!');

to make sure the file is opening ok for reading?

Also, make sure the file is delimited with ',' and not any other character.

Other than that, without knowing the data in the csv, it's a bit difficult to know where the problem is 🙁 Perhaps you could attach the file (you could remove any sensitive data first)?

ejwf

thanks for your help dopey........

have tried $fp = fopen($filename,"r") or die('could not open file!');
still getting bool(false), and no other error message.

have attached file client.csv
I have had to replace some characters with * (not confidential info, but just to be on the safe side!)

If you can find anything wrong or make any suggestions - that would be great - thanks

ejwf

here's the attachment Client1

dopey

Hi,

I've just copied and pasted your code into a php file, changed the filename to client1.csv, added the die() after the fopen and removed the mysql_query() and it works fine. How weird! Bit baffled on this one...

I've attached the modified php file (it just echos out the sql at the moment, instead of executing it). Perhaps you could try it?

dopey

file (you'll have to rename it without the .txt at the end)

ejwf

have run this code

it echos out a long list of all the records as follows;

INSERT INTO tblClient (ClientId, ConcesRef, CoName, Addr1, Addr2, Addr3, Town, County, Postcode, Phone, Fax) VALUES ('43', '1', 'Happy Joes Eatery', 'Food street', '', '', 'Hamburg', '', '', '01234 567896', '') INSERT INTO tblClient (ClientId, ConcesRef, CoName, Addr1, Addr2, Addr3, Town, County, Postcode, Phone, Fax) VALUES ('1', '1', '888ire 777 Ltd', 'vilions', ' Close, Amington', 'Tamworth', '', '', 'B77 P', '018 *20', '') etc.etc.etc.

So it is reading the file OK.

As an aside, whilst we have been working on this I tried adding the data through phpMysqlAdmin using;

LOAD DATA INFILE '$file' INTO TABLE tblClient FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\r\n'"

Originally I had a problem with password permissions but this has now been resolved by the hosts' admin, but it still doesn't add the data from the .csv file.and gives an error message 'Duplicate entry '43' for key 1'

Frankly I am baffled !!

ejwf

Had another look at your code.
Noticed you took the second while loop out
So I tried this and ran the Insert Into.... Hey Presto I got a database full of data

the code I finished up with was

require_once ('../mysql_connect.php');//connect to the database

$filename = "Client.csv";
$fp = fopen($filename,"r") or die('could not open file!');

while (!feof($fp)){ 

$data = fgetcsv($fp,5000); 

$ClientId = $data[0]; 
$ConcesRef = $data[1]; 
$CoName = $data[2]; 
$Addr1 = $data[3]; 
$Addr2 = $data[4]; 
$Addr3 = $data[5]; 
$Town = $data[6]; 
$County = $data[7]; 
$Postcode = $data[8]; 
$Phone = $data[9]; 
$Fax = $data[10]; 

$sql = "INSERT INTO tblClient (ClientId, ConcesRef, CoName, Addr1, Addr2, Addr3, Town, County, Postcode, Phone, Fax) 
VALUES ('$ClientId', '$ConcesRef', '$CoName', '$Addr1', '$Addr2', '$Addr3', '$Town', '$County', '$Postcode', '$Phone', '$Fax')";
$result = mysql_query($sql) 
 or die(mysql_error()); 
   }

thanks so much for your help 😃