Inserting bulk CSV files into the one SQL table [SQL Server 2008]

Question

I have multiple folders (six or so) with multiple .CSV files in them. The CSV files are all of the same format:

Heading1,Heading2,Heading3
1,Monday,2.45
2,Monday,3.765...

Each .CSV has the same heading names [same data source for different months]. What is the best way to import these CSVs into SQL Server 2008? The server does not have xpShell configured [for security reasons which I cannot modify], so any method which uses that (which I originally tried), will not work.

EDIT

The CSV files are a maximum of 2mb in size and do not contain any commas (other than those required for delimiters).

Any ideas?

How big are the CSV files? You could hypothetically use Excel if you needed to. Note that `BULK INSERT` does not provide a true CSV parser: it does not support escaped quotes, or even commas in quoted values ( http://stackoverflow.com/questions/12902110/bulk-insert-correctly-quoted-csv-file-in-sql-server ) for example. — Dai, Oct 21 '16 at 03:08
The largest CSV file would be around 2mb in size. The data does not contain any commas or escaped quotes. — dandev91, Oct 21 '16 at 03:12
Basically three options - first see if BULK INSERT works for your files as suggested. If not you may need to use SSIS or you could use an external process (like a powershell script) to push the data in. — Nick.Mc, Oct 21 '16 at 03:48

score 0 · Answer 1 · answered Oct 22 '16 at 14:11

F.e. you got CSV file names sample.csv on D:\ drive, with this inside:

Heading1,Heading2,Heading3
1,Monday,2.45
2,Monday,3.765

Then you can use this query:

DECLARE @str nvarchar(max),
        @x xml,
        @head xml,
        @sql nvarchar(max),
        @params nvarchar(max) = '@x xml'

SELECT @str = BulkColumn 
FROM OPENROWSET (BULK N'D:\sample.csv', SINGLE_CLOB) AS a

SELECT @head = CAST('<row><s>'+REPLACE(SUBSTRING(@str,1,CHARINDEX(CHAR(13)+CHAR(10),@str)-1),',','</s><s>')+'</s></row>' as xml)

SELECT @x  = CAST('<row><s>'+REPLACE(REPLACE(SUBSTRING(@str,CHARINDEX(CHAR(10),@str)+1,LEN(@str)),CHAR(13)+CHAR(10),'</s></row><row><s>'),',','</s><s>')+'</s></row>' as xml)

SELECT @sql = N'
SELECT  t.c.value(''s[1]'',''int'') '+QUOTENAME(t.c.value('s[1]','nvarchar(max)'))+',
        t.c.value(''s[2]'',''nvarchar(max)'') '+QUOTENAME(t.c.value('s[2]','nvarchar(max)'))+',
        t.c.value(''s[3]'',''decimal(15,7)'') '+QUOTENAME(t.c.value('s[3]','nvarchar(max)'))+'
FROM @x.nodes(''/row'') as t(c)'
FROM @head.nodes('/row') as t(c)

To get output like:

Heading1    Heading2    Heading3
1           Monday      2.4500000
2           Monday      3.7650000

At first we take data as SINGLE_CLOB with the help of OPEROWSET.

Then we put all in @str variable. The part from beginning to first \r\n we put in @head, the other part in @x with conversion to XML. Structure:

<row>
  <s>Heading1</s>
  <s>Heading2</s>
  <s>Heading3</s>
</row>

<row>
  <s>1</s>
  <s>Monday</s>
  <s>2.45</s>
</row>
<row>
  <s>2</s>
  <s>Monday</s>
  <s>3.765</s>
</row>

After that we build dynamic query like:

SELECT  t.c.value('s[1]','int') [Heading1],
        t.c.value('s[2]','nvarchar(max)') [Heading2],
        t.c.value('s[3]','decimal(15,7)') [Heading3]
FROM @x.nodes('/row') as t(c)

And execute it. Variable @x is passing as parameter.

Hope this helps you.

score 0 · Answer 2 · answered Oct 23 '16 at 23:30

I ended up solving my problem using a non-SQL answer. Thank you everyone who helped contribute. I apologise for going with a completely off-field answer using PHP. Here is what I created to solve this problem:

<?php
    //////////////////////////////////////////////////////////////////////////////////////////////////
    //                                                                                              //
    //      Date:           21/10/2016.                                                             //
    //      Description:    Insert CSV rows into pre-created SQL table with same column structure.  //
    //      Notes:          -  PHP script needs server to execute.                                  //
    //                      -  Can run line by line ('INSERT') or bulk ('BULK INSERT').             //
    //                          - 'Bulk Insert' needs bulk insert user permissions.                 //
    //                                                                                              //
    //      Currently only works under the following file structure:                                //
    //          | ROOT FOLDER                                                                       //
    //                      |   FOLDER 1                                                            //
    //                              |   CSV 1                                                       //
    //                              |   CSV 2...                                                    //
    //                      |   FOLDER 2                                                            //
    //                              |   CSV 1                                                       //
    //                              |   CSV 2...                                                    //
    //                      |   FOLDER 3...                                                         //
    //                              |   CSV 1                                                       //
    //                              |   CSV 2...                                                    //
    //                                                                                              //
    //////////////////////////////////////////////////////////////////////////////////////////////////

    //Error log - must have folder pre-created to work
    ini_set("error_log", "phplog/bulkinsertCSV.php.log");

    //Set the name of the root directory here (Where the folder's of CSVs are)
    $rootPath = '\\\networkserver\folder\rootfolderwithCSVs';

    //Get an array with the folder names located at the root directory location
    //  The '0' is alphabetical ascending, '1' is descending.
    $rootArray = scandir($rootPath, 0);

    //Set Database Connection Details
    $myServer = "SERVER";
    $myUser = "USER";
    $myPass = "PASSWORD";
    $myDB = "DATABASE";

    //Create connection to the database
    $connection = odbc_connect("Driver={SQL Server};Server=$myServer;Database=$myDB;", $myUser, $myPass) or die("Couldn't connect to SQL Server on $myServer");

    //Extend Database Connection timeout
    set_time_limit(10000);

    //Set to true for bulk insert, set to false for line by line insert
    //  [If set to TRUE] - MUST HAVE BULK INSERT PERMISSIONS TO WORK
    $bulkinsert = true;

    //For loop that goes through the folders and finds CSV files
    loopThroughAllCSVs($rootArray, $rootPath);

    //Once procedure finishes, close the connection
    odbc_close($connection);

    function loopThroughAllCSVs($folderArray, $root){
        $fileFormat = '.csv';
        for($x = 2; $x < sizeof($folderArray); $x++){
            $eachFileinFolder = scandir($root."\\".$folderArray[$x]);
            for($y = 0; $y < sizeof($eachFileinFolder); $y++){
                $fullCSV_path = $root."\\".$folderArray[$x]."\\".$eachFileinFolder[$y];
                if(substr_compare($fullCSV_path, $fileFormat, strlen($fullCSV_path)-strlen($fileFormat), strlen($fileFormat)) === 0){
                    parseCSV($fullCSV_path);
                }
            }
        }
    }

    function parseCSV($path){
        print_r($path);
        print("<br>");
        if($GLOBALS['bulkinsert'] === false){
            $csv = array_map('str_getcsv', file($path));
            array_shift($csv);                              //Remove Headers

            foreach ($csv as $line){
                writeLinetoDB($line);
            }
        }
        else{
            bulkInserttoDB($path);
        }
    }

    function writeLinetoDB($line){
        $tablename = "[DATABASE].[dbo].[TABLE]";
        $insert = "INSERT INTO ".$tablename." (Column1,Column2,Column3,Column4,Column5,Column6,Column7)
                VALUES ('".$line[0]."','".$line[1]."','".$line[2]."','".$line[3]."','".$line[4]."','".$line[5]."','".$line[6]."')";

        $result = odbc_prepare($GLOBALS['connection'], $insert);
        odbc_execute($result)or die(odbc_error($connection));   
    }

    function bulkInserttoDB($csvPath){
        $tablename = "[DATABASE].[dbo].[TABLE]";
        $insert = "BULK
                    INSERT ".$tablename."
                    FROM '".$csvPath."'
                    WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\\n')";

        print_r($insert);
        print_r("<br>");

        $result = odbc_prepare($GLOBALS['connection'], $insert);
        odbc_execute($result)or die(odbc_error($connection));
    }
?>

I ended up using the script above to write to the database line by line... This was going to take hours. I modified to the script to use BULK INSERT which unfortunately we didn't have 'permissions' to use. Once I 'obtained' permissions, the BULK INSERT method worked a charm.

Inserting bulk CSV files into the one SQL table [SQL Server 2008]

2 Answers2