How to import data from Microsoft Excel into Microsoft SQL Server
Vložit
- čas přidán 26. 04. 2023
- 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... - Věda a technologie
![How to EASILY Import data from EXCEL to SQL Server Tables [2024]](http://i.ytimg.com/vi/8EByG5t4UyE/mqdefault.jpg)
After a whole day of hitting dead ends I land on this helpful video. Thank you
Omg!!!!! THANKS!!!! Finally a useful video
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...
Oh my god thank you, thank you so much. I've been struggling for the past few weeks 🥺🙏♥️♥️♥️
Thank you for this video! Took me 2 days plus uninstalling + reinstalling SQL multiple times before landing on your video. Thank You again!
A great video, thank you for this series!
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!
OMG, thank you so much!! I could cry right now. I spent 8 hours trying to figure this out on my own.
for real... what process just to setup a project
Likewise
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
You are such an Amazing tutor!!!
Thank you sir oo. Finally able to import my data😀😀😀 after several attempts.
Thank you so much, my friend
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.
THANK YOU!
Thank you very much
Thank you so much
Thank you alot ....
thank you so much
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
I used this method in creating an ssis package, but I could not find the package after setting it up
Thank you so much!
Is it possible to conect Sharepoint list to SQL Server?
Hi Saojoaomeriti. Please have a look at softkeys.uk/blogs/blog/how-to-connect-sharepoint-list-to-sql-server-database . Phillip
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?
Hi TCells. I suggest going onto the Microsoft forums and asking someone to reach out to you directly from there. Phillip
@@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
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)
Hi JAE. I don't have a Udemy course for DP-203. Phillip
@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.
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!!!
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
Do files from Excel 365 work too? You only mention Excel 2019 and 2022 working while selecting Excel 2016 during import.
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
@@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.
I also did the same steps as you did but am getting extra columns and rows with null values.
What can I do now
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
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?
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
@@SQLServer101 No, dont do that - you will end up with tons of issues related to compatability - BAD ADVICE!!
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?
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
I keep having an error message at the end of the installation. I can send you a screen short
Hi User. I'm afraid you can't add screenshots to CZcams comments. Phillip
i have almost 200k data, i tried to import all data but only 40k were imported, may i know how can i solve this?
Hi Nochu. I would do it in smaller files. Phillip
@@SQLServer101 i see, no worries. already solved thankyou for the video!
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
------------------------------
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
Now this is what I call quality content.🤌
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)
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
@@SQLServer101 I make it 500 and still give me error
THANK YOU!