Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mfemenel
Professor Frink
1421 Posts |
 Posted - 2003-07-07 : 13:03:16
|
| Here's what I'm trying to do:We are going to receive 1 excel file, with 2 seperate tabs of information. I need to import both tabs. However, I really would prefer not to use DTS. I know I could open excel and save each tab as a seperate .csv file, but I want this job to check for new files and import them if they're there. I don't want anyone to have to split the file apart to have it run successfully. We don't have a choice on how we're getting the file from the vendor. Any ideas?Mike"oh, that monkey is going to pay" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-07-07 : 13:15:51
|
| This is one I did earlier - can't remember what happened but pretty sure it worked at one point.SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Receipts_Journal_ID.xls";Extended Properties=Excel 8.0;HDR=Yes')..sheet1$.journals==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2003-07-07 : 13:20:37
|
| That's a great idea. I'm following you up 'till the last section, ..sheet1$journals.Obviously you're looking at sheet1, why the ".." and is "journals" a column name?Mike"oh, that monkey is going to pay" |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2003-07-07 : 13:55:51
|
| Now I know I'm close, but it tells me the sheet name doesn't exist. Ideas?SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\test1.xls";User ID=Admin;Password=;Extended properties=Excel 8.0;')...Mike$I've confirmed the names and capitalizationMike"oh, that monkey is going to pay" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-07-07 : 14:05:28
|
| >> pretty sure it worked at one point.I have no idea - it was a while ago I did this and can't remember what state it was left in.I'll try it again when I get time.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2003-07-07 : 14:18:20
|
| Got it. Helps to actually have QA open to your local server when it's sitting on your friggin C drive. Stupid stupid stupid!!!SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\test1.xls;Extended Properties=Excel 8.0')...[Mike$]Mike"oh, that monkey is going to pay" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-07 : 14:24:48
|
| That happens to me too. There is a rule here that you should not log into the production machine except when performing maintenance. So we log into another machine that can get to the production servers. Well this works most of the time except when I am creating a DTS package that needs to export data to a file. The file gets created on the machine that you are connected to. Then we you go to run the DTS package through a job on the production server, it can't find the file because it doesn't exist there (it exists on the machine that I logged into). Usually only takes me about 30 seconds to figure out why it is failing, but each time I feel like an idiot.Tara |
|
|
|
|
|
|
|
|
BCP From Excel