[Kexi] Re: Importing data into table

[Jaroslaw Staniek]

On 4 February 2011 00:08, Wayne <iw at ukulele.com> 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)