Populating A Table From A Csv File

[chop]

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

[margaux]

What about using str_replace on $line before you explode it? e.g.

if ($kountLines >1) {// don't include the column names which are in the 1st line
$newline = str_replace('""','',$line);
$arrResult[] = explode( ',', $newline);

[HartleySan]

I could be wrong, but I think the reason your queries aren't working when you don't have the double quotes is because SQL queries require all strings to be surrounded by quotes.

To this end, I might propose a more consistent solution, which is to remove all the string-marking quotes from the CSV file, and then properly placing quotes around strings in your query.

 

I'm not going to write out your entire query with the proper quotes though. Instead, to demonstrate, let's look at the following example:

 

$name = '"Bill"';

$name = str_replace('"', '', $name); // Delete all sets of double quotes since the value stored in $name is already a string (and doesn't need an extra set of quotes).

$q = "SELECT * FROM users WHERE name = '$name'"; // Note that I placed the $name variable within quotes in the query. This is a more proper way to do it. Note that in your code, there are no quotes around the strings being inserted into the DB.

 

Anyway, I think margaux has the right idea in that deleting the superfulous quotes is the correct thing to do (which is what I just demonstrated above as well).

To provide a slightly more complete answer, something like the following should get you what you want:

 

$data = file_get_contents('Mon wk 1.csv'); // This will load the entirety of the 'Mon wk 1.csv' file into the $data with newlines intact.

$data = str_replace('""', '', $data); // Remove all unnecessary sets of double quotes.

$lines = explode("\r\n", $data); // Explode the data on newlines. Note that you might need to use "\r" or "\n" instead, depending on how the CSV file is formatted.

foreach ($lines as $one_line) {

$vals = explode(',', $one_line);

// Write $q variable query here. Be sure to put quotes around all strings being inserted into the DB.

// Run the query.

// Check to make sure the query executed properly.

}

// Done!

 

Hope that helps.

[chop]

Yes, thanks to both of you for the time you put into helping me. This is something that can work I think. First, I will check to see if there is some way of avoiding the ,, in the csv file in the first place but this is part of Nastar's software. I believe it is there because it is suppose to contain a handicap, which s a number so doesn't need quotes. If there is no handicap (which happens often), it comes across as just two commas. If they can fix it on their end, ( maybe they could insert a -1 to represent no handicap -which is different than a handicap of 0- ) that would be best but at least I have a model to go on if that is not possible. thanks again

[HartleySan]

chop, I deal with CSV files quite often, and the standard format for the lack of a value is ",," (without the quotes). When you split the file/line on commas, the ",," will create an array element that is an empty string, which is what you want. As such, instead of trying to "fix" the ",,", I would recommend learning how to deal with it (which isn't that hard to do, since that's the intended/default format). The entries in the CSV file that seem wrong to me are the ,"", ones. Those are the ones I'd run a string replace on, if necessary.

[chop]

Thanks. Let me experiment with this for a while. The ski mountain now says they will be changing the format a little for this year (oh, here we go) so I might have ot hold off on it for a while.

thanks