February 2, 2012 at 1:07 am
Hi,
I have to insert data from excel sheet to sql server table.
so can you please provide me the solution for how to do this in details.
I am using sql server 2008.
Regards,
Kiran
February 2, 2012 at 1:51 am
This was removed by the editor as SPAM
February 2, 2012 at 2:01 am
kiran.rajenimbalkar (2/2/2012)
Hi,I have to insert data from excel sheet to sql server table.
so can you please provide me the solution for how to do this in details.
I am using sql server 2008.
Regards,
Kiran
What have you come up with so far?
Did you do a Google search for possible solutions? One of the first results for sql server excel (not really the most advanced search terms) gives this link:
How to import data from Excel to SQL Server
You can also edit the table by right-clicking on it, select "edit table" and then copy pasting your excel values into the table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2012 at 3:38 am
:w00t:
Hello I found you a good Article ,About your request
February 2, 2012 at 5:11 am
I will tell you this step by step.
1. Go to Object Explorer.Right click on your database on which you have to import that excel file.
2. Go to Task and then go to Import Data and the wizard will open
3. now in data Data source select Microsoft Excel then click on browse button and select the file you want to import and click Next.
4. In next wizard choose whether its your window Autentication or sql authentication and accordingly give your username and password and Click next
5.again click next
6. select one check box for one table import in source and the name of the table in destination click next
7. Again click next
8. Click Finish to import the data
9. The wizard will show you execution successful
now your data is imported in your table with name given in step 6
that all my dear..
still any query reply
Prashant Goswami
February 2, 2012 at 7:18 am
Thanks for your reply its very nice for SSIS.
February 2, 2012 at 8:48 am
johnitech.itech (2/2/2012)
:w00t:Hello I found you a good Article ,About your request
Did you even read the forum title before posting this? This forum is for 2008, not 2005.
February 3, 2012 at 7:25 am
I've worked on projects in the past where the business would provide Excel sheets for populating reference tables, and they would basically just create and fill in the data in Excel by hand. There are 100 different things that can go wrong; like inconsistent column or sheet names, mixed data types in a column, truncating leading zeros, leading or trailing spaces, etc.
If you are ingesting data feeds on a recurring basis, then I'd suggest asking the source to submit the files in TAB delimited text. Excel is good as a presentation and analysis tool, but for data interchange... not so much.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 3, 2012 at 7:32 am
This was removed by the editor as SPAM
February 3, 2012 at 8:01 am
Stewart "Arturius" Campbell (2/3/2012)
Eric M Russell (2/3/2012)
I've worked on projects in the past where the business would provide Excel sheets for populating reference tables, and they would basically just create and fill in the data in Excel by hand. There are 100 different things that can go wrong; like inconsistent column or sheet names, mixed data types in a column, truncating leading zeros, leading or trailing spaces, etc.If you are ingesting data feeds on a recurring basis, then I'd suggest asking the source to submit the files in TAB delimited text. Excel is good as a presentation and analysis tool, but for data interchange... not so much.
Another bugbear regarding importing files from Excel lies in the version used.
the provider will differ depending on the version of excel used - pre 2007 used the JET provide, while 2007 and above use the ACE provider.
If you get data from 3 areas to import, of which 1 makes use of Excel 2003 and the other 2 Excel 2007/2010, you have a challenge.....
Yes, a production SQL Server box won't by default have the ACE or MSDASQL installed, and getting it installed involves meeting(s) with the sysadmins and production control.
Honestly, when I need to setup a fairly simple file ingest for a database, my default approach is to create a job with a T-SQL task doing a BULK INSERT like so:
bulk insert Products
from 'd:\products.txt'
with (fieldterminator='\t'
,rowterminator=''
,firstrow=2);
If it's slowly changing data or requires some transformations and duplicate checks, then I'll first bulk insert into a temp table and then MERGE it with the table.
Deploying SSIS packages, fiddling with data provider settings, and asking business to pretty please not change the formatting of their Excel sheets is a tiresome process. I find it much easier and more reliable to BULK INSERT.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 4, 2012 at 4:27 am
I should point out that (unless someone corrects me) there are still no 64-bit excel drivers for SSIS. You will need to execute the package using the 32-bit dtexec from the command line.
February 4, 2012 at 4:27 am
MysteryJimbo (2/4/2012)
I should point out that (unless someone corrects me) there are still no 64-bit excel drivers for SSIS. You will need to execute the package using the 32-bit dtexec from the command line.
Assuming you are using 64-bit SQL that is.
February 5, 2012 at 5:56 am
MysteryJimbo (2/4/2012)
I should point out that (unless someone corrects me) there are still no 64-bit excel drivers for SSIS. You will need to execute the package using the 32-bit dtexec from the command line.
Actually they are available:
http://www.microsoft.com/download/en/details.aspx?id=13255
(for Excel 2007 and up)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 5, 2012 at 12:55 pm
As a sidebar, I generally don't import directly from Excel spreadsheets because you can't tell when someone might have it open. I usually tell people to do an export from the spreadsheet to a TAB delimited file.
--Jeff Moden
February 5, 2012 at 1:42 pm
Jeff Moden (2/5/2012)
As a sidebar, I generally don't import directly from Excel spreadsheets because you can't tell when someone might have it open. I usually tell people to do an export from the spreadsheet to a TAB delimited file.
And do the people generally listen? 🙂
Unfortunately, Excel imports are still far too common these days.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply