Macro button to export excel spreadsheet as SQL file

You might find this helpful in getting started

http://exceluser.com/formulas/msquer...ional-data.htm

i think this is not what i need.
ex: i have in one spreadsheet 5 columns (SITEID, PM, JP NUMBER, SEQUENCE) with 9000 rows each.
so, I think i should created a insert into statement in order to create a sql file, but i want a button to press on, which can take all this information into one sql file.

can you support me with that?

well, I think it's not clear what you mean by sql file...
File with sql extension e.g. your_file.sql is actually a text file woth some sql statement in it. However this sql statement can be different type - e.g. to retrieve data (i.e. SELECT), insert data (INSERT), delete, etc.
In any case you (and we) need info for your file and also for the DB/table.
Note that you can interact with DB directly from excel using ADO, no need to create sql file and execute it afterwards...

Buran
I will try to upload my file here,
But, in case I want to upload a table information to Oracle SQL from excel
I have column name and the values. What should I do?
it would be possible to create a button to extract the file as SQL? or should I just write a insert statement with columns name and mentioning the values? Because I have a table with 5 columns and thousands of rows.

Please support

This is the formula I developed

Insert into Oracle.Location (SITEID, FAMILY, SEQUENCE, DESCRIPTION) values ('blck 18','alfa','90','beginner')

is it correct?

"Location" is the spreadsheet,
"SITEID" and the others are the columns
"blck 18" and the others are the values in the columns

I wonder if I should change the Oracle as the SQL software to dbo.

eg: insert into dbo.location or insert into [dbo].[Location]?

Hi
sorry for late reply.
You have two options:
one is to create text file with sql extension which will have INSERT statements, one per every row
second is to use ADO and execute sql command that will insert the data in your Oracle DB. Here is example that does the same with Access DB. http://www.excelforum.com/showthread...5170&p=4032216
Note that you will need to change the connection string based on what driver e.g. ODBC driver you have installed. You can google the exct connection string.

Regarding your INSERT statement above - you must have ; at the end e.g.

Insert into OracleTable (SITEID, FAMILY, SEQUENCE, DESCRIPTION) values ('blck 18','alfa','90','beginner');

Repalce OracleTable with real table name in your DB

Originally Posted by Kyle123

@buran, what's wrong with copy and paste?

@Kyle123 - don't know what you mean

Ah, I see what you mean. Nothing wrong in that respect if it's one time task and OP has access to some sql editor/db management tool.
if it's one time task he can also export data as csv and import the data from there (at least in MySQL and Access it's possible and I believe same is true for oracle)

Why do you need to use CONCATENATE? It's the single most useless function in Excel (I reckon).

What "isn't working"?

Try:

=CONCATENATE("insert into ORACLE.LOCATION (FIELDNAME, SITEID, LOCATION, DESCRIPTION, (M)TYPE, PARENT, FAILU"&"RECODE DESCRIPTION, FAILURECODE, SAFETY CRITIC"&"AL (FL010), TAG CRITICALITY (FL08), EX REGISTER (FL02), ANGILLEVEL, GLACCOUNT, ANGENGSYSID, SYSTEMID, (M)STATUS, ANGDRAWNUMBER, GRID REF (FL09), PHYSICAL AREA (FL03), MAINTENANCE JUSTIFICATION, ADOPTED STRATEGY (L02)) CONCATENATE ('"&B4&"','"&C4&"','"&D4&"','"&E4&"','"&F4&"','"&G4&"','"&H4&"','"&I4&"', '"&J4&"', '"&K4&"', '"&L4&"', '"&M4&"', '"&N4&"', '"&O4&"' '"&P4&"','"&Q4&"','"&R4&"', '"&S4&"','"&T4&"','"&U4&"','"&V4&"')")