PHP - SQL Server Bulk Insert


#1

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. THANK YOU!