CSV Append issues

edwinh · Posted: Wed Jan 14, 2009 10:00 pm Post subject: CSV Append issues

I'm trying to append csv data into an existing base table, with the same format. I run through the field matching dialogue, then get an error:
Warning! java.lang.NumberFormatException
and nothing is imported.

I've checked the format of the csv data, (using both calc and a text editor) and the field format of the base table. Everything look right, but the import just doesn't work.
_________________
Best Regards
Edwin H

MSPhobe · Posted: Thu Jan 29, 2009 6:17 am Post subject:

Idea 1) As a first step, try importing the data into a NEW table, rather than the more complex append-to-existing. If that "just works", but appending doesn't, at least you'll know that the data.... probably... doesn't really have number format problems.

Idea 2) Split the csv data into several smaller chunks. Try importing individual chunks. I'd make the first chunk just 5 records long. If some chunks DO import, and others don't, you know a lot.

Idea 3) Maybe something is going wrong with your spec of what should go where. You haven't, for instance, overlooked a simple integer-type primary key field, present in the data base you're merging to, absent from the csv, have you, for instance?

Idea 4) Look at primary key issues. Your database table probably (must?) have one. As the append process tries to import the data from the csv file, is the primary key field filling "nicely"?

Good luck....

Tom

(For an entry point to several CSV articles. try....

http://sheepdogguides.com/fdb/opof1csv.htm

mvbauer · General User Joined: 03 Feb 2009 Posts: 6

First off, great suggestions MSPhobe. I think that should help edwinh find the problem.

I have a similar question. I copied a CSV file and pasted into a Base table. Then I updated the CSV file and want to UPDATE the Base table. I tried a copy and paste again using the Append option but that didn't work. I don't get the same error message edwinh but rather a message that INSERT has failed. I assume that this is because the paste is trying an INSERT operation but since the primary key already exists it flakes out. I was so hoping that it would be smart enough to prompt me to UPDATE or INSERT into new row but that didn't happen.

Is there a simple way to UPDATE the information in the Base table with the data from a CSV file? Again, the CSV data will have rows with the same primary key which can be used to correlate the data between the CSV and the table rows.

The only solution that I could come up with is to write a macro to manually parse the CSV and do an UPDATE call manually for each field.

Thanks for any help.

RPG · Posted: Tue Feb 03, 2009 5:53 pm Post subject: howto use csv file

Hello

This story is only important for you if you have to insert a CSV every week. For one time this is not important
In chapter 6 of the manual of HSQLDB they describe hoe to use CSV files. HSQLDB makes there a kind of tables.
What I now write is not complete so you have to read the manual.

1 Make a table
2 Make how HSQLDB the csv file have to see and connect the file to HSQLDB
3 There two command for connecting and disconnecting.

There are some more rules to remember The csv must be in the same map as the database.If you edit the file as table in OOo there change more that you think. That is not a problem but remember it.

There are also good things to tell. Once you have connect the file you can use all SQL command there are, as far as I know. That means make a sql command that do the update of the real table. To make clear that it is not difficult I add all code I have made for this, with comment.

I think that using this method is quicker then with macros. Maybe you can use the macros to insert the sql code. I do like macros but they are much harder to learn then SQL code. Also SQL code is more flexible then macro code. The macro code is only to use in OOo. SQL code can be used for all SQL engines

Romke

adriand · Newbie Joined: 26 Jul 2009 Posts: 1

I had the same problem with appending CSV to an existing table.

The problem was caused because the table I was importing into had a unique key field that autonumbered each of the records in the database.

I added a column in the CSV file for this key field and manually numbered the additional records sequentially after the last record in the database

This allowed me to import - append without any problems, but it is a bit of a pain.

RPG · Posted: Sun Jul 26, 2009 2:43 pm Post subject:

Hello

If this is the first and last time you have to do this then you have nothing to what I have to tell.
One method can be: use calc in stead of the method of this thread.
Calc can make a serial number in a column and you past it in a table.
When you must do it more often, then I think there are more possibilities.
One method: copy it in a table change the number with a sequence and copy it in the you need.
You can all do it with SQL

I will not say there is a solution for your problem but if you have to it more then once describe your problem a little better

This kind of code can update one table with an other.
update "client" as b set b."Naam"=
(select "Naam" from "client_csv" as a where
a."ID" = b."ID")
where b."ID"=0
for more information about update search on this forum for Sliderule as author
I must be strongly advice try this not on any important table before you have secured test it. Otherwise you can erase a lot of your table and you wished to do it by hand.

Romke