How to import data from Microsoft Excel into Microsoft SQL Server

Рет қаралды 30,867

Жыл бұрын

In this video, we'll have a look at how to import data from Excel into SQL Server.
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: rebrand.ly/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/microsoft-powerpiv...
----
In my previous video, I discussed how to import data from SQL Server to Excel. Now, it is the other way round.
In this video, I show you how to import csv and txt files into SQL Server, and Excel spreadsheets as well.
We'll also look at how to solve the "'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine" problem.
----
Links to my website are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: idodata.com/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): idodata.com/database-fundament...
SQL Server Essential in an Hour: idodata.com/sql-server-essenti...
70-462 SQL Server Database Administration (DBA): idodata.com/sql-server-databas...
DP-300: Administering Relational Databases: idodata.com/dp-300-administeri...
Microsoft SQL Server Reporting Services (SSRS): idodata.com/microsoft-sql-serv...
SQL Server Integration Services (SSIS): idodata.com/sql-server-integra...
SQL Server Analysis Services (SSAS): idodata.com/sql-server-ssas-mu...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/microsoft-powerpiv...
1Z0-071 Oracle SQL Developer - certified associate: idodata.com/iz0-071-oracle-sql...
SQL for Microsoft Access: idodata.com/sql-for-microsoft-...
DP-900: Microsoft Azure Data Fundamentals: idodata.com/dp-900-microsoft-a...

Пікірлер: 54

@alexniyonzima2082 5 ай бұрын

After a whole day of hitting dead ends I land on this helpful video. Thank you

@deannamccloud4906 4 ай бұрын

Thank you for this video! Took me 2 days plus uninstalling + reinstalling SQL multiple times before landing on your video. Thank You again!

@MuxicMax 7 ай бұрын

Thank you soo much I spent 2 literal days to understand this after installating tons of connectors and other driver this has eventually worked sir I owe you my life...

@Lolomikagi Жыл бұрын

OMG, thank you so much!! I could cry right now. I spent 8 hours trying to figure this out on my own.

@theforexation 7 ай бұрын

for real... what process just to setup a project

@adewunmiroland8436 6 ай бұрын

Likewise

@marc_aussie 8 ай бұрын

Thank you, info on using the DB driver instead of native client was very helpful, as well as how to fill out the properties box. Got my data loaded!

@zipporahebede370 Жыл бұрын

thank you so much. this video helped me so much, was almost losing hope. uninstalled my sql server so many times because i thought that was the issue

@TimelessView Жыл бұрын

Oh my god thank you, thank you so much. I've been struggling for the past few weeks 🥺🙏♥️♥️♥️

@randalsullivan4453 7 ай бұрын

A great video, thank you for this series!

@tosinesther4900 2 ай бұрын

You are such an Amazing tutor!!!

@akindeleadebayo6504 4 ай бұрын

Thank you sir oo. Finally able to import my data😀😀😀 after several attempts.

@pw3111 4 ай бұрын

Excellent Video. Thanks. Finally got the spreadsheet into SQL. Only problem was it took me days to get the Import Export function up and running. Kept getting an error message saying DTSWizard was missing. Finally solved. Hopefully this method will also work for Access Databases.

@isrick10 11 ай бұрын

Thank you so much, my friend

@huutoan1 11 ай бұрын

THANK YOU!

@rakeshmondal6982 Ай бұрын

Thank you so much

@huongvi462 Жыл бұрын

Thank you very much

@mohdafsarkhan1525 Жыл бұрын

Thank you alot ....

@Kavi-learn Жыл бұрын

thank you so much

@zuyderrathaman2497 11 ай бұрын

Hi there, I still get the error you mention at min 5.43 (import wizard, MS ACE.OLBD 1.6 provider) even when I install the Microsoft Access Database Engine 2016 Redistributable. Kindly assist

@paulntumbong9568 9 ай бұрын

I used this method in creating an ssis package, but I could not find the package after setting it up

@saojoaomeriti 11 ай бұрын

Thank you so much! Is it possible to conect Sharepoint list to SQL Server?

@SQLServer101 11 ай бұрын

Hi Saojoaomeriti. Please have a look at softkeys.uk/blogs/blog/how-to-connect-sharepoint-list-to-sql-server-database . Phillip

@mestermester6749 5 ай бұрын

What happens if in that case the delimiter is the comma "," but you have a column that internally has commas? For example "x value, y value, z value,"... the SQL divides this field into columns and it would be wrong because "it interprets the delimiter of said column as new columns", how is this fixed? Thank you!!!

@SQLServer101 4 ай бұрын

Hi Mester. Thank you for your question. You need a text qualifier - for example "first, second, third" will be interpreted as one field if you use quotation marks as the text qualifier. For more, see learn.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-a-flat-file-data-source-sql-server-import-and-export-wizard?view=sql-server-ver16 and scroll down to "Text qualifier". Phillip

@vinayakbhardwaj1776 Жыл бұрын

I also did the same steps as you did but am getting extra columns and rows with null values. What can I do now

@SQLServer101 Жыл бұрын

Thanks for watching! It looks like your data has some extra columns and rows that you didn't ask for. Don't worry, sometimes the best data sets are the ones that have a few unexpected surprises. I would delete the extra columns and rows after it has been imported. Phillip

@JAE-gg1xb Жыл бұрын

I know this is completely of topic. But by any chance do you have a udemy course for the Azure Data Engineer DP203 ? (Not the Azure Database Administrator)

@SQLServer101 Жыл бұрын

Hi JAE. I don't have a Udemy course for DP-203. Phillip

@JAE-gg1xb Жыл бұрын

@SQL Server 101 I have bought some of your udemy course. And it was truly awesome. You make the concepts very easy to understand, plus I can understand you clearly.

@patryknejmanowski8402 Жыл бұрын

Do files from Excel 365 work too? You only mention Excel 2019 and 2022 working while selecting Excel 2016 during import.

@SQLServer101 Жыл бұрын

Hi Patrykenj. The file needs to be accessed through the File Dialog box. If you can access your Excel 365 files through there, you should be fine. Phillip

@patryknejmanowski8402 Жыл бұрын

@@SQLServer101 Thanks for reassuring me. My organization is forcing a move from Office 2016 to Office 365 in 2 months time so I'll know for sure after that and report back.

@tcells24 3 ай бұрын

it's been weeks now that I've been trying to fix the import data problem. Unfortunately, I still have the same error when importing an excel workbook like it was shown in this video. Can someone reach out to me directly because I have tried this a million different ways and not sure what other options I have at this point?

@SQLServer101 2 ай бұрын

Hi TCells. I suggest going onto the Microsoft forums and asking someone to reach out to you directly from there. Phillip

@tcells24 2 ай бұрын

@@SQLServer101 I figured it out. I had to keep looking at more videos. I realized that since my pc is 64 bit, I have to force download the 32 bit version of access to somehow reverse the import export wizard. Very odd but it worked

@fadhlurrohmanfaqih480 6 ай бұрын

Im following your steps to import Excel data into the Microsoft SQL server, but still get an error. After reading many posts from stack overflow, it said that I needed to install Microsoft Access database engine 2010. But even after installing it, the error is still there. Any advice sir?

@SQLServer101 6 ай бұрын

Hi Fadhlurrohmanfaqih. Thank you for your question. There are two types of MS Access database engine 2010 - a 32-bit version and a 64-bit version. You have installed one of them - try installing the other. If that still doesn't work, try the same with MS Access database engine 2016. Phillip

@mmtsa 7 ай бұрын

Hello. I've done all the steps and also have installed Microsoft Access Database Engine 2016 Redistributable just like your tutorial and still getting the same error and no OLEDB provider has been installed on my Windows 10. What might be wrong when this happens? It says the installation was successful but the I can't see its reflection on my job. Any suggestion?

@SQLServer101 7 ай бұрын

Hi mmtsa. I would see if you have installed the right version. There is a 32-bit version and a 64-bit version. I would try installing the other version and see if that works. Phillip

@markk364 4 ай бұрын

@@SQLServer101 No, dont do that - you will end up with tons of issues related to compatability - BAD ADVICE!!

@user-yi8xb1du2v Жыл бұрын

I keep having an error message at the end of the installation. I can send you a screen short

@SQLServer101 Жыл бұрын

Hi User. I'm afraid you can't add screenshots to KZfaq comments. Phillip

@nochu4835 5 ай бұрын

i have almost 200k data, i tried to import all data but only 40k were imported, may i know how can i solve this?

@SQLServer101 5 ай бұрын

Hi Nochu. I would do it in smaller files. Phillip

@nochu4835 5 ай бұрын

@@SQLServer101 i see, no worries. already solved thankyou for the video!

@tayyabaabro5345 Жыл бұрын

I have followed same steps but getting this error while importing my data. TITLE: SQL Server Import and Export Wizard ------------------------------ Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE [dbo].[CovidDeaths] ( [iso_code] nva..." failed with the following error: "Culture is not supported. Parameter name: culture 3072 (0x0c00) is an invalid culture identifier.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. ------------------------------ BUTTONS: OK ------------------------------

@SQLServer101 Жыл бұрын

Hi Tayya. Thank you for your question. Please have a look at stackoverflow.com/questions/66232087/sql-server-2019-agent-job-execute-error-culture-is-not-supported . Phillip

@adewunmiroland8436 6 ай бұрын

Now this is what I call quality content.🤌

@Majed905 Жыл бұрын

Hello mr.Phillip I have followed your steps but it’s always give me an error I tried everything and it doesn’t work can you help me Messages Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "OwnerName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) Error 0xc020902a: Data Flow Task 1: The "Source - Nashville Housing Data for Data Cleaning_csv.Outputs[Flat File Source Output].Columns[OwnerName]" failed because truncation occurred, and the truncation row disposition on "Source - Nashville Housing Data for Data Cleaning_csv.Outputs[Flat File Source Output].Columns[OwnerName]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Users\ajhgs\Downloads\Nashville Housing Data for Data Cleaning.csv" on data row 275. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - Nashville Housing Data for Data Cleaning_csv returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

@SQLServer101 Жыл бұрын

Hi Majed. At 8'50" I click on "Edit Mappings". You will see in OwerName that the Size is too low for the data you are importing. You need to increase that number. Phillip

@Majed905 Жыл бұрын

@@SQLServer101 I make it 500 and still give me error