Problems with uploading CSV file to SQL Server with PHP

Solution 1:

There are two major problems with the code posted:

1. CSV data is not being read in correctly, and
2. The $params array supplied to prepare() isn't being updated correctly.

Problem 1: Reading in CSV.

You're using PHP's file() method to read CSV files.

If you're trying to parse CSV files line-by-line and then split lines on commas you're doing it wrong. Have a read through RFC 4180 Common Format and MIME Type for Comma-Separated Values (CSV) Files to see how CSV files are structured and notice that field datas can contain commas, quotes and line break characters. i.e.: You need a character-oriented state machine to parse them.

The correct way to do it is to use a CSV parsing function or library that somebody else has already written and debugged for you, e.g.: fgestcsv().

Consider the following example...

foo1.csv:

col1,col2,col3
alpha,bravo,charlie
"hello,
""cruel""
world!",bravo,charlie

foo1.php:

<?php
if (($handle = fopen("foo1.csv", "r")) !== FALSE) {
    $row = 1;
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        print "Row $row: ";
        var_dump($data);
        $row++;
    }
    fclose($handle);
}
?>

When run this outputs...

% php -f foo1.php
Row 1: array(3) {
  [0]=>
  string(4) "col1"
  [1]=>
  string(4) "col2"
  [2]=>
  string(4) "col3"
}
Row 2: array(3) {
  [0]=>
  string(5) "alpha"
  [1]=>
  string(5) "bravo"
  [2]=>
  string(7) "charlie"
}
Row 3: array(3) {
  [0]=>
  string(19) "hello,
"cruel"
world!"
  [1]=>
  string(5) "bravo"
  [2]=>
  string(7) "charlie"
}

Problem 2: Updating $params correctly

The code posted constructs an array from variables and then updates the variables with CSV data. This won't work, consider the following...

foo2.php:

<?php
$param1 = null;
print "\$param1 = ";
var_dump($param1);

$params = array($param1);
print "\$params = ";
var_dump($params);

$param1 = 42;
print "\$param1 = ";
var_dump($param1);
print "\$params = ";
var_dump($params);

$params[0] = 47;
print "\$params = ";
var_dump($params);
?>

When run this outputs...

% php -f foo2.php
$param1 = NULL
$params = array(1) {
  [0]=>
  NULL
}
$param1 = int(42)
$params = array(1) {
  [0]=>
  NULL
}
$params = array(1) {
  [0]=>
  int(47)
}

Notice how $param1 was updated to 42 successfully but $params[0] was still NULL? $params was only updated when we set a applied via $params[0] directly.

Your CSV reading code (hopefully updated to use fgestcsv()) should be updating the $params[0] through $params[7] array elements directly.