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
Create Insert Statements (Read 4089 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Create Insert Statements
02.03.06 at 11:36:23
 
Customer Tarrif asked the following question.
 
Question:
I am looking to convert a CSV file (from MS Excel) into SQL code so I can copy and paste it into a tool for MySQL that my webhost provides.  
My webhost has already told me that 3rd party programs  probably won't be able to connect to their MySQL database for security reasons.  
Can SQL*XL read an Excel file and output the SQL code into a text file so I can copy-paste it to my webhost's tool?
 
Answer:
Yes it can!
SQL*XL has a function called SQLCreateInsertStatement that you can use to create an SQL insert statement. The function takes the table name, the range where the column names are stored and the range where the data is. It will display the insert statement as a result.
 
In the current version (SQL*XL 4.0.40) this works well for Oracle databases (it is called CreateInsertStatement in this version). I have made a small modification to make it work for all database types and renamed it to SQLCreateInsertStatement. When you are connected to the database, SQL*XL will pick the correct insert statement syntax. However since you cannot connect to your database I have added an extra parameter to specify the database type. Use "MySQL", "Access", "Oracle", "SQL Server" or "ODBC".
 
Example:
=sqlxl.xla!SQLCreateInsertStatement("MyTable",$A$1:$B$1,A6:B6,"mysql")
 
I have added a spreadsheet to this topic that you can use as an example.
Back to top
« Last Edit: 02.03.06 at 16:22:24 by Gerrit-Jan Linker »  

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