Apache OpenOffice (AOO) Bugzilla – Issue 76577
Add SQL export
Last modified: 2013-02-07 22:42:58 UTC
It would be helpful if SQL could be exported. The first row would be the column name, the following rows would result in an insert statement. For example at the moment I am preparing a table for iso 639 language codes: A B C D E 639-1 639-2T 639-2B 639-3 Name de deu ger deu German en eng eng eng English Would be great to have it as insert statements like: insert into languages ('639-1','639-2T','639-2B','639-3','Name') values ('de','deu','ger','deu','German'); insert into languages ('639-1','639-2T','639-2B','639-3','Name') values ('en','eng','eng','eng','English'); The table name must be configurable.
Your wish is not clear to me. Why export a single 'insert' statement? Or do you mean, that the whole table definition and all its insert statements is exported? Which syntax should be used? Please have a look at issue 58915. I guess, that it describes what you want.
I submitted an issue about SQL integration into the OOo Calc component. For a more extended discussion, see http://www.openoffice.org/issues/show_bug.cgi?id=66588. Although I do NOT know IF that one would help you, I nevertheless encourage you to take a look. Basically, that issue is about issuing SQL statements inside Calc on Calc Tables. I have NOT discussed the INSERT statement, because that gets slightly more complicated. Nevertheless, that could be extended sometime in the future (well, even the SELECT is NOT yet implemented in Calc).
Dear Regina, I don't know why you think about a "single" insert statements - please think about thousands. What I mean is instead of CSV export - export as SQL insert statements where the table name for the insert must be configurable or better the calc sheetname is used. The issue 58915 is very simular - but the discussion is going into another direction. As said - I want it like the csv export but with sql insert statements. Regarding discoleo: Issue 66588 is something completly different from my point of view.
I hope I get it: You want a tool, that generates a text file from the sheet in Calc in that kind, that the file contains the SQL statements to make a database table, which is similar to the sheet, if you will execute the statements with a database engine. This is a rough idea, which needs a lot of details think about, like range in the sheet, field type, primary key, SQL syntax. Nevertheless it is a valid requirement.
Please keep it simple - think about it like another form of CSV export. Primary keys etc. are a matter of the database design. - I only want to have a kind of export that writes SQL insert statements instead of CSV.
Well, I probably missed the point. However, IF you only want to have a pseudo-csv output (with that additional text), that is quite easy to obtain. 1. export everything you want as usual (as a csv text file with strings enclosed by "'") 2. install awk/gawk (on Unix already available, on Windows, go to http://www.sourceforge.net, search for the project Gnuwin32 -> it is free) 3. run the following gawk script 3.1. write this text into a new text file, e.g. "my.gawk.script.txt" { tmp = "insert into languages ('639-1','639-2T','639-2B','639-3','Name')" tmp = tmp " values (" tmp = tmp $0 ");" print tmp > "my.new.modified.csv" } 3.2. open the console window 3.3. type into the console: gawk -f"my.gawk.script.txt" "my.original.csv" 4. "my.new.modified.csv" will be the new, modified csv file That's it.
reassigning features and enhancements to user requirements@openoffice.org which will be the default owner for those tasks (was introduced some time ago)