Search the Community

I'm currently working on building a db of Nastar ski race results. I receive the raw data from a ski mountain in the form of a csv (comma delimited) flat file. The code below reads in the csv and puts it into an array. Then the data within the array is inserted into a table. The problem is that the csv files that I receive are inconsistent in that some of the blank columns appear as ,"", ( a pair of double quotes delimited by commas) and some appear as ,, (just a pair of commas with no quotes between them). The latter gives me a mySql syntax error. I know it is the lack of quotation pairs that is the problem because if I do a search and replace to insert the quote pairs, everything runs okay. Is there something I can do to make my code accept either ,, OR ,"", as a blank to be inserted in the table. I'm not sure that I can lay the responsibility on the client to guarantee a consistent file structure. thanks----- $arrResult = array(); $arrLines = file('Mon wk 1.csv'); $kountLines = 0; foreach($arrLines as $line) { ++$kountLines; echo $kountLines."<br />"; if ($kountLines >1) {// don't include the column names which are in the 1st line $arrResult[] = explode( ',', $line); $q = "INSERT INTO rawRaceResults (bib,nastarID, lastName, firstName, initial, address, city, state, zip, country, birthdate, raceAge, sex, discipline, ffSkiTeam, skiClubTeam, skiResortTeam, email, teamPoints, misc1, misc2, misc3, highSchoolPoints, ageGroup,course1, result1, handicap1, medal1, course2, result2, handicap2, medal2) VALUES ({$arrResult[0][0]},{$arrResult[0][1]}, {$arrResult[0][2]}, {$arrResult[0][3]},{$arrResult[0][4]},{$arrResult[0][5]}, {$arrResult[0][6]}, {$arrResult[0][7]}, {$arrResult[0][8]}, {$arrResult[0][9]}, {$arrResult[0][10]}, {$arrResult[0][11]}, {$arrResult[0][12]}, {$arrResult[0][13]}, {$arrResult[0][14]}, {$arrResult[0][15]}, {$arrResult[0][16]}, {$arrResult[0][17]}, {$arrResult[0][18]}, {$arrResult[0][19]}, {$arrResult[0][20]}, {$arrResult[0][21]}, {$arrResult[0][22]}, {$arrResult[0][23]}, {$arrResult[0][24]}, {$arrResult[0][25]}, {$arrResult[0][26]}, {$arrResult[0][27]}, {$arrResult[0][28]}, {$arrResult[0][29]}, {$arrResult[0][30]}, {$arrResult[0][31]})"; $r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc)); if (mysqli_affected_rows($dbc) == 1) { // If it ran OK. echo "Line ".$kountLines.". "; echo "<br /><br />"; } } //if ($kountLines >1) loop $arrResult = array(); } //foreach loop