How to Bulk Insert from XLSX file extension?
Solution 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
Solution 2
You need to use OPENROWSET
Check this question: import-excel-spreadsheet-columns-into-sql-server-database
Solution 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.
SƲmmēr Aƥ
Updated on July 16, 2022Comments
-
SƲmmēr Aƥ almost 2 years
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.