BULK INSERT a xlsx file

Question

I have been struggling to import my .xlsx file for a while now and cant seem to figure out what is happening. I follow all the steps I find in other SO posts, and other tutorials.

I have a simple .xlsx file for now:

This is the code i run to grab the file and put it into a temp table for now:

IF OBJECT_ID('tempdb..#Compare') IS NOT NULL
    DROP TABLE #Compare;

CREATE TABLE #Compare
(
    Name VARCHAR(MAX)
    ,Lastname VARCHAR(MAX)
);


BULK INSERT #Compare
        FROM 'C:\Users\Michael\Documents\Taquanta\TAQKANBAN - 44 - 3pm Compare\compare.xlsx'
            WITH
    (
                FIELDTERMINATOR = '\t',
                ROWTERMINATOR = '\n'
    )
GO

SELECT * FROM #Compare c

And the results returned are not what i expected :(

The results:

Please could anyone let me know where I am going wrong?

If this is one time activity, you could use import/export wizard in SSMS. — Nikhil Vartak, Sep 30 '15 at 10:47
Unfortunately - i'm trying to build an automated insert and compare job. So I need to figure this out :) — Fizor, Sep 30 '15 at 10:48
Perhaps you need to go this way. http://stackoverflow.com/questions/13124680/how-to-bulk-insert-from-xlsx-file-extension — Nikhil Vartak, Sep 30 '15 at 11:00
@vnikhil - thanks for your help, but I just read that this is not even possible - i will have to try get the file in another format from the client! Thanks dude. — Fizor, Sep 30 '15 at 11:14

score 0 · Answer 1 · answered Sep 30 '15 at 11:48

I've done this with PowerShell several times. You could adapt something like the following.

$filename = "C:\Users\Michael\Documents\Taquanta\TAQKANBAN - 44 - 3pm Compare\compare.xlsx";
$ExcelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='$filename';Extended Properties='Excel 12.0 Xml;HDR=YES';"
$ExcelConn = New-Object System.Data.OleDb.OleDbConnection($ExcelConnStr);
$ExcelConn.Open();
$ExcelCommand = New-Object System.Data.OleDb.OleDbCommand("SELECT * FROM [Sheet1`$a5:ae]" ,$ExcelConn);

$adpt = New-Object System.Data.OleDb.OleDbDataAdapter;
$adpt.SelectCommand = $ExcelCommand;

$dt = New-Object System.Data.DataTable;
[void]$adpt.Fill($dt);


$SQLConnStr = "Server=XXXXX;Database=XXXXX;Trusted_Connection=True;";
$SQLBulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($SQLConnStr);
$SQLBulkCopy.DestinationTableName = "dbo.TableName"
$SQLBulkCopy.WriteToServer($dt);
$SQLBulkCopy.Close();

BULK INSERT a xlsx file

1 Answers1