Importing data from Text file thru web

benatdot

I need to create a single page that allows a user to import a CSV text file into a database, replacing everything there. Its coming from an exporting function in a different program. Now I can do this with the fscanf function but I run into formatting problems.

I can run the following:
fscanf ($handle, "%s;%s;%s...\n")

it will import the correct number of rows. However, all fields will be empty (database is mysql).

I'm running into a problem with the field formatting. Here's a sample from my data:
"130673";"RTE 7 STAR PROJECT";"D.AHMADZAI";"1";2003/08/15 0:00:00;1.0e-03;"SCHENECTADY";;"Z01";;;"180674, STAR"

Fields are separated by ';'. Notice how not every field is surrounded by a " ". My question is, how can a strip out the " " or handle them properly so I can allow this user to import all data????

Thanks,
Ben

sid

$import holds the incoming line;

$token = explode(";" strreplace("\"", "", $import));

will return the following array:

$token[0] = "130673"
$token[1] = "RTE 7 STAR PROJECT"
$token[2] = "D.AHMADZAI"
$token[3] = "1"
$token[4] = "2003/08/15 0:00:00"
$token[5] = "1.0e-03"
$token[6] = "SCHENECTADY"
$token[7] = ""
$token[8] = "Z01"
$token[9] = ""
$token[10] = ""
$token[11] = "180674, STAR"

then you just insert that stuff into your database

benatdot

Thanks for the reply. When I try to print out fields (to test before I'm inserting into the database), php just prints Array. I'm having trouble getting this to work right with fscanf and any fields that include spaces. Example:

This works fine, inserts data in the correct fields, but when a string that has to be inserted has a space, it stops and goes to the next record

while ($import = fscanf ($handle, "%s;%s;%s;%s;%s\n")) {
list($line) = $import;
//Seperate fields from ;
$data = explode(";", str_replace("\"", "", $line));
list ($pin, $bin, $descr, $treat, $let) = $data;

//Insert data
if(!$result = mysql_query("INSERT INTO BRData (PIN, BIN, DESCR, Treatment, LET) VALUES ('$pin', '$bin', '$descr', '$treat', '$let')", $link_id)){
showerror();
}
}

I've tried using explode without the list construct, but that just prints out Array, and having Array in every field of my database is no help 🙂

If someone could answer this question as well as another, that would be a big help.

What datatype does fscanf return? An array or a string? I'm having trouble just printint out the line of text fscanf will get just for testing purposes. That prints out array as well. Thanks for the help,
ben

benatdot

Well I found an answer here:
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10252173&highlight=fscanf

Which referenced:
http://us2.php.net/manual/en/function.fgetcsv.php

I then modified my code to this:

//Get line of text
while($line = fgetcsv ($handle, 1000, ";")) {
list ($pin, $descr, $pm, $onoff, $targ, $cost, $county, $dnum, $fund, $psecost, $lowb, $remark) = $line;

//Insert line
if(!$result = mysql_query("INSERT INTO LetList (PIN, DESCR, PM, ONOFF, TARG, Cost, County, DNUM, FUND, PSECOST, LOWB, Remark)
VALUES '$pin', '$descr', '$pm', '$onoff', '$targ', '$cost', '$county', '$dnum', '$fund', '$psecost', '$lowb', '$remark')", $link_id)){
showerror();
}

}

THis inserts in a bunch of rows just the way I want them. Only problem is it will error off with a MySQL Error1064 when it gets to a certain row. I checked my source data, which is a CSV and there isn't anything special about the row its complaining about. Same thing, just different.