How to import data from Microsoft Excel into Microsoft SQL Server

Подписаться 17 тыс.

Просмотров 34 тыс.

50% 1

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/que...
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/dat...
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql...
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql...
SQL Server Integration Services (SSIS): rebrand.ly/sql...
SQL Server Analysis Services (SSAS): rebrand.ly/sql...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/mic...
----
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/que...
98-364: Database Fundamentals (Microsoft SQL Server): idodata.com/dat...
SQL Server Essential in an Hour: idodata.com/sql...
70-462 SQL Server Database Administration (DBA): idodata.com/sql...
DP-300: Administering Relational Databases: idodata.com/dp-...
Microsoft SQL Server Reporting Services (SSRS): idodata.com/mic...
SQL Server Integration Services (SSIS): idodata.com/sql...
SQL Server Analysis Services (SSAS): idodata.com/sql...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/mic...
1Z0-071 Oracle SQL Developer - certified associate: idodata.com/iz0...
SQL for Microsoft Access: idodata.com/sql...
DP-900: Microsoft Azure Data Fundamentals: idodata.com/dp-...

Опубликовано:

3 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 58

@alexniyonzima2082 8 месяцев назад

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

@Lolomikagi Год назад

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

@theforexation 10 месяцев назад

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

@adewunmiroland8436 9 месяцев назад

Likewise

@MuxicMax 10 месяцев назад

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...

@deannamccloud4906 7 месяцев назад

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

@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

@marc_aussie Год назад

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!

@pw3111 7 месяцев назад

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.

@akindeleadebayo6504 7 месяцев назад

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

@TimelessView Год назад

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

@genesisrojas6217 3 месяца назад

Omg!!!!! THANKS!!!! Finally a useful video

@tosinesther4900 5 месяцев назад

You are such an Amazing tutor!!!

@randalsullivan4453 10 месяцев назад

A great video, thank you for this series!

@zuyderrathaman2497 Год назад

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

@rakeshmondal6982 4 месяца назад

Thank you so much

@saojoaomeriti Год назад

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

@SQLServer101 Год назад

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

@adewunmiroland8436 9 месяцев назад

Now this is what I call quality content.🤌

@isrick10 Год назад

Thank you so much, my friend

@no.more.official 2 месяца назад

Thank you

@huutoan1 Год назад

THANK YOU!

@huongvi462 Год назад

Thank you very much

@mohdafsarkhan1525 Год назад

Thank you alot ....

@Kavi-learn Год назад

thank you so much

@paulntumbong9568 Год назад

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

@tcells24 6 месяцев назад

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 5 месяцев назад

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

@tcells24 5 месяцев назад

@@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

@mestermester6749 8 месяцев назад

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 8 месяцев назад

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

@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.

@carlanaielyperez4659 4 месяца назад

THANK YOU!

@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

@nochu4835 9 месяцев назад

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 8 месяцев назад

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

@nochu4835 8 месяцев назад

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

@mmtsa 10 месяцев назад

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 10 месяцев назад

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 7 месяцев назад

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

@mf_faqih 9 месяцев назад

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 9 месяцев назад

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

@PhetoleRamatapa Год назад

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 RU-vid comments. Phillip

@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

@bakihanma1680 Месяц назад

woah woah woah, that is not fine lmfao, bro just turned the sheet from a database into one table.

@SQLServer101 Месяц назад

Hi Baki. Thank you for your comment. This video is about importing an Excel spreadsheet into a SQL Server table, not from a database. Phillip

@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