September 20, 2011 at 5:52 am
Hi all,
i have excel(.xlsx) file having alot of data.how i can import it to sqlserver2005's table?
September 20, 2011 at 5:59 am
use ssis.
September 20, 2011 at 6:01 am
<removed>
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 20, 2011 at 6:01 am
@SSCrazy
i can not use this...no option in my sqlserver2005....can u pls guid me a litle bit?
September 20, 2011 at 6:05 am
i tried alot but in vain ,To use DTS..there is no option for xlsx...they only support xls..what i can do here?
in this case
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
i m getting error...
September 20, 2011 at 6:07 am
engrshafiq4 (9/20/2011)
@SSCrazyi can not use this...no option in my sqlserver2005....can u pls guid me a litle bit?
you can install ssis from the setup disk.
September 20, 2011 at 6:09 am
engrshafiq4 (9/20/2011)
i tried alot but in vain ,To use DTS..there is no option for xlsx...they only support xls..what i can do here?in this case
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
i m getting error...
you will need to use the MS data access component version 12 to connect to XSLX, google "SSIS XLSX" and you will find loads of examples
September 20, 2011 at 6:19 am
Actually when i am going to sqlserver i do not have SQL Server Business Intelligence Development Studio....although i configuration manager i do have ssis having state running
September 20, 2011 at 6:23 am
engrshafiq4 (9/20/2011)
Actually when i am going to sqlserver i do not have SQL Server Business Intelligence Development Studio....although i configuration manager i do have ssis having state running
BIDS shouldn't be installed on your SQL server, it is desinged to be a client application that connects to the server, so it should be installed on your workstation and if it isn't then you can install it by running the SQL setup disk and selecting the required options to install BIDS.
September 20, 2011 at 6:45 am
Now How i can copy whole data from one column in one table and replace the old data in other table'column?
September 20, 2011 at 6:52 am
engrshafiq4 (9/20/2011)
Now How i can copy whole data from one column in one table and replace the old data in other table'column?
you can do this in SSIS, by using the SQL task to do an Update statement, or for a faster set-based method use a staging table and the UPDATE T-sql statement.
if the tables are in the same db/server then it may be easier to simply write a t-sql udpate to do the update.
September 20, 2011 at 6:54 am
yes the tables are in the same server...how i can write update statement?
September 20, 2011 at 6:57 am
Hi,
If you are using xlsx file in DTS are you getting any error?
You can change xlsx file in xls also use save option in Excel.
Shatrughna
September 20, 2011 at 7:00 am
when i save as xlsx to xls,some data are missing...could not get whole data....next question is...should the columns in table and header text in xls will be same?
September 20, 2011 at 7:04 am
engrshafiq4 (9/20/2011)
yes the tables are in the same server...how i can write update statement?
go read BOL it is all in there..
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply