February 14, 2011 at 8:16 pm
guys do you know how to import data from excel 2010 to MS Sql 9.0?
Should that need an SSIS or some DTS or SqlBulkCopy or ADO.net?
help me please.. thanks! 😀
February 14, 2011 at 8:48 pm
u can use import and export data....
February 14, 2011 at 8:49 pm
from where? 🙂
February 14, 2011 at 10:01 pm
right click database --> task --> import data.
search google on how to import data in sql server, you'll definitely find many links
February 14, 2011 at 10:32 pm
hi 😀
theres no import and export button there.. i scanned all the buttons but theres nothing there.. i think its the version im using.. im using sql server 2005.. and the one you gave to was the sql server 2008..
but its ok.
appriciated it! thanks!
but still i cant import my data to sql 🙁
February 14, 2011 at 10:43 pm
What Anum Verma suggested will also work exactly the same for SQL Server 2005. You did not mention what version of 2005 you are using. If you are using express Edition, it will not have this function.
February 14, 2011 at 10:52 pm
im currently using Microsoft SQL Server Management Studio Express..
i wanna know if theres a way to import data from excel to ms sql?
or should i need the BI? (business intelligence) or other dts?
i dont know im a newbie.. hehe
thanks a lot sir!
February 14, 2011 at 11:04 pm
Why not use the Import/Export wizard available in the SQL Server Management Studio.
Satnam
February 16, 2011 at 6:06 am
You can use SSIS to perform these type of Excel File imports to SQL Server.
1) Simply create a new SSIS project in BIDS 2005 or 2008
2) Drag an Excel source onto the Data flow design surface and double click it to open the Excel Source editor.
3) click the new button for the connection manager. This opens the Excel conection Manager dialog.
for the excel file path in this dialog, click browse to select the loction of your excel file and set some of the other properties as you see fit. you may also want to select "First row has column names", if you want to preserve these column names in the output - this is generally used when exporting excel data to flat comma separated value files.
4) Drag an OleDB destination onto the Data Flow designer, this will serve as your SQL database/table that you are writing your excel data to. Next double click this newly added OleDB destination to open its editor and establish a connection to your SQL Server database and set the parameter mapping and your all set.
Please note that SSIS supports Excel dta types, but unfortunately it does not translate well to how most databases are constructed. Most columns in Excel have probably been set to General. SSIS interprets General format as a Unicode data type in SSIS. If you have a Unicode type in SSIS and you try to insert into a varchar column, it will potentially fail. to solve this problem..
Use the Data Conversion Transform (performs the T-SQL equivalent of the Cast/convert function). Drag this transform onto your Data Flow designer and connect it to the Excel source you created above, double click it. Here you check the Excel columns that you need to convert. Consider making youe excel integer fields "four bit signed integers" in this dialog and make your excel character fields "string(DT_STR)" data types and you should be fine.
So here is your task sequence on the Data flow design surface...
Excel source connected to Data Conversion Transform which connects to the OleDB destination and your all set.
Hope this helps....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply