Linker IT Software
Google
Web www.oraxcel.com
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 2048 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
 
Quote:
Gerrit-Jan,

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

 
Hi,
 
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