|
|
You're in the
sql server dts
group:Help! I need a simple way to import an Excel file...
sql server dts:
I recently upgraded my dev machine to SQL 2005. It's the only machine on our network that's running SQL 2005; everything else is on SQL 2000. It's been a bit of a struggle getting used to the new management console, but mostly liveable. However, now I'm getting totally frustrated by something that seems like it should be incredibly simple. I need to import a spreadsheet into a database that resides on one of our SQL 2000 servers. I've done this a million times in SQL2K - go to the table list for that database, right-mouse click on the table list, select Import Data and go from there. Maybe I was just lazy, because I've never really bothered learning the bcp syntax - it hasn't been a big deal. So now, of course, I try to do the same thing and that option isn't there. And the more I dig, the more it starts to look as if I HAVE to use bcp, or, God help me, SSIS inside a Visual Studio project, which looks like shooting a mouse with an elephant gun. So, is there an easy way to import a spreadsheet to a SQL2K (or SQL 2005) database? I have tried bcp (have to have an existing table created that you import into; not difficult, but not as simple as before), OPENDATASOURCE (tells me I have an invalid table name), and looked at SSIS. FYI: These Aren't Easy. And, in case I'm stuck w/ the OPENDATASOURCE method, here's the code I was using: SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\WebsiteApproval.xls"; User ID=Admin;Password=;Extended properties=Excel 8.0')...WebsiteApproval It seems to open the spreadsheet correctly, but it doesn't recognize the TableName (the last item). The only sheet is named WebsiteApproval, and there are no named ranges. Please, could somebody give me some good news! Thanks in advance, and I'm sorry for venting, Jim
That certainly fits the criteria of "easy". Thank you very much.
[quoted text, click to view] "Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in message news:OEChOP6BGHA.1028@TK2MSFTNGP11.phx.gbl... > In SSMS, right-click the DB, Tasks, Import Data... > This will invoke the wizard, which builds a SSIS package for you in much > the same way that the wizard in SQL 2000 built a DTS package. > > > -- > Darren Green > http://www.sqldts.com > http://www.sqlis.com > > "Jim" <floopdog@bodiddy.com> wrote in message > news:eSOwzB1BGHA.1028@TK2MSFTNGP11.phx.gbl... >> Hi, >> >> I recently upgraded my dev machine to SQL 2005. It's the only machine on >> our network that's running SQL 2005; everything else is on SQL 2000. >> It's been a bit of a struggle getting used to the new management console, >> but mostly liveable. However, now I'm getting totally frustrated by >> something that seems like it should be incredibly simple. >> >> I need to import a spreadsheet into a database that resides on one of our >> SQL 2000 servers. I've done this a million times in SQL2K - go to the >> table list for that database, right-mouse click on the table list, select >> Import Data and go from there. Maybe I was just lazy, because I've never >> really bothered learning the bcp syntax - it hasn't been a big deal. So >> now, of course, I try to do the same thing and that option isn't there. >> And the more I dig, the more it starts to look as if I HAVE to use bcp, >> or, God help me, SSIS inside a Visual Studio project, which looks like >> shooting a mouse with an elephant gun. >> >> So, is there an easy way to import a spreadsheet to a SQL2K (or SQL 2005) >> database? I have tried bcp (have to have an existing table created that >> you import into; not difficult, but not as simple as before), >> OPENDATASOURCE (tells me I have an invalid table name), and looked at >> SSIS. FYI: These Aren't Easy. And, in case I'm stuck w/ the >> OPENDATASOURCE method, here's the code I was using: >> >> SELECT * >> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', >> 'Data Source="c:\WebsiteApproval.xls"; >> User ID=Admin;Password=;Extended properties=Excel >> 8.0')...WebsiteApproval >> >> It seems to open the spreadsheet correctly, but it doesn't recognize the >> TableName (the last item). The only sheet is named WebsiteApproval, and >> there are no named ranges. >> >> Please, could somebody give me some good news! >> >> Thanks in advance, and I'm sorry for venting, >> >> Jim >> > >
In SSMS, right-click the DB, Tasks, Import Data...
This will invoke the wizard, which builds a SSIS package for you in much the same way that the wizard in SQL 2000 built a DTS package. -- Darren Green http://www.sqldts.com http://www.sqlis.com [quoted text, click to view] "Jim" <floopdog@bodiddy.com> wrote in message news:eSOwzB1BGHA.1028@TK2MSFTNGP11.phx.gbl... > Hi, > > I recently upgraded my dev machine to SQL 2005. It's the only machine on > our network that's running SQL 2005; everything else is on SQL 2000. It's > been a bit of a struggle getting used to the new management console, but > mostly liveable. However, now I'm getting totally frustrated by something > that seems like it should be incredibly simple. > > I need to import a spreadsheet into a database that resides on one of our > SQL 2000 servers. I've done this a million times in SQL2K - go to the > table list for that database, right-mouse click on the table list, select > Import Data and go from there. Maybe I was just lazy, because I've never > really bothered learning the bcp syntax - it hasn't been a big deal. So > now, of course, I try to do the same thing and that option isn't there. > And the more I dig, the more it starts to look as if I HAVE to use bcp, > or, God help me, SSIS inside a Visual Studio project, which looks like > shooting a mouse with an elephant gun. > > So, is there an easy way to import a spreadsheet to a SQL2K (or SQL 2005) > database? I have tried bcp (have to have an existing table created that > you import into; not difficult, but not as simple as before), > OPENDATASOURCE (tells me I have an invalid table name), and looked at > SSIS. FYI: These Aren't Easy. And, in case I'm stuck w/ the > OPENDATASOURCE method, here's the code I was using: > > SELECT * > FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', > 'Data Source="c:\WebsiteApproval.xls"; > User ID=Admin;Password=;Extended properties=Excel > 8.0')...WebsiteApproval > > It seems to open the spreadsheet correctly, but it doesn't recognize the > TableName (the last item). The only sheet is named WebsiteApproval, and > there are no named ranges. > > Please, could somebody give me some good news! > > Thanks in advance, and I'm sorry for venting, > > Jim >
You can also call the import/export wizard from SQL management studio by
right-clicking the database...tasks....import/export. You can execute immediately or save the package (and edit with VS2005 aka elephant gun) JL [quoted text, click to view] "Jim" <floopdog@bodiddy.com> wrote in message news:OaCBKU%23BGHA.2920@tk2msftngp13.phx.gbl... > That certainly fits the criteria of "easy". Thank you very much. > > "Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in > message news:OEChOP6BGHA.1028@TK2MSFTNGP11.phx.gbl... >> In SSMS, right-click the DB, Tasks, Import Data... >> This will invoke the wizard, which builds a SSIS package for you in much >> the same way that the wizard in SQL 2000 built a DTS package. >> >> >> -- >> Darren Green >> http://www.sqldts.com >> http://www.sqlis.com >> >> "Jim" <floopdog@bodiddy.com> wrote in message >> news:eSOwzB1BGHA.1028@TK2MSFTNGP11.phx.gbl... >>> Hi, >>> >>> I recently upgraded my dev machine to SQL 2005. It's the only machine >>> on our network that's running SQL 2005; everything else is on SQL 2000. >>> It's been a bit of a struggle getting used to the new management >>> console, but mostly liveable. However, now I'm getting totally >>> frustrated by something that seems like it should be incredibly simple. >>> >>> I need to import a spreadsheet into a database that resides on one of >>> our SQL 2000 servers. I've done this a million times in SQL2K - go to >>> the table list for that database, right-mouse click on the table list, >>> select Import Data and go from there. Maybe I was just lazy, because >>> I've never really bothered learning the bcp syntax - it hasn't been a >>> big deal. So now, of course, I try to do the same thing and that option >>> isn't there. And the more I dig, the more it starts to look as if I HAVE >>> to use bcp, or, God help me, SSIS inside a Visual Studio project, which >>> looks like shooting a mouse with an elephant gun. >>> >>> So, is there an easy way to import a spreadsheet to a SQL2K (or SQL >>> 2005) database? I have tried bcp (have to have an existing table >>> created that you import into; not difficult, but not as simple as >>> before), OPENDATASOURCE (tells me I have an invalid table name), and >>> looked at SSIS. FYI: These Aren't Easy. And, in case I'm stuck w/ the >>> OPENDATASOURCE method, here's the code I was using: >>> >>> SELECT * >>> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', >>> 'Data Source="c:\WebsiteApproval.xls"; >>> User ID=Admin;Password=;Extended properties=Excel >>> 8.0')...WebsiteApproval >>> >>> It seems to open the spreadsheet correctly, but it doesn't recognize the >>> TableName (the last item). The only sheet is named WebsiteApproval, and >>> there are no named ranges. >>> >>> Please, could somebody give me some good news! >>> >>> Thanks in advance, and I'm sorry for venting, >>> >>> Jim >>> >> >> > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |