[sql-server] 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.

This question is related to sql-server bulkinsert xlsx

The answer is


You need to use OPENROWSET

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


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.


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.


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

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to bulkinsert

Insert Multiple Rows Into Temp Table With SQL Server 2012 PostgreSQL - SQL state: 42601 syntax error Import CSV file into SQL Server Cannot bulk load. Operating system error code 5 (Access is denied.) How to Bulk Insert from XLSX file extension? Bulk Insert Correctly Quoted CSV File in SQL Server How to speed up insertion performance in PostgreSQL BULK INSERT with identity (auto-increment) column How do I temporarily disable triggers in PostgreSQL? mongodb: insert if not exists

Examples related to xlsx

How to save .xlsx data to file as a blob Parse XLSX with Node and create json Easy way to export multiple data.frame to multiple Excel worksheets Using Pandas to pd.read_excel() for multiple worksheets of the same workbook Python convert csv to xlsx How to Bulk Insert from XLSX file extension? Convert xlsx to csv in Linux with command line Importing Excel files into R, xlsx or xls Convert xlsx file to csv using batch Excel "External table is not in the expected format."