Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Trying to read in a file into mysql, only 1 set of data reads, what gives?

Malfoy

Malfoy

macrumors 6502a
Original poster
Nov 1, 2005
688
2
Ok I'm working on a project and I'm trying to dump a large amount of info into the mysql db. I'm trying with a test file first and it seems no matter how I format it, the script only reads the first set. Here is the code:

code said:
<html>
<body>
<p>
<?php
include "connect.php";

if(isset($_POST['submit']))

{

$filename=$_POST['filename'];

$handle = fopen("$filename", "r");

while (($data = fgetcsv($handle, 1000000, ",")) !== FALSE)

{



$import="INSERT into RestLocation(RID, StreetAdd, City, State, zip, Phone) values('$data[0]','$data[1]','$data[2]', '$data[3]', '$data[4]', '$data[5]' )";

mysql_query($import) or die(mysql_error());

}

fclose($handle);

print "Import done";



}

else

{



print "<form action='import.php' method='post'>";

print "Type file name to import:<br>";

print "<input type='text' name='filename' size='20'><br>";

print "<input type='submit' name='submit' value='submit'></form>";

}

?>

</p>
</body>
Click to expand...

This is the source (a csv file):
csv said:
1,
2254 TIGER TOWN PARKWAY,
OPELIKA, AL, 36801,
334 -749-1255,
1,
3360 ROSS CLARK CIRCLE,
DOTHAN, AL, 36303,
334 -792-8777,
1,
401 OXFORD EXCHANGE BLVD,
OXFORD, AL, 36203,
256 -831-4911
Click to expand...

No matter how I format the csv file the code stops after reading the first phone number. Any ideas on what I need to change so it actually moves on to the next set of addresses/phone numbers? Thanks in advance.
 
Malfoy said:
No matter how I format the csv file the code stops after reading the first phone number. Any ideas on what I need to change so it actually moves on to the next set of addresses/phone numbers? Thanks in advance.
Click to expand...
So you did try to put every record in a row?

The code you provided doesn't work, because the query will fail and exists in the die()

It only reads the "1" in the first iteration, so that $data[>0] is missing

For debugging I would skip the mysql part first und just try to get the rows printed row by row.
 
I didn't try putting the information in a row because the way the data is given to me, I'd have to manually format it to make that happen. The code works for me though so its odd it doesn't work for you.
This is the output in phpmyadmin of what it does when ran once:
dbresult.png


It never starts on the 2nd set of data.

I do a print out and it only prints the first set of information.
I set everything to 1 line and it still only prints the first row.
If i remove the !=false check, it still only reads 1 line.
Sigh I'm going to bed now.

Attached is a screenshot of my desktop showing everything. its a big pic so plan accordingly, :)
 

Attachments

  • phpsit.png
    phpsit.png
    111.8 KB · Views: 94
is $data[6] happen to be "1" again?

How shall fgetcsv know, that the new record starts after "334 -749-1255,"?

Use print_r($data)
 
Why I'm not in bed yet given its 430 here is beyond me.


mheidt said:
is $data[6] happen to be "1" again?

How shall fgetcsv know, that the new record starts after "334 -749-1255,"?

Use print_r($data)
Click to expand...

angelneo said:
What is your row record delimiter in your csv? line break? seems like one giant line to me.
Click to expand...
Good questions and I don't know. I just assumed (first mistake I know) that it would treat the file as 0, 1, 2, 3,4, 5 new record time 0, 1, 2, 3,4,5 and repeat until finished since I told it to parse 6 things over and over until the end of file.

How do I go about setting a row delimiter?
 
To help you get the records onto one line you can use a regular expression find and replace. This can be done with TextWrangler (free) or others.

Your CSV:
Code: 
1,
2254 TIGER TOWN PARKWAY,
OPELIKA, AL, 36801,
334 -749-1255,
1,
3360 ROSS CLARK CIRCLE,
DOTHAN, AL, 36303,
334 -792-8777,
1,
401 OXFORD EXCHANGE BLVD,
OXFORD, AL, 36203,
256 -831-4911

Use this as your regular expression find: \r(?!(\d,).*$)

You may need to change the \r at the beginning depending on what format the file is. Alternatives would be \n (unix) or \r\n (Windows).

Then for the replace part you just put: \1

This'll change the above input to:
Code: 
1,2254 TIGER TOWN PARKWAY,OPELIKA, AL, 36801,334 -749-1255,
1,3360 ROSS CLARK CIRCLE,DOTHAN, AL, 36303,334 -792-8777,
1,401 OXFORD EXCHANGE BLVD,OXFORD, AL, 36203,256 -831-4911

Then maybe you'll have better luck with a properly formatted CSV file. Though those commas at the end of each line may cause a problem, but can be fixed with a second find and replace: find( ,\r ) replace with( \r )

Let me know if you have any problems with that.
 
Ok, after a complete rewrite of the code we got it to work with reading mulitple lines in with each address on one line. So that problem is gone. Now the last thing I need to figure out is how to convert


2254 Tiger Town Parkway
Opelika, AL, 36801
334-749-1255
3360 Ross Clark Circle
Dothan, AL, 36303
334-792-8777

to

2254 Tiger Town Parkway, Opelika, AL, 36801, 334-749-1255
3360 Ross Clark Circle, Dothan, AL, 36303, 334-792-8777

I ran your expression angel on a version of the data and it works but it seems to be dependent on the number 1 being there. When the 1 is removed it makes everything 1 line. Can you try to explain to me what each part of ur expression for find is doing as well as pt me on how I can bring it all onto 1 line without the #1 being there. Muchos gracias in advance.
 
Yeah, it kind of depended on that number at the beginning.

A quick analysis of the first regular expression find: \r(?!(\d,).*$)
\r = new lines (on Mac)
?!(\d,) = is not (?!) a digit (\d) followed by a comma
.*$ = the rest of the line has miscellaneous characters

New regular expression find for your new situation: \r((\D|\d{3}-).*$)
\r = same as before, new lines
\D = not a digit (address line)
| = or
\d{3}- = three digits followed by dash (first 4 characters of telephone number

The replace on this is , \1

So you end up with lines that start with non-digits or 3 digits and a dash, get rid of the newline at the beginning of that line and replace it with a comma and space. The above at least works with the two records you posted here, but be sure to check it against a number of records.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back