How to Bulk Insert from XLSX file extension?

问题:

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.

回答1:

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

回答2:

You need to use OPENROWSET

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

回答3:

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.

回答4:

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.