Importing CSV to MySQL database

Question

I'm trying to import a csv to a mysql table in my db but I keep getting this error:

Unknown column '' in 'field list'

INSERT INTO `job_budget_report` (`Job_Num`, `Description`, `EngWeekly`, `EngTTD`, `EngBudget`, `DesignWeekly`, `DesignTTD`, `DesignBudget`, `DetailsWeekly`, `DetailsTTD`, `DetailsBudget`, `EjectionWeekly`, `EjectionTTD`, `EjectionBudget`, `CarbonEDMWeekly`, `CarbonEDMTTD`, `CarbonEDMBudget`, `GdrillBMWeekly`, `GdrillBMTTD`, `GdrillBMBudget`, `CNCWeekly`, `CNCTTD`, `CNCBudget`, `MMQCOtherWeekly`, `MMQCOtherTTD`, `MMQCOtherBudget`, `SpottingWeekly`, `SpottingTTD`, `SpottingBudget`, `SpotPLWeekly`, `SpotPLTTD`, `SpotPLBudget`, `HWWeekly`, `HWTTD`, `HWBudget`, `TotalMach`, `TotalOther`, `TotalOFMach`, ``) VALUES
('1000', 'description text here', '0', '0', '0', NULL, '0', '0', NULL, NULL, '0', NULL, '0', NULL, '0', '0', '0', '0', '0', '0', '0', '0', '0', '20.5', '0', '0', '0', '0', '0', '18.5', '40', '0', '0', '0', '0', '0', '0', '0', '');

Table Structure:

https://i.sstatic.net/OeW8x.jpg

Now I realize that theres extra empty fields in that query but how do I remove them from my csv ?

Here's what my csv looks like when I open it in sublime text:

Job_Num;Description;EngWeekly;EngTTD;EngBudget;DesignWeekly;DesignTTD;DesignBudget;DetailsWeekly;DetailsTTD;DetailsBudget;EjectionWeekly;EjectionTTD;EjectionBudget;CarbonEDMWeekly;CarbonEDMTTD;CarbonEDMBudget;GdrillBMWeekly;GdrillBMTTD;GdrillBMBudget;CNCWeekly;CNCTTD;CNCBudget;MMQCOtherWeekly;MMQCOtherTTD;MMQCOtherBudget;SpottingWeekly;SpottingTTD;SpottingBudget;SpotPLWeekly;SpotPLTTD;SpotPLBudget;HWWeekly;HWTTD;HWBudget;TotalMach;TotalOther;TotalOFMach 5710;GM K2XL Bracket FRT 5/D Belt R;0;0;0;;0;0;;;0;;0;;0;0;0;0;0;0;0;0;0;20.5;0;0;0;0;0;18.5;40;0;0;0;0;0;0;0;0;0;457.56;328.8;;;;;

Sorry I don't know how to structure this nicer, I'll post an image of one of the rows in the excel file before and after I clear formatting as well.

Before: https://i.sstatic.net/3jdRI.jpg

After: https://i.sstatic.net/cjjME.jpg

Is there a more efficient way to do this ?

Answer 1

Use fgetcsv() to read, clean and insert into your table. - http://www.php.net/fgetcsv

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