How can I import an Excel file into SQL Server

0 votes

Having data in an Excel file, or since it is 2020, an XLSX file. I need to enter this data into SQL Server in the manner described below:

  1. ad hoc, the use case being feeding tables with test data, or infrequent data loads of small amounts of data (say < 3k rows), and

  2. In a repeatable, robust, and possibly automated way for a production system.

Can someone please help me with this? 

Sep 13, 2022 in Database by Kithuzzz
• 38,000 points
2,054 views

1 answer to this question.

0 votes

You can use OPENROWSET to import an Excel file in SQL Server.

SELECT * INTO Your_Table FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                        'Excel 12.0;Database=C:\temp\MySpreadsheet.xlsx',
                        'SELECT * FROM [Data$]')

I hope this helps you.

answered Sep 15, 2022 by narikkadan
• 63,600 points

Related Questions In Database

0 votes
1 answer

How Can I use "Date" Datatype in sql server?

There's problem in all of them and ...READ MORE

answered Feb 9, 2022 in Database by Neha
• 9,020 points
1,361 views
0 votes
1 answer

How can we achieve excel ceiling function with significance value in SQL Server

If the underlying data types and rounding ...READ MORE

answered Apr 4, 2022 in Database by gaurav
• 23,260 points
1,008 views
0 votes
0 answers

How can I show the table structure in SQL Server query?

Query: SELECT DateTime, Skill, Name, TimeZone, ID, User, ...READ MORE

Aug 11, 2022 in Database by Kithuzzz
• 38,000 points
802 views
0 votes
0 answers

Import CSV file into SQL Server

I have a few fundamental questions and ...READ MORE

Aug 14, 2022 in Database by Kithuzzz
• 38,000 points
1,025 views
0 votes
1 answer

Stop Excel from automatically converting certain text values to dates

I've discovered that getting the desired result ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 63,600 points
529 views
+2 votes
1 answer
0 votes
0 answers

How to save the import csv file to mongodb using pyspark (or python)?

I have this code, and I want ...READ MORE

Oct 9, 2019 in Python by Ahmed
• 310 points
2,412 views
0 votes
0 answers

PHP import Excel into database (xls & xlsx)

One of the plugins I looked for ...READ MORE

Jul 30, 2022 in PHP by Kithuzzz
• 38,000 points
1,485 views
0 votes
1 answer

How can I set an SQL Server connection string?

.NET DataProvider -- Standard Connection with username ...READ MORE

answered Sep 19, 2022 in Database by narikkadan
• 63,600 points
696 views
0 votes
1 answer

How can I create a unique constraint on my column (SQL Server 2008 R2)?

The "indexes and keys" dialogue, not the ...READ MORE

answered Sep 20, 2022 in Database by narikkadan
• 63,600 points
927 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP