[FIXED] How to add an external variable with BULK INSERT DATA in php?

Issue

I have a large csv file (over 100,000 lines) encoded in UTF-8-BOM that looks like this:

27336;00203-AND1;90-BLACK;9.5;2
27336;00203-ET1;90-BLACK;10;1
27336;00203-ET1;90-BLACK;12;1
...

And a table in my SQL Server database with these columns :

storenumber | stylecode | colour | size | units | timestamp

I use Bulk Insert Data to load the file at once, but I would like to add my $timestamp variable to each line inserted in my table but it doesn't work... How do I do it?

 <?php

include("connexion.php");

ini_set('max_execution_time', 32400);
$timestamp= date("y-m-d H:i");


$csv= "D:/xampp/htdocs/retail_BI/files/BI2_20200720_1344_00076.txt";

                     $query = "BULK INSERT dbo.Y2_Inventory 
                    FROM '$csv' 
                    WITH (
                    FIELDTERMINATOR = ';', 
                    ROWTERMINATOR = '\n',
                    ERRORFILE = 'myfileerror.log'
                     )";

                     $stmt = $conn->query( $query );     
                     if (!$stmt) { echo $conn->error;} 


$query2 = "UPDATE dbo.Y2_Inventory SET timestamp = ? WHERE timestamp IS NULL";

                     $stmt = $conn->query( $query2 );     
                    
            
        echo "good";        

?>

Solution

You need to consider the following:

• Always try to use parameters in your statement (of course if possible) or carefully sanitize the input data. In this specific case you may try to check if the input file exists and after that inject filename in your statement.
• The input data doesn't match the table definition, so you may try to import the data in a temporary table.
• Pass datetime values as text using an unambiguous format (e.g. 2020-07-25T12:00:00).

The following example is a possible solution to your problem:

Table:

CREATE TABLE Y2_Inventory (
    storenumber int,
    stylecode nvarchar(50),
    colour nvarchar(50),
    size numeric(10, 1), 
    units int,
    [timestamp] datetime
)

PHP script:

<?php
//
include("connexion.php");
ini_set('max_execution_time', 32400);

// CSV file
$csv = "D:/xampp/htdocs/retail_BI/files/BI2_20200720_1344_00076.txt";  
$err = "D:/xampp/htdocs/retail_BI/files/BI2_20200720_1344_00076.err";
if (!file_exists($csv)) {
    die("CSV file not exists.");    
}
    
// Time stamp
$timestamp = date("Y-m-d\TH:i:s");

// INSERT Data
try {
    $query = "
        SET NOCOUNT ON;
        
        SELECT storenumber, stylecode, colour, size, units
        INTO #t
        FROM Y2_Inventory
        WHERE 1 = 0;
        
        BULK INSERT #t 
        FROM '$csv' 
        WITH (
            ERRORFILE = '$err',
            FIELDTERMINATOR = ';', 
            ROWTERMINATOR = '\n'
        );
        
        INSERT INTO Y2_Inventory (storenumber, stylecode, colour, size, units, [timestamp])
        SELECT storenumber, stylecode, colour, size, units, ?
        FROM #t;
    
        DROP TABLE #t;
    ";
    $stmt = $conn->prepare($query);     
    $stmt->bindParam(1, $timestamp, PDO::PARAM_STR);
    $stmt->execute();
    echo "OK";        
} catch (PDOException $e) {
    die ("Error executing query. ".$e->getMessage());
}
?>

Answered By - Zhorov