PHP - SQL Server Bulk Insert

Ask about general coding issues or problems here.

Moderators: egami, macek, gesf

Post Reply
gabeconc
New php-forum User
New php-forum User
Posts: 4
Joined: Sun Sep 10, 2017 5:51 am

Mon Sep 11, 2017 1:28 pm

Hi, I need to import a CSV (or Excel) file into a SQL Server table.

For testing purposes, I have following SQL Code code:

>>>>>>>>>>>> SQL CODE >>>>>>>>>>>>>>>>>>>>
BULK INSERT test_TABLE
FROM '\\SERVER1\public\Purchasing\Uploads\TestFile.csv'
WITH
(
FIRSTROW=2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS
)
<<<<<<<<<<<< END SQL CODE <<<<<<<<<<<<<<<<<<<<

If I run it in SQL Server Query Analyzer, It works fine…. It imports the rows from TestFile.csv into test_TABLE without any problem.

MY CHALLENGE IS THAT I NEED TO EXECUTE THIS CODE FROM PHP.

By now, I have tried by putting this Bulk Insert code into a SQL Server Stored Procedure as follows:

>>>>>>>>>>>>> PROCEDURE CODE >>>>>>>>>>>>>>>>>>>
CREATE PROCEDURE PROCTEST AS
BULK INSERT test_TABLE
FROM '\\SERVER1\public\Purchasing\Uploads\TestFile.csv'
WITH
(
FIRSTROW=2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS
)
<<<<<<<<<<<<< END PROCEDURE CODE <<<<<<<<<<<<<<<<<<<

If I run this Stored Procedure from SQL Server Query Analyzer it runs good.

Following is the command I’m using:

>>>>>>>>>> SQL CODE >>>>>>>>>>>>>>>>>>>>>>

EXEC SERVER1.AddOns.dbo.PROCTEST

<<<<<<<<<< END SQL CODE <<<<<<<<<<<<<<<<<<

If I try to run the same command from PHP, I’m NOT GETTING IT TO EXECUTE THE STORED PROCEDURE.
I have tried this in two different ways (in order to make this post simpler, I’m just putting the PHP code that executes the stored procedure):

Attempt # 1

>>>>>>>>>>> PHP CODE >>>>>>>>>>>>>>>>>>>>>

$sqlRunProc = " SERVER1.AddOns.dbo.PROCTEST";
$statement = $conn->query($sqlRunProc);

<<<<<<<<<<<< END PHP CODE <<<<<<<<<<<<<<<<

Attempt #2

>>>>>>>>>>>>> PHP CODE >>>>>>>>>>>>>>>>>>>

$IntegrateData = " SERVER1.AddOns.dbo.PROCTEST";
$statement = $conn->prepare($IntegrateData);
$statement->execute();

<<<<<<<<<<<<< END PHP CODE <<<<<<<<<<<<<<<

I’m not being able to insert the CSV file into the test_TABLE by running it from PHP.

Due to my inexperience, I’m not sure if the SYNTAX of the PATH FOR THE CSV FILE is properly expressed in the store procedure, so it can be properly interpreted by PHP.

I want to mention that SERVER1, which is where the CSV test_File and the SQL Server database reside, is a DIFFERENT SERVER than where the whole PHP Environment is running (not sure if that affects in any way).

I WILL APPRECIATE ANY HELP/GUIDANCE ANYBODY CAN PROVIDE (OR PERHAPS A SIMPLER WAY TO DO THIS IMPORT) THANK YOU!

User avatar
hyper
php-forum Fan User
php-forum Fan User
Posts: 502
Joined: Mon Feb 22, 2016 5:52 pm

Tue Sep 12, 2017 11:48 am

Use CALL for stored procedures.

This seems to be making life difficult for yourself though? in my mind having to continuously translate and save data from one form to another should only be done once (when upgrading) otherwise there will be newer and newer problems as you go.

Luckily I've been windoze free for a few years and never felt better, my disturbed memories of those times lead me to think that Ekzel can export into a database directly (after a fashion)?

gabeconc
New php-forum User
New php-forum User
Posts: 4
Joined: Sun Sep 10, 2017 5:51 am

Fri Sep 15, 2017 6:36 am

Thank you for the suggestion Hyper.
I ended up trying some other way, by using PHP FGETCSV function... seems might be a viable solution for what I need.
Still will research about how to use the CALL function you suggested.

Thank you again!

Gabe.

User avatar
hyper
php-forum Fan User
php-forum Fan User
Posts: 502
Joined: Mon Feb 22, 2016 5:52 pm

Fri Sep 15, 2017 8:14 am

You're welcome :)

Using fgetscsv or something else would be a better solution than using a stored procedure in my opinion.

I suggested CALL since you had been using a stored procedure.

Is this to replace using ekzel, or to use with it? I'd be more inclined to only use one thing.

gabeconc
New php-forum User
New php-forum User
Posts: 4
Joined: Sun Sep 10, 2017 5:51 am

Sun Sep 17, 2017 6:37 am

Hyper, now I'm using the FOPEN and FGETCSV to import this data and looks is working fine, except whenever I have null/blank values on any of the columns/rows of my csv file.
Do you happen to know how can I import all rows even if they have blank values in any of the columns?

Any help will be truly appreciated.

Below the code I'm using:
/******************************************************************/
if (($handle = fopen("uploads/TestFile.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
for ($c=0; $c < $num; $c++) {
$col[$c] = $data[$c];
}

$col1 = $col[0];
$col2 = $col[1];
$col3 = $col[2];
$IntegrateData = "INSERT INTO TestTable
VALUES (". $col1.",".$col2.",".$col3.")";
$statement = $conn->prepare($IntegrateData);
$statement->execute();
}
fclose($handle);
}

Post Reply