How to Bulk Insert from XLSX file extension?

Question

Can anyone advise how to bulk insert from .xlsx file?

I tried the below query already:

BULK INSERT #EVB FROM 'C:\Users\summer\Desktop\Sample\premise.xlsx' 
WITH (FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n', FIRSTROW = 2);

SELECT * FROM #EVB

I also tried with FIELDTERMINATOR like "**\t**", "**,**", "**;**", "**|**", but this doesn't work either.

Unfortunately, there is no error message.

xlsx is zipped xml, you cannot insert from it. You're trying to treat it as a csv file, which it almost certainly is not. — Marc B, Oct 29 '12 at 15:32

score 28 · Answer 1 · answered Oct 29 '12 at 15:34

28

you can save the xlsx file as a tab-delimited text file and do

BULK INSERT TableName
        FROM 'C:\SomeDirectory\my table.txt'
            WITH
    (
                FIELDTERMINATOR = '\t',
                ROWTERMINATOR = '\n'
    )
GO

answered Oct 29 '12 at 15:34

wootscootinboogie

8,461
33
112
197

2

This was extremely helpful. The row terminators in the actual txt files that Excel exports are CRLF terminated. I tried RowTerminator = "\r\n" and repeatedly could not get it to import. Using \n solved the problem. I know this is is an old recommendation, but THANK YOU! It's still current. – EricI Mar 14 '20 at 18:32
This is the method I use but the file size is so large, it's very time consuming to open a 150mb xlsx file and save as text. Not to mention, the random bugs. Is there a native way to bulk insert directly from XLSX? – PowerUser Aug 09 '22 at 19:56

score 3 · Answer 2 · edited May 23 '17 at 12:34

3

You need to use OPENROWSET

Check this question: import-excel-spreadsheet-columns-into-sql-server-database

edited May 23 '17 at 12:34

Community

1
1

answered Oct 29 '12 at 15:42

Turque

708
7
18

score 1 · Answer 3 · answered Oct 29 '12 at 15:39

Create a linked server to your document

http://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm

Then use ordinary INSERT or SELECT INTO. If you want to get fancy, you can use ADO.NET's SqlBulkCopy, which takes just about any data source that you can get a DataReader from and is pretty quick on insert, although the reading of the data won't be esp fast.

You could also take the time to transform an excel spreadsheet into a text delimited file or other bcp supported format and then use BCP.

score 0 · Answer 4 · answered Jul 05 '17 at 13:05

It can be done using SQL Server Import and Export Wizard. But if you're familiar with SSIS and don't want to run the SQL Server Import and Export Wizard, create an SSIS package that uses the Excel Source and the SQL Server Destination in the data flow.

How to Bulk Insert from XLSX file extension?

4 Answers4

Linked