Import data from Excel to SQL Server table

[@AdedayoMuhammed]

THANKSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS U SO MUCH ,STRAIGHT FORWARD ND WELL EXPLAINED ,U MAKE MY DAY

[@Mini_LOL_Station]

Thaks Manoj Ji. Thaks for sharing such kind of information in this platform.

[@aykuterdogan4808]

Great video, Manoj. Thank you for this video.

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@johng5295]

Thanks in a million! Very well explained. This is the nth time that I am watching this again. Great content. Awesome. I couldn't find this explanation--simply put anywhere else. “Great teachers are hard to find”. Grade: A++ 💥

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@failureiseverything]

Sql server native client 11.0 is not showing in my destination field

[@rasalghulgaming4072]

Thank you Simple, Clear Explanation

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@FrancescoGrosso1094]

1.How about appending/adding data to an already existing table? 2. Can you choose the delimiters if the raw data is not clean, i.e. not perfectly delimited on every row?

[@ManojPandeyManub22]

For #1 you can try creating a Stored Procedure and get the data in a temp table first, then MERGE in your main destination table. For #2 you will have to clean the XL or CSV file, otherwise you will end up errors/issues or mixing up columns data.

[@FrancescoGrosso1094]

@@ManojPandeyManub22 Cheers mate! Thanks!

[@cesarl.c.847]

Hi Francesco... to check LoaderDataPlus tool, loads flat files and export to MariaDB, MySQL, PostgreSQL, Oracle, SQLSERVER, SQLite.

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@amittechnicalagra]

​@@ManojPandeyManub22kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@LogarPassport]

❤❤❤Thanks work for me

[@Ocelot_1997]

Many thanks Manoj!

[@sallu21m]

Getting error ...MS.ace.oledb.12.0 provider is not registered on local machine.....pls help

[@sumbalkhan8208]

Same issue

[@kanasree123]

Helped me a lot. Thanks a lot......

[@excellearning8555]

Thank you very much Brother.Nice Guide.

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@furremurre]

Thanks, Manoj !

[@utkarshkumar8671]

Thank you so much Manoj, this helped me a lot.

[@sulaimandanish4561]

Bro i'm learning to be a DBA. Can you give me some tips regarding it?

[@BoxerDogs]

This is going to be easier than I thought. Would I be able to use SQL Server Studio to connect to a Deltek Costpoint database?

[@PhilcoCup]

As long as you are able to procure the installer for the ODBC which is the connector for that specific DB you should be able to configure the data origin in your control panel, and then it SHOULD appear in the options

[@sonaligore7872]

thank u for your video

[@ijeanpierrebp]

great tutorial

[@amintaleghani2110]

thanks for your video. What will happen to the formulas that are the excel table after importing to SQL?

[@ManojPandeyManub22]

only data gets imported from the cells/sheets, formulas are behind the XL cells.

[@BoxerDogs]

Can you do a video that shows how to use data in an Excel file, to update existing records in a database?

[@SQLwithManoj]

This is bit tricky and probably not a good way to do via XL with macros. Please create your own UI or work with somebody who knows it. The above demo I shown was for only data analysis purpose.

[@TheIanmurphy]

I watched your video on exporting data as well and it was very helpful. I want to know if its possible to export the data to excel, make changes to it, and then import the data back into the same table to make updates instead of importing a new table?

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@JoelErnstrom]

Hello there! I have a very important question and I've been tearing my hair out trying to find the answer. I have an Excel spreadsheet much like yours that I have imported to SQL Server. On the spreadsheet, I have dates in the same format that you are using; Year-Month-Day. This is EXACTLY how I want it. But, this SQL Server just added (literally) 17 zeros! behind each of the dates. (1996-03-24 00:00:00.0000000 +00:00) HOW do I get rid of all the dang zeros? YOUR import shows BirthDate and HireDate with no zeros! What have I done wrong? Thank you for your time and, trust me, I can hardly wait for your reply!

[@ManojPandeyManub22]

I think the column datatype in your table is DATETIME or DATETIME2. If you just want to store the DATE, and not the time, please change its DATATYPE to DATE, by using ALTER command.

[@JoelErnstrom]

@@ManojPandeyManub22 Thank you very much for getting back to me on this! So, I ran an ALTER command and it gave me some error message about 'not allowing nulls' or something. Then, I went into the table and did something that, "apparently", I'm not supposed to be able to do without 'dropping? or recreating?' (yeah, I'm a noob at this) the table. I right clicked on my 'Date' column, clicked 'Modify', and changed the Type (you were right about DateTime) to 'Date'. That worked. The Date column is showing how I thought it needed to be originally. Upon further investigation into how SQL saves dates, the format doesn't really matter. The only thing that matters is how it needs to be read on the user/application end--which is the MAIN issue to my Date problem. I am creating an application in C#/VS and I need to figure out why, when I load my data into dataGridView1, it shows improperly--MM-dd-yyyy. I never told it to do that. Why doesn't VS just use the date "AS IT'S ENTERED" and not assume that I must want it changed?

[@rahulgautam511]

Hi Manoj, Hope you are doing good. Getting below errors while importing the data.

[@sebamary9634]

how to import specific columns from excel file into an existing table with CONDITION?

[@yunisahmed4772]

dear, if you got the result please send to me, thanks a lot

[@TeluguCookingdelights]

Hi, Instead of repeating this task each and every time manually is there any query to perform the same operation. Thanks in advance

[@nnaveen646]

I want the live data to get exported from Excel to SQL server automatically at scheduled intervals?

[@SQLwithManoj]

You need SSIS for this and you will need to create SQL-Job to schedule to run at specific intervals.

[@Veemes1]

Lifesaver!

[@noradlinarosli380]

Hi Sir, For the column in excel, should be follow the sequence in sql table? Thank you

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@montoyescoful]

I have multiple tables in one single sheet in Excel (all of them in table format). How do I import them into SQL Server?

[@SQLwithManoj]

you can check my another video on this: kzbin.info/www/bejne/Y4G8ZqqLZZqkiMU

[@pauls7056]

Great video, Manoj. Short, well explained and easy to follow. Thank you for posting.

[@yuvrajpatil55]

how to import today data from particular table... please request you make such video and share

[@SQLwithManoj]

sure @Yuvraj, can you please provide more detail on your ask?

[@yuvrajpatil55]

i have import data from sql server to know department wise current date sale but it's showing all table i need only today's date data from sql . so please suggest

[@SQLwithManoj]

see, your table should have a column to determine today's data. If its there then you can apply filter by WHERE clause to get today's or a specific date. Like: WHERE CreatedDateColumn = GETDATE();

[@ayushisahu9926]

Thanks the vedio was so helpful but facing few error

[@SiloTvGaming]

Outstanding job!

[@muhammadomer3676]

Good Tutorial. Can you please tell how to import specific columns from excel file into an existing table?

[@SQLwithManoj]

To import specific columns you can either: 1. copy excel worksheet with specific columns you want 2. dump data in another table, then use specific columns into your main target table.

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@amittechnicalagra]

​@@SQLwithManojkzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@drs10301]

Hi, Could you please help me how to import data from excel to sql server using sql script

[@SQLwithManoj]

sorry for replying late, but this can be done by using OPENQUERY() or Linked Server, link: sqlwithmanoj.com/2010/11/12/query-excel-file-source-through-linked-server/

[@dz-zs2vj]

hi I have export an excel csv file and I want to add a column using conditional fractionation transformer in ssis how i do that ?

[@KarishmaNazShaik]

Where can i find all the inserted values.. its showing table..where all the table got created but where can i find the inserted values file

[@rosaccalla1]

thanks so mauch.. ive a problem when I try to import. Ahora ya lo puedo importar correctamente

[@99.milestothemoonchubby12]

when I import files excel. it show "TITLE: SQL Server Import and Export Wizard ------------------------------The operation could not be completed.------------------------------ ADDITIONAL INFORMATION:The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)------------------------------ BUTTONS:OK ------------------------------ "

[@ManojPandeyManub22]

this issue comes when you Excel & SQL Server are not on same architecture, i.e. 32 bit or 64 bit. I think your SQL Server is on 64 bit, but your Excel or ms office is on 32 bit. Both of them need to be the same, you need to uninstall either of them and install the same version.

[@yohannesalmaw484]

I do have the same issue.

[@SHIVAMKumar-uo3xm]

i am getting error. >>> TITLE: SQL Server Import and Export Wizard ------------------------------ The operation could not be completed. ------------------------------ ADDITIONAL INFORMATION: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data) ------------------------------ BUTTONS: OK ------------------------------

[@jackiecooper9439]

Getting the same error. Did you find any resolution?

[@bikassingh7469]

Awesome man. Thank you so much

[@tiwari_gulshan]

Sir i am importing a excel file where in one column has date in it but some row are blanks while importing this file have given this date datatype as database data but its showing error could please suggest me correct one

[@dipakmaiti2676]

excellent

[@rajendiranwalaja7101]

Thanks

[@sruthimeena3782]

What to do, in my sql server the data source is not shown excel. It only sql client and oledb.net connection

[@PK0608]

No one gives a link to the Excel or CSV file used in this video. Disappoint to see even Manoj did not do it. I am getting error.

[@cesarl.c.847]

Hi Prakash... may be your interest to check LoaderDataPlus tool, loads and verifies flat files and exports them to MariaDB, MySQL, PostgreSQL, Oracle, SQLSERVER, SQLite.

[@jaykumarnilkanth2354]

hi sir, I am not able to get the task option, I am still stuck on how to import the data can you please help me with that

[@kishoreak302]

Thanks for the video..can u let us know how add additional new rows in this table from excel?

[@PhilcoCup]

That would be done with the Insert command, you might want to check out the good tutorials at w3 schools www.w3schools.com/sql/sql_insert.asp

[@primarqo]

You just use existing table in destination instead of new table.

[@emma6366]

i don't have the option sql server native client 11.0 option what should i do ?

[@maramalbadrani5453]

Do you find any solution? I have the same problem

[@emma6366]

@@maramalbadrani5453 ​ there are 2 solutions: 1- download visual studio installer and follow all the steps then reinstall ssms. 2- (someone else's comment) As of 6.25.2023, So I have the Microsoft SQL SERVER 2022 version 64 bit install. I was able to get it to work by installing the latest "Microsoft ODBC Driver for SQL Server" and choosing that one as the destination since the SQL Server Native Client 11.0 no longer appears as an option.

[@rondunphy9433]

Thank you!

[@mayanksapra6507]

Hello Sir, Thanks for this video. I am using SSMS 19.1, in this version this option "SQL Server Native client 11" is not listed as destination in SQL Server import and export wizard. Can you please share a solution how can i fix this?

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@blackpanther9809]

there is no option as microsoft excel in the data source ,,what should i do

[@PhilcoCup]

Maybe it was not included on installation, or maybe yo do not have Excel?

[@wendyk9694]

What if you don't have Microsoft.ACE.OLEDB registered on your local machine?

[@basheerj1754]

Manoj, could you please answer to this question, i dont have Microsoft.ACE.OLEDB provider registered in my local machine, pls help us through...

[@manishpatel9294]

Hi Manoj ji, I want to import 5 lacs raw from just one excel table to sql table and I did it yesterday but still it is on stuck at “copying to” on 10th step is almost more then 14 hours, kindly support me in this matter

[@prasunjeetsoni6143]

great it works..

[@shravankumarerra7936]

Good sir.....can u please teach us full course on MS SQL server DBA in 1 video......it wil help us sir....

[@amittechnicalagra]

kzbin.info/www/bejne/fYi7nX-opaqbiJYsi=HLf4dhBCL2nrgr7B

[@buildyourself009]

not done bro...

[@vinaydubey9590]

i am getting this result while import from excel please help. TITLE: SQL Server Import and Export Wizard ------------------------------ The operation could not be completed. ------------------------------ ADDITIONAL INFORMATION: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data) ------------------------------ BUTTONS: OK ------------------------------

[@SQLwithManoj]

you need to install the "Microsoft Access Database Engine", check this link: sqlwithmanoj.com/2016/12/24/sql-error-the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine-system-data/

[@Jmtreciever2]

Getting error