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 |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2004-06-15 : 07:09:34
|
Hi all,Never had to play around with excel data into SQL before (a very sheltered life, I know  ).Now I am looking at this. The way I see it, there are a few options:DTS - pretty simple to set up (except for on file that gives a strange jet driver error complaining that "the external table is not in expected format", "error during initialization of the provider") for some of the files. My concern about this is migrating DTS packages through dev to test to pre production to production - is there a nice way of doing this? I always end up having to re-create the DTS package.BCP - trying to play around to get this working. Would this be the "recommended method"? I seem to recall that it would be faster, although given the files size, that may not be a problem (they PROMISE the file will only have 50-odd rows ...  ).ADDED: BCP looks like a no go - I have datetime data. Is there a good way of taking this and doing some massaging? BCP says it doesn't handle datetime data. QUOTE from BOL - "Values in the data file being imported for computed or timestamp columns are ignored, and SQL Server 2000 automatically assigns values"Bulk Insert - trying to workaround using this, but ran into a "Bulk Insert: Unexpected end-of-file (EOF) encountered in data file." Error - is this because I am cheating by using excel files - is this purely for ascii text files?Given properly controlled evnironments that have controlled rollouts of code that should not change between environments, what is the "best practice" for this?EDIT TO ADD:forgot to add, there will be 4 sequential import, scheduled, and based on previous imprts successful execution. I am leaning to DTS, except for the migration issue.Thoughts, comments, general laughter ?  *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
uberman
Posting Yak Master
159 Posts |
Posted - 2004-06-15 : 07:51:53
|
| Not sure if this will help but it might give you some ideasHave you thought about accessing the excel sheet(s) directly as linked servers?Copy "OLE DB Provider for Jet" into the Index of BoL for the relevant page.I have done this sort of thing before, once linked you treat them as just another table source and can insert/transform with sql queries. |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-06-15 : 08:00:38
|
| Thanks uberman ... will look at, although instinctively, I think it may be a problem - we are receiving differently named files, although we can potentially rename the file each time. The files are being received from an external company....*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
|
excel file imports - best ways