[Kexi] importing mysql spreadsheets created with phpmyadmin

[jazz_johnson at verizon.net]

I have a simple database created with phpmyadmin and stored on a mysql server.
It basically just consists of  a group of yearly/quarterly tables: 
table_2007, table_2006, etc.
It's nothing fancy. Just basically a large spreadsheet done every year and 
stored on a mysql server to facilitate multi-user data entry/retieval.

I'd like to import these tables into a new kexi project.
I don't really want to keep the old spreadsheet structure --
I want something more like a database application -- which is
why I'm investigating Kexi.
I'd like to take some fields from the old spreadsheets and insert
them into my new kexi database tables, e.g. something like:

#!/usr/bin/perl -w
use strict;
use DBI;
my $db1 = "dbi:mysql:${database1};${host}";
my $dbh1 = DBI->connect($db1, $user, $password,
                      { RaiseError => 1, AutoCommit => 0 }
                      ) || die "Error connecting to the database:   
$DBI::errstr\n";
my $query1="SELECT `ID`, `$FIELD1`, `$FIELD2` , `$FIELD3` FROM `$table1` ";
my $sth1 = $dbh1->prepare( $query1 ) or die "Can't prepare SQL statement: ", 
$dbh1->errstr(), "\n";
$sth1->execute or die "Can't execute SQL statement: ", $sth1->errstr(), "\n";
while($row_hash = $sth1->fetchrow_hashref) {
        $FIELD1=$row_hash->{'Field1'};
        $FIELD2=$row_hash->{'Field2'};
        $FIELD3=$row_hash->{'Field3'};
#      printf("%s %5d %05d %05d %15s\n", $table2, $row_hash->{'ID'}, $FIELD1, 
$FIELD2, $FIELD3);
# <Here INSERT my FIELDS into my kexi tables.>
}
$sth1->finish();
$dbh1->disconnect() or warn "Failed to disconnect; ", $dbh1->errstr(), "\n";
exit;


Using kpogre, I see that the tables kexi creates are much more complex
than the simple spreadsheets I created with phpmyadmin, so I can't simply
copy the columns I want into the new kexi structures.

So I tried using Kexi's import wizard to import both structure+data.
But Kexi doesn't seem to understand the datatypes:
"The data type for Table2005 could not be determined. Please select one of the 
following data types: Byte, Short Integer, Integer, Big Integer, ...."


I'm not sure what I'm supposed to pick. ???
The fields in my  tables  are collated using latin1_general_cs with  types:
int(10), char(3), varchar(4), varchar(128), decimal(7,2), date, varchar(15)


Also, when I import my tables, will Kexi allow me to move data from selected 
columns into my new kexi tables, or will I have to use phpmyadmin to 
export just those fields which correspond to the fields in my new kexi tables?
I'm trying to avoid having to manually key in several years' worth of data and
risking typos/data corruption.


Also, with the old spreadsheets created with phpmyadmin, adding a new field
to an existing table was no problem, but with Kexi I see that if I modify the 
design of the table (even by adding a comment to a field) that all the data 
in the table is lost when the new structure is saved.  So if there are future
changes to Kexi, will future version always be backwards compatible, even if
I want to take advantage of new features without losing my data?



Thanks again.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 197 bytes
Desc: This is a digitally signed message part.
Url : http://mail.kde.org/pipermail/kexi/attachments/20070509/19f3a7e5/attachment.sig