Errors reading .csv file data into mysql database! Plz help!

sketchgal · January 6, 2012, 12:21pm

I have the following code set up, which has worked for different data sources in the past.
I now have a new .csv file which is slightly differently formatted and I can’t get this file to work with it at all!

Basically I need to read the contents of the .csv file into a database which already exists.
The column count with the query etc is correct but the issues I’m getting I think are due to there the " marks are in the .csv file.

The " marks are knocking off the array and the data is being put into the wrong columns in the db as a result.

Can anyone tell me how to fix this please?
Any help would be much appreciated.

Here’s what the current .php script looks like:

$myFile = "test.csv";
$fh = fopen($myFile, 'r');

while(!feof($fh))
{
    $data = fgets($fh);
    list($data1,$data2) = explode('"',$data,2);
    $data2 = str_replace(array(",", "'", '*', '£', 'é'),array('#', '#', '', '&pound;', '&eacute;'),$data2);//replaces comma's with hash mark to insert into db correctly
	//$data2 = str_replace(array("'",'"'),array('*',''),$data2);//replaces apostrophe with star to insert into db correctly
    $data = explode(',',$data1.$data2);


    print_r ($data);
	
//foreach($data as $row){
$query = "INSERT INTO Used_Stock (Feed_Id, Vehicle_Id, Full_Registration, Colour, Fuel_Type, Year, Mileage, Body_Type, Doors, Make, Model, Variant, Engine_Size, Price, Transmission, Picture_Refs, Service_History, Previous_Owner, Category, Four_Wheel_Drive, Options, Comments, New, Used, Site, Origin, V5, `Condition`, ExDemo, Franchise_Approved, Trade_Price, Trade_Price_Extra, Service_History_Text, Cap_Id,Type) VALUES ('".$data[0]."', '".$data[1]."', '".$data[2]."' , '".$data[3]."', '".$data[4]."', '".$data[5]."', '".$data[6]."', '".$data[7]."', '".$data[8]."', '".$data[9]."', '".$data[10]."', '".$data[11]."', '".$data[12]."', '".$data[13]."', '".$data[14]."', '".$data[15]."', '".$data[16]."', '".$data[17]."', '".$data[18]."', '".$data[19]."', '".$data[20]."', '".$data[21]."', '".$data[22]."', '".$data[23]."', '".$data[24]."', '".$data[25]."', '".$data[26]."', '".$data[27]."', '".$data[28]."', '".$data[29]."', '".$data[30]."', '".$data[31]."', '".$data[32]."', '".$data[33]."','2')";
		 mysql_query($query) or die(mysql_error());
 //print_r ($row);
//}
	//echo 'What a Success';
	}

Here’s some of the data from the .csv file:

Feed_Id,Vehicle_ID,FullRegistration,Colour,FuelType,Year,Mileage,BodyType,Doors,Make,Model,Variant,EngineSize,Price,Transmission,PictureRefs,ServiceHistory,PreviousOwners,Category,FourWheelDrive,Options,Comments,New,Used,Site,Origin,V5,Condition,ExDemo,FranchiseApproved,TradePrice,TradePriceExtra,ServiceHistoryText,Cap_Id
105461,397496,SK11RCO,“Sunlight Silver”,Diesel,2011,9,Estate,5,Mazda,6,TS2,2183,17495,Manual,“3691-SK11RCO_1.jpg,3691-SK11RCO_2.jpg,3691-SK11RCO_3.jpg,3691-SK11RCO_4.jpg,3691-SK11RCO_5.jpg,3691-SK11RCO_6.jpg,3691-SK11RCO_7.jpg,3691-SK11RCO_8.jpg,3691-SK11RCO_9.jpg”,1,CARS,Cloth,“Mazda6 2.2D Estate TS2 163ps”,N,Y,C,UK,N,N,Y,47147
105461,397498,SL11SXP,Graphite,Petrol,2011,3423,Hatchback,5,Mazda,3,TS2,1598,12495,Manual,“3693-SL11SXP_1.jpg,3693-SL11SXP_2.jpg,3693-SL11SXP_3.jpg”,1,CARS,“Cloth, Black, 16’’ alloy wheels, AM/FM Radio/Single CD, Anti-lock Braking System (ABS), Automatic climate control, Automatic headlights, Automatic wipers with rain sensor, Body coloured bumpers, Body coloured door handles, Body coloured door mirrors, Body coloured rear roof spoiler, Cruise control, Driver & passenger airbags, Dynamic Stability Control (DSC), Electric heated door mirrors, Electric power steernig, Electric windows, Electronic Brake Distribution (EBD), Emergency Brake Assist (EBA), Front fog lamps, Front side airbags, Front/rear curtain airbags, High level brake light, ISOFIX child seat anchorage, MAIDA system, Remote central door locking, Steering wheel audio controls”,“Mazda3 1.6 5dr TS2”,N,Y,C,UK,N,N,Y,43576

Raju_Gautam · January 6, 2012, 1:23pm

I would recommend using existing PHP functions rather than parsing as string.
http://www.php.net/manual/en/function.str-getcsv.php
http://www.php.net/manual/en/function.fgetcsv.php
So that you don’t have to take care of lots of conditions like quotes, etc.

sketchgal · January 6, 2012, 4:11pm

Thanks for the reply and help on this. I’ve had a look at the links you sent me.
Its sort of working but not correctly.

I’ve pasted the file I now have below. Can yo tell me why some of the rows in the file are showing with the correct number of fields in the line and others are showing with less?
I think its down to " marks but I don’t know how to fix this. Can you help me please?

<?php


$row = 1;
if (($handle = fopen("file.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\
";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\
";
        }
    }
    fclose($handle);
}




function parse_csv($file,$comma=',',$quote='"',$newline="\
") {

    $db_quote = $quote . $quote;

    // Clean up file
    $file = trim($file);
    $file = str_replace("\\r\
",$newline,$file);

    $file = str_replace($db_quote,'&quot;',$file); // replace double quotes with &quot; HTML entities
    $file = str_replace(',&quot;,',',,',$file); // handle ,"", empty cells correctly

    $file .= $comma; // Put a comma on the end, so we parse last cell


    $inquotes = false;
    $start_point = 0;
    $row = 0;

    for($i=0; $i<strlen($file); $i++) {

        $char = $file[$i];
        if ($char == $quote) {
            if ($inquotes) {
                $inquotes = false;
                }
            else {
                $inquotes = true;
                }
            }

        if (($char == $comma or $char == $newline) and !$inquotes) {
            $cell = substr($file,$start_point,$i-$start_point);
            $cell = str_replace($quote,'',$cell); // Remove delimiter quotes
            $cell = str_replace('&quot;',$quote,$cell); // Add in data quotes
            $data[$row][] = $cell;
            $start_point = $i + 1;
            if ($char == $newline) {
                $row ++;
                }
            }
        }
    return $data;
    }
$myFile = "file.csv";
$fh = fopen($myFile, 'r');
$file = fgets($fh);
//$file = "WesternMazda_GForces/WesternMazda_GForces.csv";

/*$file ='Year,Make,Model,Description,Price
1997,Ford,E350,"ac, abs, moon",3000.00
1999,Chevy,"Venture ""Extended Edition""","",4900.00
1999,Chevy,"Venture ""Extended Edition, Very Large""","",5000.00
1996,Jeep,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799.00';*/

print_r(parse_csv($file)); // Returns what you'd expect. See http://en.wikipedia.org/wiki/Comma-separated_values for example

?>

Here’s some of the data from the .csv file:

Feed_Id,Vehicle_ID,FullRegistration,Colour,FuelType,Year,Mileage,Bodytype,Doors,Make,Model,Variant,EngineSize,Price,Transmission,PictureRefs,ServiceHistory,PreviousOwners,Description,FourWheelDrive,Options,Comments,New,Used,Site,Origin,v5,Condition,ExDemo,FranchiseApproved,TradePrice,TradePriceExtra,ServiceHistoryText,Cap_ID
105461,397492,SL11SZN,“Metropolitan Grey”,Petrol,2011,1732,Hatchback,3,Mazda,2,TS,1349,8495,Manual,“3687-SL11SZN_1.jpg,3687-SL11SZN_2.jpg,3687-SL11SZN_3.jpg,3687-SL11SZN_4.jpg,3687-SL11SZN_5.jpg,3687-SL11SZN_6.jpg”,N,1,“Mazda2 1.3 3dr TS Air Con”,“Cloth, Black, Air conditioning, AM/FM Radio/Single CD, Anti-lock Braking System (ABS), Driver & passenger airbags, Electric door mirrors, Electric front windows, Electronic Brake Distribution (EBD), Emergency Brake Assist (EBA), Height adjustable steering column, Immobiliser, ISOFIX child seat anchorage, MAIDA system, Power-assisted steering, Remote central door locking, Supplementary Restraint System, Thatcham category 1 alarm”,“Mazda2 1.3 3dr TS Air Con”,N,Y,C,UK,N,N,N,49263
105461,397496,SK11RCO,“Sunlight Silver”,Diesel,2011,9,Estate,5,Mazda,6,TS2,2183,17495,Manual,“9391-SK11RCO_2.jpg,9391-SK11RCO_3.jpg,9391-SK11RCO_4.jpg,3691-SK11RCO_5.jpg,3691-SK11RCO_6.jpg,3691-SK11RCO_7.jpg,3691-SK11RCO_8.jpg,9391-SK11RCO_9.jpg”,N,1,“Mazda6 2.2D Estate TS2 163ps”,“Cloth, Black, 17’’ alloy wheels, 60:40 split folding rear seat, Adjustable steering wheel, Anti-lock Braking System (ABS), Automatic headlights, Automatic wipers with rain sensor, Body coloured bumpers, Body coloured door handles, Climate control, Cruise control, Dual front airbags, Dynamic Stability Control (DSC), Electric heated door mirrors, Electric windows, Electronic Brake Distribution (EBD), Emergency Stop Signalling (ESS), Front fog lamps, Front side airbags, Front/rear curtain airbags, Heat reflecting glass, Immobiliser, ISOFIX child seat anchorage, MAIDA system, Power-assisted steering, Rear Parking Sensor, Rear Vehicle Monitoring system, Remote central door locking, Thatcham category 1 alarm”,“Mazda6 2.2D Estate TS2 163ps”,N,Y,C,UK,N,N,N,47147
105461,397498,SL11SXP,Graphite,Petrol,2011,3423,Hatchback,5,Mazda,3,TS2,1598,12495,Manual,“3693-SL11SXP_1.jpg,3693-SL11SXP_2.jpg,3693-SL11SXP_3.jpg”,N,1,“Mazda3 1.6 5dr TS2”,“Cloth, Black, 16’’ alloy wheels, AM/FM Radio/Single CD, Anti-lock Braking System (ABS), Automatic climate control, Automatic headlights, Automatic wipers with rain sensor, Body coloured bumpers, Body coloured door handles, Body coloured door mirrors, Body coloured rear roof spoiler, Cruise control, Driver & passenger airbags, Dynamic Stability Control (DSC), Electric heated door mirrors, Electric power steernig, Electric windows, Electronic Brake Distribution (EBD), Emergency Brake Assist (EBA), Front fog lamps, Front side airbags, Front/rear curtain airbags, High level brake light, ISOFIX child seat anchorage, MAIDA system, Remote central door locking, Steering wheel audio controls”,“Mazda3 1.6 5dr TS2”,N,Y,C,UK,N,N,N,43576
105461,397512,SL11SZF,“Crystal White Pearl”,Diesel,2011,4697,Hatchback,5,Mazda,3,Sport,2184,17495,Manual,“3707-SL11SZF_1.jpg,3707-SL11SZF_3.jpg,9407-SL11SZF_4.jpg,3707-SL11SZF_5.jpg,3707-SL11SZF_6.jpg,3707-SL11SZF_7.jpg,3707-SL11SZF_8.jpg,3707-SL11SZF_9.jpg”,N,1,“Mazda3 2.2D 5dr Sport with Leather (185ps)”,“Leather, Black Sports, Electric driver’s seat with memory, 17’’ alloy wheels, 6 CD autochanger, 60:40 split folding rear seat, Anti-lock Braking System (ABS), Automatic air conditioning, Automatic headlights, Automatic wipers with rain sensor, Auxiliary input jack and power outlet, Bluetooth hands-free kit, Body coloured bumpers, Body coloured door handles, Body coloured door mirrors, Body coloured rear spoiler, BOSE? premium audio system, Cruise control, Driver & passenger airbags, Dynamic Stability Control (DSC), Electric heated door mirrors, Electric windows, Electronic Brake Distribution (EBD), Emergency Brake Assist (EBA), Emergency Stop Signalling (ESS), Front fog lamps, Front side airbags, Immobiliser”,“Mazda3 2.2D 5dr Sport with Leather (185ps)”,N,Y,C,UK,N,N,N,43588
105461,397514,SL11SZJ,“Stormy Blue”,Diesel,2011,5797,Hatchback,5,Mazda,6,TS,2183,15995,Manual,“3709-SL11SZJ_1.jpg,3709-SL11SZJ_2.jpg,3709-SL11SZJ_3.jpg,3709-SL11SZJ_4.jpg,3709-SL11SZJ_5.jpg,3709-SL11SZJ_6.jpg,3709-SL11SZJ_7.jpg,3709-SL11SZJ_8.jpg,3709-SL11SZJ_9.jpg”,N,1,“Mazda6 2.2D 5dr TS 163 PS”,“Cloth, Black, 16’’ alloy wheels, 60:40 split folding rear seat, Adjustable steering wheel, Anti-lock Braking System (ABS), Body coloured bumpers, Body coloured door handles, Climate control, Cruise control, Dual front airbags, Dynamic Stability Control (DSC), Electric heated door mirrors, Electric windows, Electronic Brake Distribution (EBD), Emergency Stop Signalling (ESS), Front side airbags, Front/rear curtain airbags, Heat reflecting glass, Immobiliser, ISOFIX child seat anchorage, MAIDA system, Power-assisted steering, Remote central door locking, Single CD, Steering wheel audio controls, Supplementary Restraint System, Thatcham category 1 alarm, Traction Control System (TCS), Trip computer”,“Mazda6 2.2D 5dr TS 163 PS”,N,Y,C,UK,N,N,N,47143
105461,397515,SL11SZU,“Brilliant Black”,Diesel,2011,4956,MPV,5,Mazda,5,TS2,1560,17495,Manual,“9410-SL11SZU_1.jpg,3710-SL11SZU_2.jpg,3710-SL11SZU_3.jpg,3710-SL11SZU_4.jpg,3710-SL11SZU_5.jpg,3710-SL11SZU_6.jpg,3710-SL11SZU_7.jpg,3710-SL11SZU_8.jpg,3710-SL11SZU_9.jpg”,N,1,“Mazda5 1.6D 5dr TS2”,“Cloth, Black/Black, AM/FM Radio/Single CD, Anti-lock Braking System (ABS), Automatic climate control, Automatic headlights, Automatic wipers with rain sensor, Auxiliary input jack and power outlet, Body coloured bumpers, Body coloured door handles, Body coloured door mirrors, Cruise control, Driver & passenger airbags, Dynamic Stability Control (DSC), Electric heated door mirrors, Electric windows, Electronic Brake Distribution (EBD), Emergency Brake Assist (EBA), Emergency Stop Signalling (ESS), Front side airbags, Front/rear curtain airbags, High level brake light, ISOFIX child seat anchorage, Leather steering wheel, MAIDA system, Power-assisted steering, Privacy glass for rear windows, Rear Parking Sensor”,“Mazda5 1.6D 5dr TS2”,N,Y,C,UK,N,N,N,49172
105461,397518,SL11SZK,Black,Diesel,2011,5826,Estate,5,Mazda,6,Sport,2183,20495,Manual,“3713-SL11SZK_2.jpg,3713-SL11SZK_3.jpg,9413-SL11SZK_4.jpg,9413-SL11SZK_5.jpg,3713-SL11SZK_6.jpg,3713-SL11SZK_7.jpg,9413-SL11SZK_8.jpg,3713-SL11SZK_9.jpg”,N,1,“Mazda6 2.2D Estate Sport 180 PS”,“Part Leather, Black, 18’’ alloy wheels, 6 CD autochanger, 60:40 split folding rear seat, Adaptive front lighting system, Adjustable steering wheel, Aluminium pedal set, Anti-lock Braking System (ABS), Automatic headlight levelling, Automatic headlights, Automatic wipers with rain sensor, Bi-xenon headlights, Body coloured bumpers, Body coloured door handles, BOSE? sound system, Climate control, Cruise control, Dual front airbags, Dynamic Stability Control (DSC), Electric heated door mirrors, Electric windows, Electronic Brake Distribution (EBD), Emergency Stop Signalling (ESS), Front side airbags, Front/rear curtain airbags, Headlight cleaning, Heat reflecting glass, Heated front seats, Immobiliser”,“Mazda6 2.2D Estate Sport 180 PS”,N,Y,C,UK,N,N,N,47148
105461,397521,X22NKY,“Stormy Blue”,Petrol,2009,16912,Convertible,2,Mazda,MX-5,1.8i,1798,9995,Manual,“3716-X22NKY_1.jpg,3716-X22NKY_2.jpg,3716-X22NKY_3.jpg,3716-X22NKY_4.jpg,3716-X22NKY_5.jpg,3716-X22NKY_6.jpg,3716-X22NKY_7.jpg,3716-X22NKY_8.jpg”,N,1,“Mazda MX-5 1.8i SE”,“Cloth, Black, 16’’ alloy wheels, Anti-lock Braking System (ABS), Body coloured door mirrors, Driver & passenger airbags, Electric heated door mirrors, Electric windows, Electronic Brake Distribution (EBD), Immobiliser, Leather steering wheel, MAIDA system, MX-5 branded scuff plates, Passenger airbag deactivation, Power-assisted steering, Remote central door locking, Single CD, Thatcham category 1 alarm, Twin exhausts”,“Mazda MX-5 1.8i SE”,N,Y,C,UK,N,N,N,32469
105461,397523,SM11PVV,“CLEAR WATER BLUE”,Diesel,2011,5,Estate,5,Mazda,6,TS,2183,16995,Manual,“3718-SM11PVV_1.jpg,3718-SM11PVV_2.jpg,3718-SM11PVV_3.jpg,3718-SM11PVV_4.jpg,3718-SM11PVV_5.jpg,3718-SM11PVV_6.jpg,3718-SM11PVV_7.jpg”,N,1,“Mazda6 2.2D Estate TS 163 PS”,“Cloth, Black,”,“Mazda6 2.2D Estate TS 163 PS”,N,Y,C,UK,N,N,N,47146
105461,397528,SM11PVJ,Aluminum,Diesel,2011,7,MPV,5,Mazda,5,Sport,1560,18495,Manual,“3723-SM11PVJ_1.jpg,3723-SM11PVJ_2.jpg,3723-SM11PVJ_3.jpg,3723-SM11PVJ_4.jpg,3723-SM11PVJ_5.jpg,3723-SM11PVJ_6.jpg,3723-SM11PVJ_7.jpg,3723-SM11PVJ_8.jpg,3723-SM11PVJ_9.jpg”,N,1,“Mazda5 1.6D 5dr Sport”,“Leather, Black Sports, AM/FM Radio/Single CD, Anti-lock Braking System (ABS), Automatic climate control, Automatic headlights, Automatic wipers with rain sensor, Auxiliary input jack and power outlet, Body coloured bumpers, Body coloured door handles, Body coloured door mirrors, Body coloured rear spoiler, Cruise control, Driver & passenger airbags, Dynamic Stability Control (DSC), Electric heated door mirrors, Electric sliding doors, Electric windows, Electronic Brake Distribution (EBD), Emergency Brake Assist (EBA), Emergency Stop Signalling (ESS), Front fog lamps, Front side airbags, Front/rear curtain airbags, Heated front seats, High level brake light, ISOFIX child seat anchorage, Leather steering wheel”,“Mazda5 1.6D 5dr Sport”,N,Y,C,UK,N,N,N,49173
105461,397530,ST10OSJ,“Ice Blue”,Petrol,2010,7653,Hatchback,3,Mazda,2,TS2,1349,7695,Manual,“3725-ST10OSJ_1.jpg,3725-ST10OSJ_2.jpg,3725-ST10OSJ_3.jpg,3725-ST10OSJ_4.jpg,3725-ST10OSJ_5.jpg,3725-ST10OSJ_6.jpg,3725-ST10OSJ_7.jpg,3725-ST10OSJ_8.jpg”,N,1,“Mazda2 1.3 3dr TS2”,“Cloth, Black, Anti-lock Braking System (ABS), Body coloured bumpers, Body coloured door handles, Body coloured door mirrors, Driver & passenger airbags, Electric front windows, Electric heated door mirrors, Electronic Brake Distribution (EBD), Emergency Brake Assist (EBA), Front side airbags, Front/rear curtain airbags, Immobiliser, ISOFIX child seat anchorage, MAIDA system, Manual air conditioning, Power-assisted steering, Remote central door locking, Remote fuel flap release, Thatcham category 1 alarm”,“Mazda2 1.3 3dr TS2”,N,Y,C,UK,N,N,N,40222
105461,397535,VO11NCJ,“Crystal White Pearl”,Diesel,2011,5719,4x4,5,Mazda,CX-7,“Sport Tech”,2184,23995,Manual,“3730-VO11NCJ_1.jpg,3730-VO11NCJ_2.jpg,3730-VO11NCJ_3.jpg,3730-VO11NCJ_5.jpg,3730-VO11NCJ_6.jpg,3730-VO11NCJ_7.jpg,3730-VO11NCJ_8.jpg,3730-VO11NCJ_9.jpg”,N,1,“Mazda CX-7 2.2D Sport Tech”,“Leather, Black, 6-disc CD autochanger, Anti-lock Braking System (ABS), Automatic headlight levelling, Automatic headlights, Automatic wipers with rain sensor, Body coloured bumpers, Body coloured door mirrors, Body coloured rear roof spoiler, BOSE? premium audio system, Climate control, Digital clock, Dome interior light, Dual front airbags, Dynamic Stability Control (DSC), Electric heated door mirrors, Electric windows, Electronic Brake Distribution (EBD), Emergency Brake Assist (EBA), Emergency Stop Signalling (ESS), Front centre console with storage, Front fog lamps, Front side airbags, Front/rear curtain airbags, Headlamp-on warning, Headlight cleaning, High level brake light, Immobiliser”,“Mazda CX-7 2.2D Sport Tech”,N,Y,C,UK,N,N,N,45074
105461,397539,SK59OPU,“Metropolitan Grey”,Petrol,2009,12316,Hatchback,3,Mazda,2,TS2,1349,6995,Manual,“3734-SK59OPU_1.jpg,3734-SK59OPU_2.jpg,3734-SK59OPU_3.jpg,3734-SK59OPU_4.jpg,3734-SK59OPU_5.jpg,3734-SK59OPU_6.jpg,3734-SK59OPU_7.jpg,3734-SK59OPU_8.jpg”,N,1,“Mazda2 1.3 3dr TS2”,“Cloth, Black, Anti-lock Braking System (ABS), Body coloured bumpers, Body coloured door handles, Body coloured door mirrors, Driver & passenger airbags, Electric front windows, Electric heated door mirrors, Electronic Brake Distribution (EBD), Emergency Brake Assist (EBA), Front side airbags, Front/rear curtain airbags, Immobiliser, ISOFIX child seat anchorage, MAIDA system, Manual air conditioning, Power-assisted steering, Remote central door locking, Remote fuel flap release, Thatcham category 1 alarm”,“Mazda2 1.3 3dr TS2”,N,Y,C,UK,N,N,N,40222
105461,397541,SK59PHU,“Metropolitan Grey”,Petrol,2009,8124,Convertible,2,Mazda,MX-5,1.8i,1798,9995,Manual,“3736-SK59PHU_1.jpg,3736-SK59PHU_2.jpg,3736-SK59PHU_3.jpg,3736-SK59PHU_4.jpg,3736-SK59PHU_5.jpg,3736-SK59PHU_6.jpg,3736-SK59PHU_7.jpg,3736-SK59PHU_8.jpg”,N,0,“Mazda MX-5 1.8i SE”,“Cloth, Black, 16’’ alloy wheels, Anti-lock Braking System (ABS), Body coloured door mirrors, Driver & passenger airbags, Electric heated door mirrors, Electric windows, Electronic Brake Distribution (EBD), Immobiliser, Leather steering wheel, MAIDA system, MX-5 branded scuff plates, Passenger airbag deactivation, Power-assisted steering, Remote central door locking, Single CD, Thatcham category 1 alarm, Twin exhausts”,“Mazda MX-5 1.8i SE”,N,Y,C,UK,N,N,N,32469
105461,397542,SM60GYW,Black,Diesel,2010,11784,Hatchback,5,Mazda,3,Sport,2184,15495,Manual,“3737-SM60GYW_1.jpg,3737-SM60GYW_2.jpg,3737-SM60GYW_3.jpg,3737-SM60GYW_4.jpg,3737-SM60GYW_5.jpg,3737-SM60GYW_6.jpg,3737-SM60GYW_7.jpg,3737-SM60GYW_8.jpg,3737-SM60GYW_9.jpg”,N,1,“Mazda3 2.2D 5dr Sport with Leather (185ps)”,“Leather, Black Sports, Electric driver’s seat with memory, 17’’ alloy wheels, Anti-lock Braking System (ABS), Automatic climate control, Automatic headlights, Automatic wipers with rain sensor, Body coloured bumpers, Body coloured door handles, Body coloured rear roof spoiler, BOSE? premium audio system, Cruise control, Driver & passenger airbags, Dynamic Stability Control (DSC), Electric power steernig, Electric windows, Electronic Brake Distribution (EBD), Emergency Brake Assist (EBA), Front fog lamps, Front side airbags, Front/rear curtain airbags, High level brake light, ISOFIX child seat anchorage, Leather steering wheel, MAIDA system, Privacy glass for rear windows, Remote central door locking”,“Mazda3 2.2D 5dr Sport with Leather (185ps)”,N,Y,C,UK,N,N,N,43588
105461,469188,SK09UAL,“Stormy Blue”,Petrol,2009,28847,Hatchback,5,Mazda,3,Sport,1598,7995,Manual,“6648-SK09UAL_1.jpg,6648-SK09UAL_2.jpg,6648-SK09UAL_3.jpg”,N,1,“Mazda3 1.6 5dr Sport”,“Cloth, Black Sports, 17’’ alloy wheels, 3 x 3-point rear seatbelts, 6 CD autochanger, Anti-lock Braking System (ABS), Automatic climate control, Automatic headlights, Automatic wipers with rain sensor, Body coloured door handles, Body coloured door mirrors, Body coloured side mouldings, BOSE? premium audio system, Driver & passenger airbags, Dynamic Stability Control (DSC), Electric heated door mirrors, Electric windows, Electronic Brake Distribution (EBD), Emergency Brake Assist (EBA), Front fog lamps, Front/rear curtain airbags, Headlight cleaning, High level brake light, ISOFIX child seat anchorage, MAIDA system, Power-assisted steering, Privacy glass for rear windows, Remote central locking”,“Mazda3 1.6 5dr Sport”,N,Y,C,UK,N,N,N,40030

Raju_Gautam · January 6, 2012, 4:34pm

In just a quick test with your own data in a CSV file, I don’t see any problem with this code:


echo '<table width="100%" border="1">';
$row = 1;
if (($handle = fopen("products.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
		echo "<tr>\
";
        foreach($data as $d)
            echo "<td>" . (empty($d) ? '&nbsp;' : $d) . "</td>\
";
		echo "</tr>\
";
		$row++;
    }
    fclose($handle);
}
echo '</table>';

sketchgal · January 11, 2012, 12:12pm

Thanks very much for the response on this. Could you possibly show me how I can adapt the code above which displays the data correctly in a table to instead input the data into a database.
Previously I was using the following code but don’t know where to put it to work in with the above. Any help would be greatly appreciated.



$query = "INSERT INTO Used_Stock (Feed_Id, Vehicle_Id, Full_Registration, Colour, Fuel_Type, Year, Mileage, Body_Type, Doors, Make, Model, Variant, Engine_Size, Price, Transmission, Picture_Refs, Service_History, Previous_Owner, Category, Four_Wheel_Drive, Options, Comments, New, Used, Site, Origin, V5, `Condition`, ExDemo, Franchise_Approved, Trade_Price, Trade_Price_Extra, Service_History_Text, Cap_Id,Type) VALUES ('".$data[0]."', '".$data[1]."', '".$data[2]."' , '".$data[3]."', '".$data[4]."', '".$data[5]."', '".$data[6]."', '".$data[7]."', '".$data[8]."', '".$data[9]."', '".$data[10]."', '".$data[11]."', '".$data[12]."', '".$data[13]."', '".$data[14]."', '".$data[15]."', '".$data[16]."', '".$data[17]."', '".$data[18]."', '".$data[19]."', '".$data[20]."', '".$data[21]."', '".$data[22]."', '".$data[23]."', '".$data[24]."', '".$data[25]."', '".$data[26]."', '".$data[27]."', '".$data[28]."', '".$data[29]."', '".$data[30]."', '".$data[31]."', '".$data[32]."', '".$data[33]."','2')";
mysql_query($query) or die(mysql_error());

Raju_Gautam · January 11, 2012, 3:48pm

Are the CSV header field/column names same as the fields of database table?

Raju_Gautam · January 11, 2012, 4:02pm

If the CSV header are same as the table fields then the following should work:


$row = 1;
$headers = array();
$errors = array();
if (($handle = fopen("products.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        if($row == 1){
            $headers = $data;
        }
        else{
            $cols = array();
            foreach($data as $i=&gt;$d){
                $cols[] = "`" . $headers[$i] . "`='$d'";
            }
            $cols[] = "`Type`='2'";
            $sql = "INSERT INTO Used_Stock SET " . implode(",", $cols);
            if(!mysql_query($query)){
                // store field id if query failed to know what row was not added
                $errors[] = $data[0];
            }
        }
        $row++;
    }
    fclose($handle);
}
if(count($errors)){
    echo "Following Field_ID were not inserted in the database: &lt;br /&gt;";
    echo implode(",", $errors);
}
else{
    echo "Import Successful";
}

sketchgal · January 11, 2012, 4:13pm

The CSV field headers are not exactly the same. Does that make a big difference?

Raju_Gautam · January 11, 2012, 4:41pm

Yes it makes difference to work with my code above. Because I have used the header column fields and one more added field ‘Type’ at the end assuming that your fields are same in the table. If it is possible to change the database table as per the column header of the CSV then the above code will work quite good.

sketchgal · January 11, 2012, 5:06pm

Your code looks brilliant but I can’t change the structure of the database. Is there a way I can change the code above so I can drop in the code I posted in the first post here? Thanks for being so patient with me on this. Appreciate the help.

sketchgal · January 23, 2012, 9:39am

Can anyone please help me with this?
I’m pulling my hair out with this now.
I have the following code which displays a table correctly of all the data in the csv file. All I need to work out is how to get this script to read this into a database.
Even if it has to have the same column headers as the csv file I can work with that but just now I can’t get this into a table at all.

I tried the code above but it doesn’t put anything into my database it just returns a list of all the rows it failed on which is the whole file.

Any help would be greatly appreciated with this, I’m struggling.


&lt;?php

function parse_csv($file,$comma=',',$quote='"',$newline="\
") {

    $db_quote = $quote . $quote;

    // Clean up file
    $file = trim($file);
    $file = str_replace("\\r\
",$newline,$file);

    $file = str_replace($db_quote,'&quot;',$file); // replace double quotes with &quot; HTML entities
    $file = str_replace(',&quot;,',',,',$file); // handle ,"", empty cells correctly

    $file .= $comma; // Put a comma on the end, so we parse last cell


    $inquotes = false;
    $start_point = 0;
    $row = 0;

    for($i=0; $i&lt;strlen($file); $i++) {

        $char = $file[$i];
        if ($char == $quote) {
            if ($inquotes) {
                $inquotes = false;
                }
            else {
                $inquotes = true;
                }
            }

        if (($char == $comma or $char == $newline) and !$inquotes) {
            $cell = substr($file,$start_point,$i-$start_point);
            $cell = str_replace($quote,'',$cell); // Remove delimiter quotes
            $cell = str_replace('&quot;',$quote,$cell); // Add in data quotes
            $data[$row][] = $cell;
            $start_point = $i + 1;
            if ($char == $newline) {
                $row ++;
                }
            }
        }
    return $data;
    }
$myFile = "products.csv";
$fh = fopen($myFile, 'r');
$file = fgets($fh);


echo '&lt;table width="100%" border="1"&gt;';
$row = 1;
if (($handle = fopen("products.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        echo "&lt;tr&gt;\
";
        foreach($data as $d)
		
		//$query = "INSERT INTO Stock_Test (Feed_Id, Vehicle_Id, Full_Registration, Colour, Fuel_Type, Year, Mileage, Body_Type, Doors, Make, Model, Variant, Engine_Size, Price, Transmission, Picture_Refs, Service_History, Previous_Owner, Category, Four_Wheel_Drive, Options, Comments, New, Used, Site, Origin, V5, `Condition`, ExDemo, Franchise_Approved, Trade_Price, Trade_Price_Extra, Service_History_Text, Cap_Id,Type) VALUES ('".$d[0]."', '".$d[1]."', '".$d[2]."' , '".$d[3]."', '".$d[4]."', '".$d[5]."', '".$d[6]."', '".$d[7]."', '".$d[8]."', '".$d[9]."', '".$d[10]."', '".$d[11]."', '".$d[12]."', '".$d[13]."', '".$d[14]."', '".$d[15]."', '".$d[16]."', '".$d[17]."', '".$d[18]."', '".$d[19]."', '".$d[20]."', '".$d[21]."', '".$d[22]."', '".$d[23]."', '".$d[24]."', '".$d[25]."', '".$d[26]."', '".$d[27]."', '".$d[28]."', '".$d[29]."', '".$d[30]."', '".$d[31]."', '".$d[32]."', '".$d[33]."','2')";
		 //mysql_query($query) or die(mysql_error());
            echo "&lt;td&gt;" . (empty($d) ? '&nbsp;' : $d) . "&lt;/td&gt;\
";
        echo "&lt;/tr&gt;\
";
        $row++;
    }
    fclose($handle);
}
echo '&lt;/table&gt;';

?&gt;

Raju_Gautam · January 23, 2012, 4:36pm

I created the following table structure:


CREATE TABLE `used_stock` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `Feed_Id` varchar(255) DEFAULT NULL,
  `Vehicle_ID` varchar(255) DEFAULT NULL,
  `FullRegistration` varchar(255) DEFAULT NULL,
  `Colour` varchar(255) DEFAULT NULL,
  `FuelType` varchar(255) DEFAULT NULL,
  `Year` varchar(255) DEFAULT NULL,
  `Mileage` varchar(255) DEFAULT NULL,
  `Bodytype` varchar(255) DEFAULT NULL,
  `Doors` varchar(255) DEFAULT NULL,
  `Make` varchar(255) DEFAULT NULL,
  `Model` varchar(255) DEFAULT NULL,
  `Variant` varchar(255) DEFAULT NULL,
  `EngineSize` varchar(255) DEFAULT NULL,
  `Price` varchar(255) DEFAULT NULL,
  `Transmission` varchar(255) DEFAULT NULL,
  `PictureRefs` text,
  `ServiceHistory` varchar(255) DEFAULT NULL,
  `PreviousOwners` varchar(255) DEFAULT NULL,
  `Description` text,
  `FourWheelDrive` varchar(255) DEFAULT NULL,
  `Options` text,
  `Comments` text,
  `New` varchar(255) DEFAULT NULL,
  `Used` varchar(255) DEFAULT NULL,
  `Site` varchar(255) DEFAULT NULL,
  `Origin` varchar(255) DEFAULT NULL,
  `v5` varchar(255) DEFAULT NULL,
  `Condition` varchar(255) DEFAULT NULL,
  `ExDemo` varchar(255) DEFAULT NULL,
  `FranchiseApproved` varchar(255) DEFAULT NULL,
  `TradePrice` varchar(255) DEFAULT NULL,
  `TradePriceExtra` varchar(255) DEFAULT NULL,
  `ServiceHistoryText` varchar(255) DEFAULT NULL,
  `Cap_ID` varchar(255) DEFAULT NULL,
  `Type` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

And ran the following code (little modified than previous one) because previous one was not tested with the database insertion.


$row = 1;
$headers = array();
$errors = array();
if (($handle = fopen("products.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        if($row == 1){
            $headers = $data;
        }
        else{
            $cols = array();
			// insert records only at least the field Feed_Id exists for the row
			if(!empty($data[0])){
				foreach($data as $i=&gt;$d){
					$cols[] = "`" . $headers[$i] . "`='" . mysql_real_escape_string($d) . "'";
				}
				$cols[] = "`Type`='2'";
				$sql = "INSERT INTO Used_Stock SET " . implode(",", $cols);
				if(!mysql_query($sql)){
					// store field id if query failed to know what row was not added
					$errors[] = array('data'=&gt;$data[0], 'sql'=&gt;$sql, 'error'=&gt;mysql_error());
				}
			}
        }
        $row++;
    }
    fclose($handle);
}
if(count($errors)){
    echo "Following Field_ID were not inserted in the database: &lt;br /&gt;";
    //echo implode(",", $errors);
	echo "&lt;pre";print_r($errors);die();
}
else{
    echo "Import Successful";
}

It works quite fine. I have modified and added few functions there please check them correctly how they work.

sketchgal · January 23, 2012, 4:56pm

You are an absolute genius!! Thankyou so much for your time and help with this.
Had to change one of the column names but it now reads the file into the database correctly.
Now all I need to do is set up a script which will copy the data I need into the main table.

Really appreciate the help.

Thanks Again!!

Hatrix · January 27, 2012, 12:01am

I need to write a script to do the following.

read data from csv files in a bunch of directories (the list of directories will grow in time)
the idea is that these csv files will be updated by car dealers and then uploaded to their respective directories thru ftp (I’m good with this bit as I will set the dir’s and access)
problem comes in that at the dealer side the content of the files will keep changing. (some car info will remain / some will be deleted / and new ones will be added), this goes for all the dealers.
With every csv file they will upload images relating to the data in the csv files, so I will need to link this as well somehow.
the idea is to run this file with cron.php at set intervals to update the database (will ask how to do this when I get there)

What I’ve managed so far is to loop through the lot with the glob function, and adding the files, but my problem is updating the database. (cars removed from the csv files must not be deleted just set to sold, so we can keep that data for statistics)

Hope someone can point me in the right direction.

This is the code i’ve written so far: (Also added the temp database and some csv files)
PS: there are no headings in the csv files.

<?php
$username ="root";
$password = "myPasword";
$host = "localhost";
$table = "csv_table";
$conn = new mysqli("$host", "$username", "$password");

// echo "Connected to localhost" . "<br />";

mysql_select_db("csvdb") or die(mysql_error());
// echo "Connected to Database";

?>


<?php

// Set variable for csv file path 
$dir = "dealer_upload/*/*.csv"; 

// Open a known directory, and proceed to read its contents 
foreach(glob($dir) as $file) 
{ 
//echo "PATH AND FILENAME: " . $file . "<br /><br />";

// Create the array

$fileTemp = $file;
$fp = fopen($fileTemp,'r');
$datas = array();
while (($data = fgetcsv($fp)) !== FALSE)
{
$stockNumber = trim($data[0]);
$make = trim($data[1]);
$model = trim($data[2]);
$derivative = trim($data[3]);
$series = trim($data[4]);
$reg = trim($data[5]);
$vin = trim($data[6]);
$driveAwayPrice = trim($data[7]);
$priceExcluding = trim($data[8]);
$specialPrice = trim($data[9]);
$year = trim($data[10]);
$kilometres = trim($data[11]);
$body = trim($data[12]);
$colour = trim($data[13]);
$engine = trim($data[14]);
$transmission = trim($data[15]);
$fuel = trim($data[16]);
$options = trim($data[17]);
$sellingPoints = trim($data[18]);
$nvic = trim($data[19]);
$redBook = trim($data[20]);

// Insert Data
mysql_query ("INSERT INTO $table (id_dealer, stockNumber, make, model, derivative, series, reg, vin, driveAwayPrice, priceExcluding, specialPrice, year, kilometres, body, colour, engine, transmission, fuel, options, sellingPoints, nvic, redBook)
VALUES ('$file', '$stockNumber', '$make', '$model', '$derivative', '$series', '$reg', '$vin', '$driveAwayPrice', '$priceExcluding' ,'$specialPrice' , '$year' , '$kilometres' , '$body', '$colour', '$engine', '$transmission', '$fuel', '$options', '$sellingPoints', '$nvic', '$redBook')
")
or die (mysql_error());


} 

} 

?>

Topic		Replies	Views
Importing CSV files to MYSQL using PHP PHP	9	2309	October 7, 2010
Import/Modify CSV PHP	5	2396	July 11, 2010
Reading .txt file into mysql stopped working! Please help PHP	10	732	October 26, 2011
CSV upload poblems PHP	1	400	April 7, 2010
Reading CSV to database with condition PHP	3	246	July 30, 2011

Errors reading .csv file data into mysql database! Plz help!

Related topics