June 28, 2006 at 5:38 pm
I need to upload data from excel and I need to be able to check the data before I load it into a table, but I can't use OPENROWSET because I can't access the Jet 4.0 OLE DB on the server (it's been disabled).
So how can I upload my data without just doing a strait upload? I need to make my key values on the fly (since I can't get SQL to autonumber my key column to save my life), and I can't seem to get the excel data into a stored procedure, so I can manipulate it with TSQL. The only option that works is strait from my file into my live table or some other pre-generated table (not even a temp table).
I'd rather not code any activex scripts, because I'm trying to learn TSQL and all my VB code skills are from VB 6.0. Don't want to try and learn two languages at once.
Any suggestions?
June 28, 2006 at 5:49 pm
Can you get it out of excel first? CSV or tab delimited that a BULK INSERT could handle?
If not, I don't know of any way to bypass the OLE DB provider and have SQL Server read an excel spreadsheet directly. I think even DTS and SSIS need the provider to do uploads.
June 28, 2006 at 6:37 pm
I have a VB.Net routine that imports excel into a temp DS and then I test for duplicates within my live DB before uploading the data. This uses the Jet engine though. I have done simple DTS uploads from Excel and this creates a new table within the DB. If you did that and then fired off a sp to look at the contents of the table, add, modify or upload the data, why wouldn't that work? The Books On-Line have given me a lot of references for importing and my VS Walkthroughs are another source, so most of my activity is a mix of VB and SQL sp.
June 29, 2006 at 9:18 am
I can export to comma delimited.
And if I can import it using a stored procedure, I'd probably use a table variable, so I can do all my checking and manipulating there. I just need to load it into memory or a temp table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply