PHP: importing CSV

Brad_Williams

Greetings. I have been having a bit of a problem with a PHP site that I've been creating. Basically, it provides a web-based front-end for customers to access records for their calibrations. The records are stored MySQL, but they are imported as a CSV file. However, the standard PHP CSV function doesn't work, since some of the fields contain a quotation mark, and the CSV encloses the fields in ". So, I wrote my own code to process the data, but it WTFs. Badly. (filled the Application log AND the System log.)

if($_POST['action'] == "gages")
{
if($gagedata = fopen("GAGES.csv","r+"))
{
 echo("File opened. ");
}
else
{
 echo("Could not open gage file!");
}

$record = 0;
while(!feof($gagedata))
{
 $row = fgets($gagedata);
 $data = array();
 $data[$record] = array();

 $offset = 0;
 $field = 0;
 $comma = strpos($row, ",", $offset);

 while($comma)
 {
 $rawdata = substr($row, $offset, $comma);
 $length = strlen($rawdata) - 1;
 $data[$record][$field] = substr($rawdata, 0, $length);
 $offset += $comma;
 $field++;

 $row = substr($row, ($offset + 1));
 }


$check = mysql_query("SELECT * from nem_gages WHERE control=$data[0]");

$sstring = strstr($data[$record][0], "#");
$prefix = str_replace($sstring, "", $data[0]);

if($check)
{
if(mysql_query("UPDATE contacts SET control = '$data[$record][0]', company = '$data[$record][1]', dept = '$data[$record][2]', hilocation = '$data[$record][3]', name = '$data[$record][4]', mfg = '$data[$record][5]', model = '$data[$record][6]', sn = '$data[$record][7]', gagetype = '$data[$record][8]', street1 = '$data[$record][10]', street2 = '$data[$record][11]', city = '$data[$record][12]', state = '$data[$record][13]', zip = '$data[$record][14]', contact = '$data[$record][15]', status ='$data[$record][16]', lastcal = '$data[$record][19]', duedate = '$data[$record][20]', prefix = '$prefix' WHERE control = '$data[$record][0]"))
{
echo "$data[$record][0] sucessfully updated. ";
}
else
{
echo " $data[$record][0] failed to update ";
}

}
else
{

if(mysql_query("INSERT into nem_gages(control, company, dept, hilocation, name, mfg, model, sn, gagetype, dfilename, street1, street2, city, state, zip, contact, status, caltype, calint, lastcal, duedate, curpart, curhours, prefix) VALUES('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]','$data[15]','$data[16]','$data[19]','$data[20]',$prefix`)"))
{
echo "Record $data[$record][0] sucessfully added. ";
}
else
{
echo "Failed to add record $data[$record][0] ";
}
}
$record++;
}
}

Any ideas as to (a) how I can use the standard function with the " in the fields, or (b) fix this WTF of code?

Regards,
Brad Williams
Northeast Metrology

R_Flowers

Try this function on some sample .csv files:

<?php

function CSV2Array($content, $delim = ',', $encl = '"', $optional = 1)
{
 $reg = '/(('.$encl.')'.($optional?'?(?(2)':'(').
'[^'.$encl.']'.$encl.'|[^'.
$delim.'\r\n]))('.$delim.'|\r\n)/smi';

 preg_match_all($reg, $content, $treffer);
 $linecount = 0;

 for ($i = 0; $i<=count($treffer[3]);$i++)
 {
 if(isset($treffer[1][$i])){
 $liste[$linecount][] = $treffer[1][$i];
 if(isset($treffer[3][$i])){
 if ($treffer[3][$i] != $delim)
 $linecount++;
 }
 }
 }
 return $liste;
}

// usage for example:
$content = join('',file('test.csv'));
$liste = CSV2Array($content);
echo "<pre>";
print_r($liste);
echo "</pre>";
?>

It came from http://www.php.net/manual/en/function.fgetcsv.php#50186

I did put in a couple of if(isset())s to get rid of some warnings. I did not take the time to discover why the function was trying to access an unset array variable, however. It may be something you want to figure out.

BTW, on the subject of setting cookies - was the only problem setting the cookies after the header, or was the 'secure flag' an issue as well?

Brad_Williams

R.Flowers, thank you again for the quick response to my question. Unfortunately, there is an issue with the function that you posted: when I run the script, my error log shows "preg_match_all() expects parameter 2 to be string, array given in ...", and the script basically craps itself (my browser's just been "waiting for page" for 5 minutes now (no timeout, though).).

Also, the issue with setting cookies was simply a matter of passing them after the header was sent. The secure parameter didn't seem to make a difference.

Regards,
Brad Williams
Northeast Metrology

R_Flowers

Hmm... sorry about that, but php.net is a good reference in any case. The example I posted did work on my machine (XP Pro/IIS/PHP chimera). The file 'test.csv' in my case was a simple 3 x 3 Excel sheet saved in CSV format, with a couple of values like "one, two, three" (commas between quotes) to test.

Did you use the join('',file('test.csv')) syntax? The file() function returns an array, and I suppose the join junction here implodes it into a string.

Brad_Williams

Ah, that may have done it. I didn't use join(), so that might be the problem... I'll test it out, mess with it a bit, and see what happens.

Regards,
Brad Williams
Northeast Metrology

PS: IIS is the spawn of Satan himself. ;)

R_Flowers

@Brad Williams said:

PS: IIS is the spawn of Satan himself. ;)

Heh! And to make matters worse, it's not even version 6, it's 5.1... Oh, well it's just for light development.

Brad_Williams

I've got a LAMP stack for internal dev/testing.

Back on topic, I think that it's working: the server's processing like it should be, but it's taking forever (31,000+ records, so it's almost to be expected). I'll report back if/when it ever finishes processing.

Brad_Williams

Follow up: no dice..I think that the function that you posted is a WTF or something. There's an IF statement that contains the mysql_query statement (returns a boolean), but for each entry, it returns false, so it shows "Failed to add record Array[0]". Interestingly, Array[0] is supposed to be a column from the table (note my original post... $data[$record][0] is a unique control number which is supposed to appear instead of Array[0]). Also, print_r($data) doesn't print anything.

Regards,
Brad Williams
Northeast Metrology

EDIT: would it be easier if I posted my whole page of code?

R_Flowers

@Brad Williams said:

EDIT: would it be easier if I posted my whole page of code?

Maybe, although I don't feel I am much help :) That function does not have a reference to any MySQL functions, so...

How about posting a small snippet of example data (the CSV file)?

Brad_Williams

"CONTROL","COMPANY","DEPT","HLOCATION","NAME","MFG","MODEL","SN","GAGETYPE","DFILENAME","STREET1","STREET2","CITY","STATE","ZIP","CONTACT","STATUS","CALTYPE","CALINT","LASTCAL","DUEDATE","CURPARTS","CURHOURS"
"02-09442","EK (KAD) $8.00","886, Area: REC INSP","OMS, Bldg. 9, Flr. 1","Micrometer 1"","Mitutoyo","Digital","02-09442","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/9/1999","8/9/2000","0","0"
"02-09443","EK (KAD) $8.00","886, Area: REC INSP","OMS, Bldg. 9, Flr. 1","Micrometer 25mm","Mitutoyo","Digital Ball","02-09443","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/9/1999","8/9/2000","0","0"
"02-09450","EK (KAD) $8.00","153, Area: SPECIAL MACH","DRAWER #443, Bldg. 9, Flr. 1","Micrometer 1"","Mitutoyo","Digital","02-09450","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/4/1999","8/4/2000","0","0"
"02-26453","EK (KAD) $8.00","159","N/A, Bldg. 9, Flr. 1","Indicator Manual 1000","Mitutoyo","543-182","02-26453","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","MARK CONNOLLY : (726-9496)","Out of Service","0","0","8/2/1999","8/2/2000","0","0"
"02-26454","EK (KAD) $8.00","159, Area: LAB","159, Bldg. 9, Flr. 1","Indicator .0001"","Mitutoyo","Digital","02-26454","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/4/1999","8/4/2000","0","0"
"02-27920","EK (KAD) $8.00","159","Dept. 159, Bldg. 9, Flr. 1","Micrometer Manual 1000","Starrett","N/A","02-27920","Misc. Report","\GDF\iscrpt.gdf","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","MARK CONNOLLY : (726-9496)","Out of Service","0","0","8/17/2000","8/18/2001","0","0"
"02-28649","EK (KAD) $8.00","157, Area: DTM","J.HUMNICKY, Bldg. 9, Flr. 1","Micrometer 2"","Starrett","N/A","02-28649","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/9/1999","8/9/2000","0","0"
"02-28654","EK (KAD) $8.00","157","Dept. 157, Bldg. 9, Flr. 1","Caliper Manual 1000","Federal","1"","02-28654","Misc. Report","\GDF\iscrpt.gdf","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","MARK CONNOLLY : (726-9496)","Out of Service","0","0","8/14/2000","8/15/2001","0","0"
"02-28671","EK (KAD) $8.00","719, Area: FIBER OPTICS","NANCY GOETZ, Bldg. 2, Flr. 1","Indicator .0001"","Brown & Sharpe - Bestest","N/A","02-28671","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/14/2000","8/15/2001","0","0"
"02-28679","EK (KAD) $8.00","719, Area: FIBER OPTICS","JIM BOYLE, Bldg. 2, Flr. 1","Micrometer 1"","Starrett","N/A","02-28679","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/15/2000","8/16/2001","0","0"
"02-30349","EK (KAD) $8.00","153, Area: Centering","Centering, Bldg. 9, Flr. 1","Rod Standard Manual 1000","Mitutoyo","167-143","02-30349","Misc. Report","\GDF\iscrpt.gdf","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","MARK CONNOLLY : (726-9496)","Out of Service","0","0","8/17/2000","8/18/2001","0","0"
"02-30349-#1","EK (KAD) $8.00","153, Area: OPTICS","CENTERING, Bldg. 9, Flr. 1","Micrometer Set Plug 2"","N/A","N/A","02-30349-#1","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/17/2000","8/18/2001","0","0"
"02-30349-3","EK (KAD) $8.00","153, Area: OPTICS","CENTERING, Bldg. 9, Flr. 1","Micrometer Set Plug 3"","N/A","N/A","02-30349-3","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/17/2000","8/18/2001","0","0"
"02-31225","EK (KAD) $8.00","157, Area: PGM","MIKE BRIZZEE, Bldg. 9, Flr. 1","Micrometer 1"","Mitutoyo","Indicating","02-31225","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/14/2000","8/15/2001","0","0"
"02-31526","EK (KAD) $8.00","000","000, Bldg. 0, Flr. 0","Indicator Manual 1000","Swedish Gage Co.","N/A","02-31526","Misc. Report","\GDF\iscrpt.gdf","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","MARK CONNOLLY : (726-9496)","Out of Service","0","0","8/17/2000","8/18/2001","0","0"
"02-31527","EK (KAD) $8.00","000","000, Bldg. 0, Flr. 0","Indicator Manual 1000","N/A","N/A","02-31527","Misc. Report","\GDF\iscrpt.gdf","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","MARK CONNOLLY : (726-9496)","Out of Service","0","0","8/17/2000","8/18/2001","0","0"
"02-32250","EK (KAD) $8.00","153, Area: OPTICS","CENTERING, Bldg. 9, Flr. 1","Micrometer Set Plug 1"","N/A","N/A","02-32250","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/12/2000","8/13/2001","0","0"
"02-32250-2","EK (KAD) $8.00","153, Area: OPTICS","CENTERING, Bldg. 9, Flr. 1","Micrometer Set Plug 2"","N/A","N/A","02-32250-2","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/17/2000","8/18/2001","0","0"
"02-32250-3","EK (KAD) $8.00","153, Area: MODEL SHOP","OPTICS, Bldg. 9, Flr. 1","Micrometer Set Plug 3"","N/A","N/A","02-32250-3","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/3/1999","8/3/2000","0","0"
"02-32250MM","EK (KAD) $12.00","153, Area: OPTICS","CENTERING, Bldg. 9, Flr. 1","Micrometer Set Plug 125MM","N/A","N/A","02-32250MM","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/14/2000","8/15/2001","0","0"
"02-33424","EK (KAD) $64.79","000, Area: N/A","QC","Caliper Manual 1000","Starrett","120","02-33424","Caliper Inch","\GDF\6caliper.gdf","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","MARK CONNOLLY : (726-9496)","Out of Service","0","0","9/6/2000","9/7/2001","0","0"
"02-33426","EK (KAD) $8.00","159","Dept. 159, Bldg. 9, Flr. 1","Depth Micrometer Manual 1000","Starrett","440","02-33426","Misc. Report","\GDF\iscrpt.gdf","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","MARK CONNOLLY : (726-9496)","Out of Service","0","0","8/17/2000","8/18/2001","0","0"
"02-34027","EK (KAD) $8.00","719, Area: FIBER OPTICS","NANCY GOETZ, Bldg. 2, Flr. 1","Indicator .0001"","Mitutoyo","Digital","02-34027","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","8/17/2000","8/18/2001","0","0"
"02-39727","EK* (KAD) $8.00","153, Area: N/A","153, Bldg. 9, Flr. 1","Indicator 0-2"","Mitutoyo","Digimatic","02-39727","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","9/12/2000","8/31/2001","0","0"
"02-42219","EK (KAD) $8.00","153, Area: MACHINE SHOP","F.SCHUBERT, Bldg. 9, Flr. 1","Micrometer 1"","Starrett","N/A","02-42219","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","9/12/2000","8/31/2001","0","0"
"02-42220","EK (KAD) $8.00","153, Area: MACHINE SHOP","F.SCHUBERT, Bldg. 9, Flr. 1","Micrometer 2"","Starrett","N/A","02-42220","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","9/12/2000","8/31/2001","0","0"
"02-42221","EK (KAD) $11.00","153, Area: MACHINE SHOP","F.SCHUBERT, Bldg. 9, Flr. 1","Caliper 6"","Starrett","Dial","02-42221","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","9/12/2000","8/31/2001","0","0"
"02-42222","EK (KAD) $8.00","153, Area: MACHINE SHOP","F.SCHUBERT, Bldg. 9, Flr. 1","Micrometer 0-6"","Starrett","Depth","02-42222","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","9/12/2000","8/31/2001","0","0"
"02-42223","EK (KAD) $11.00","153, Area: MACHINE SHOP","F.SCHUBERT, Bldg. 9, Flr. 1","Caliper 6"","Mitutoyo","Digimatic","02-42223","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","9/12/2000","8/31/2001","0","0"
"02-42761","EK (KAD) $8.00","153, Area: MACHINE SHOP","F.SCHUBERT, Bldg. 9, Flr. 1","Indicator","Federal","N/A","02-42761","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","9/12/2000","8/31/2001","0","0"
"02-43532","EK (KAD) $8.00","153, Area: MACHINE SHOP","F.SCHUBERT, Bldg. 9, Flr. 1","Micrometer 2"","Mitutoyo","N/A","02-43532","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","9/12/2000","8/31/2001","0","0"
"02-43563","EK (KAD) $11.00","153, Area: MACHINE SHOP","F.SCHUBERT, Bldg. 9, Flr. 1","Caliper 6"","Mitutoyo","Dial","02-43563","EK Report","\GDF\EKREPORT.GDF","901 ELMGROVE ROAD","EK","ROCHESTER","NY","14653","JOE D. TIPPS : (726-1093)","Out of Service","0","0","9/12/2000","8/31/2001","0","0"
"111","ACCUMED TECHNOLOGIES INC.","QC","QC","Caliper","","","","Caliper Metric","\GDF\Caliperm.gdf","150 BUD MIL DRIVE","ATI","BUFFALO","NY","14206","SHARON STABLER : (853-1800, ext.125)","In Service","0","0","6/13/2006","6/14/2007","0","0"
"3M#1000","3M COMPANY","QC","QC","Aluminum Block Manual 1000","N/A",".5"x4"x4"","1000","Block","\GDF\Block.gdf","GATE 3 MAINTENANCE DOCK","305 SAWYER AVE. (3M)","TONAWANDA","NY","14150","G. MADONIA","Out of Service","0","0","7/12/2001","7/12/2002","0","0"
"3M#1001","3M COMPANY","QC","QC","Aluminum Block Manual 1000","N/A","4"x4"x4"","1001","Block","\GDF\Block.gdf","GATE 3 MAINTENANCE DOCK","305 SAWYER AVE. (3M)","TONAWANDA","NY","14150","G. MADONIA","Out of Service","0","0","7/12/2001","7/12/2002","0","0"
"3M#1002","3M COMPANY","QC","QC","Aluminum Block Manual 1000","N/A","4"x4"x4"","1002","Block","\GDF\Block.gdf","GATE 3 MAINTENANCE DOCK","305 SAWYER AVE. (3M)","TONAWANDA","NY","14150","G. MADONIA","Out of Service","0","0","7/12/2001","7/12/2002","0","0"
"3M#1003","3M COMPANY","QC","QC","Aluminum Block Manual 1000","N/A",".5"x4"x4"","1003","Block","\GDF\Block.gdf","GATE 3 MAINTENANCE DOCK","305 SAWYER AVE. (3M)","TONAWANDA","NY","14150","G. MADONIA","Out of Service","0","0","7/12/2001","7/12/2002","0","0"
"3M#1004","3M COMPANY","QC","QC","Aluminum Block Manual 1000","N/A",".5"x4"x4"","1004","Block","\GDF\Block.gdf","GATE 3 MAINTENANCE DOCK","305 SAWYER AVE. (3M)","TONAWANDA","NY","14150","G. MADONIA","Out of Service","0","0","6/16/2000","6/17/2001","0","0"
"3M#1005","3M COMPANY","QC","QC","Aluminum Block Manual 1000","N/A",".5"x4"x4"","1005","Aluminum Block","\GDF\Aluminum.gdf","GATE 3 MAINTENANCE DOCK","305 SAWYER AVE. (3M)","TONAWANDA","NY","14150","VINCE OLIVIERI : (876-1596, Ext.205)","Out of Service","0","0","8/8/2001","8/9/2002","0","0"

I don't know how this mess is going to look when it gets posted, but here are a few lines of the CSV file (there are like 23 columns in the data). And as for the MySQL functions, I'm using the data from the function you posted to do an INSERT.

R_Flowers

It turned out about as well as could be expected.

The function I posted (which I borrowed from a user posting at php.net) does not contain mysql_query, right?

Now, your code you posted before does. You will have to take a look at the array that function returns, and integerate that into your code.