Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Upload my XL data to the Oracle table (Read 2015 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Upload my XL data to the Oracle table
22.11.10 at 10:15:42
Upload my XL data to the Oracle table

I have done pretty well with this.
I have written quite a large XL application and I am happy with that.

But I am struggling with the syntax to upload my XL data to the Oracle table.

I have data in a range of A2:G61 on a worksheet. (Row 1 is only the header)

That is 60 rows of 7 columns each in columns A..G

I want to load them into my table MY_TABLE with the columns specified as below.

I have my InsertRecordset as below and 1 record did go into my oracle table.

But all the columns were empty.

Do I need to put this inside a For..Next loop somehow.

Can you send me a sample of how to do this.

Also, can I delete records from an oracle table with SQL*XL?

SQLXL.InsertRecordset Table:="MY_TABLE", Columns:="COLA, COLB, COLC, COLD, COLE, COLF, COLG", DataRange:=Range("$A$2:$G$2"), PromptOnError:=False, SortToStatus:=True, CommitFrequency:=1, Orientation:=1, Silent:=True, Feedback:=True

Nice to hear you managed to build a nice Excel application using SQL*XL.  
The problems you report with the InsertRecordset command is likely due to the range you have used. You have just given one row as A2:G2. Perhaps you wanted to give more rows like: A2:G62.
Apart from that I cannot see any problems. It should commit at the end but just to be sure, issue a commit after your insert. That could be a logical explanation as well for not seeing the data in the database.
On deletes. That is a little trickier. I have not built in any facilities to delete too easily. I regard it as a bit too dangerous if deletes would be allowed with a button click. Especially when recording macro's it does not make sense.  
However deletes are soo easy to do in a delete statement. Why not compile a list of delete statements and let SQL*XL execute those. That is very easy to automate.
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1