On 4 February 2011 00:08, Wayne <[email protected]> wrote: > How does one import a csv file into an empty table? > When I try to do this, it comes in as a new table > instead of filling an existing table with data. The > reason I bring this up is that I'm trying to change the > parameters of an existing (1000 entries) table to allow > combo boxes. However, if I change the settings, all > data will be deleted. How can I then import back the > deleted data (which I saved as a csv file) into this > revised table? The kexi_add_column script does not work > for me.
Hi Wayne, The script kexi_add_column would not take all cases into account. Indeed your idea of using CSV export/import would work as a temporary solution (end easier because 100% available in the GUI) but we do not have "append to existing table" option. By the way - I encourage to add such wish at bugs.kde.org, it sould not be hard to implement and I am willing to do that for Kexi 2.4.0.... Kexi 1.x will not see any new features or non-fatal bug fixes. So I propose temporary solution without using CSV. It requires executing SQL statements. The requirement is that the new design of your table should be compatible with the old design. What means: if you add a column, add append it as the last. And do not delete columns unless you agree on extra semi-manual work. 1. Create a new empty table using Kexi which is similar to the original one as explained above. Below I am assuming your old table is called tab1 and the new is tab2. 2. From command line dump the database using this command sqlite3 yourdb.kexi .dump > yourdb.sql 3. Open yourdb.sql (it's a text file) in good text editor, e.g. Kate. 4. Locate set of lines starting with: INSERT INTO "tab1" VALUES( 5. Select all the lines and copy to clipboard 6. Start a new text document and paste your clipboard contents. Use "Replace" function of your text editor, and replace INSERT INTO "tab1" with INSERT INTO "tab2" on each line (e.g. using "Replace All" in Kate). 7. Your statements may bo not compatible with tab2 you designed. E.g. if you added a new column, you need to add extra NULL argument to each row. What means that if you had INSERT INTO "tab2" VALUES(1, 'foo', 'bar'); you need to add a NULL argument: INSERT INTO "tab2" VALUES(1, 'foo', 'bar', NULL); You don't have to do that by hand. "Replace" function of the text editor can be used: Replace ); with , NULL); (e.g. using "Replace All" in Kate). 8. When you're done, select all the fixed lines and copy to the clipboard. 9. In the yourdb.sql file go to the last line of the file where you have COMMIT; line; _Before_ COMMIT; line, paste the clipboard contents. 10. Save the file changes. 11. Create database from the modified sql dump file yourdb.sql: sqlite3 yourdb-new.kexi < yourdb.sql Import errors will appear if your modified INSERT statements do not match the design of tab2 or if the syntax is broken. 12. Open yourdb-new.kexi in Kexi. tab2 should not contain data. If you inserted just NULL values in the new columns, do not be surprised you have no values in the new column(s) (e.g. those with combo boxes). In this case you'd need to select proper values in the data view of the table, or in a form dedicated to this table. If you want all-automatic conversion from combobox-less table to a combobox one, really advanced tool should be needed. You can file a wish on bugs.kde.org for such a "normalization" tool, but it should not be expected to appear very soon. That said, yes I think it would be very handy as also in my practice found tables that would require normalization but have a lot of data entered already... -- regards / pozdrawiam, Jaroslaw Staniek http://www.linkedin.com/in/jstaniek Kexi & Calligra (kexi-project.org, identi.ca/kexi, calligra-suite.org) KDE Software Development Platform on MS Windows (windows.kde.org) _______________________________________________ Kexi mailing list [email protected] https://mail.kde.org/mailman/listinfo/kexi
