Using fgetcsv to add data into sql server

Question

Using fgetcsv is a brand new function for me today. I'm used to dealing with mysql so sql server is also sort of a beast. Hence why I'm turning to you guys!

I have the below code which I believe does everything I need except for taking the csv, and putting it into a sql table.

Can you guys help with some sample codes that might mitigate this problem?

function connect() {
    if (!function_exists('sqlsrv_num_rows')) { // Insure sqlsrv_1.1 is loaded.
        die ('sqlsrv_1.1 is not available');
    }

    /*
    * Log all Errors.
    */
    sqlsrv_configure("WarningsReturnAsErrors", TRUE);        // BE SURE TO NOT ERROR ON A WARNING
    sqlsrv_configure("LogSubsystems", SQLSRV_LOG_SYSTEM_ALL);
    sqlsrv_configure("LogSeverity", SQLSRV_LOG_SEVERITY_ALL);

    $conn = sqlsrv_connect('instance', array
    (
    'UID' => 'userName',
    'PWD' => 'password',
    'Database' => 'database',
    'CharacterSet' => 'UTF-8',
    'MultipleActiveResultSets' => true,
    'ConnectionPooling' => true,
    'ReturnDatesAsStrings' => true,
    ));

    if ($conn === FALSE) {
        get_last_error();
    }

    return $conn;
}

function query($conn, $query) {
    $result = sqlsrv_query($conn, $query);
    if ($result === FALSE) {
        get_last_error();
    }
    return $result;
}

function execute ( $stmt ) {
    $result = sqlsrv_execute($stmt);
    if ($result === FALSE) {
        get_last_error();
    }
    return $result;
}

$conn = connect();


if (($handle = fopen(getcwd().'C:\path\TASKS.csv', "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) { 
        $db->execute("INSERT INTO Database..table (taskID, name, active) VALUES ('{$data[1]}','{$data[2]}','{$data[3]}')");
        set_time_limit(60); // reset timer on loop
    } 
}

With the above code I am currently getting the following error

failed to open stream: Invalid argument in C:\path\insertDataFGet.php on line 51

Line 51 is the $db->execute line

Sample of csv

1,taskName1,1
2,taskName2,0
3,taskName3,0

What's the problem you're having? Show the code you attempted, explain how it's not working, and someone will help you fix it. — Barmar
– Barmar, Commented Apr 16, 2013 at 8:52

Dave · Accepted Answer · 2013-04-16 09:20:02Z

1

Here's how I do it on one of my little import scripts. There's probably better ways of doing it but this works.

if (($handle = fopen(getcwd()."/tmp/updatedpricing.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {  // loop through each line of CSV. Returns array of that line each time so we can hard reference it if we want.
    $db->execute("UPDATE table SET col1='{$data[1]}',col2='{$data[2]}',col3='{$data[3]}' WHERE something=something",ENABLE_DEBUG);
    set_time_limit(60); // reset timer on loop
    } 
}
fclose($handle);

This code was just an example There's 2 issues in it with implementing directly into your code first is getcwd() in your case you don't need it its used because I use virtual paths and "ln -s" created on the fly so my working directory isn't always the same and the file isn't always in the same location. Since you know the exact path you can just put the path in directly

if (($handle = fopen('C:\path\TASKS.csv', "r")) !== FALSE) {

The second issue is you cannot copy and paste my sql statement as its dependent on my custom mysql db access class. So you'll have to change the line starting with $db->execute to use your available mysql functions. If you're doing a basic insert I guess you can build your query separately and then just pass it through your query() or execute() functions either one should work. Don't forget to correctly specify the table name your writing to your example code is wrong.

Third issue is you need to make sure you escape your input vars properly $data[] etc and also make sure you match the columns from the CSV to your insert columns don't forget $data will count from 0 (zero)

edited Apr 16, 2013 at 9:20

answered Apr 16, 2013 at 8:52

Dave

3,2882 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mhopkins321 Over a year ago

what does the 10000 do in fgetcsv($handle, 10000, ","). I read the manual and I can tell it has something to do with length, but you're not the first person to have 10k there

Dave Over a year ago

Tells it to read in a line of no more than 10000 bytes/kbytes not sure which off top of head I know the csv in my case will never have more than 40 characters so I can get away with reading in a fixed length line. If its a variable length input line eg a product description for example you may want to use a little line length calc to give you the actual length of the line you're attempting to read and pass that real length to fgetcsv() rather than a fixed length as I do

mhopkins321 Over a year ago

I am getting an error. I have made an edit to my original. Would you be able to help me through this one?

mhopkins321 Over a year ago

You're a life saver. Thanks for the help. I REALLY appreciate it. I am 90% sure the functions I have work for sql server. I edited out the table names and username and stuff. They are accurate in my script. Can I put inside the while loop $params = array({$data[1]},{$data[2]},{$data[3]}); to get the data from each row of the csv?

Dave Over a year ago

yes each row of the csv when read in the loop will present as $data which is an array so you just reference the column with [0]...[x] etc. There would be no need to redefine $data as $params as you can simply pass $data back to your query function and then just ignore the columns you don't want. Remember $data is an array. You can also use count($data) to get a column count and then do some smart validation/whatever inside your primary line loop too. Its entirely upto you and your needs. My example is only basic.

Collectives™ on Stack Overflow

Using fgetcsv to add data into sql server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related