Table not found in statement

[mbnoimi]

After reading Using csv/text files as editable data source tutorial I tried to load my csv file I said in the tutorial but Base gave me the following error message:

Code: Select all

1: Table not found in statement [SET TABLE "contacts" SOURCE "contacts.csv;ignore_first=true;encoding=UTF-8"]

How I fix this issue?

Important:

• I'm using OOo 3.2.0 on M$ Windows.
• In the attachment you can find Base and csv files.

[r4zoli]

Your table in database is not a TEXT table. You probably created in design view.
You needs to create "contacts" table, in Tools>SQL... command window:

Code: Select all

CREATE TEXT TABLE "contacts" ("ID" INTEGER PRIMARY KEY, ........................)

.

[mbnoimi]

You probably created in design view.

Right, why I've to create the table from SQL command window? are there any differences between TEXT tables and table by design view?

[mbnoimi]

Is there any ability to create TEXT tables visually instead of SQL command Window?

[r4zoli]

Is there any ability to create TEXT tables visually instead of SQL command Window?

No, you can create text table only in SQl command window.

Right, why I've to create the table from SQL command window?

If you want to connect extarnal csv table this is the way.
See in HSQLDB documentation: http://hsqldb.org/doc/guide/ch06.html

[mbnoimi]

Thanks r4zoli, one more question please

I used CSV as data source because I want to remove duplicated rows from CSV file, although I successfully connect to CSV file but I could edit it because of primary key issue as I mentioned in the following post:
http://user.services.openoffice.org/en/ ... 80#p138680

Could you help me?

[evwool]

Has this been solved yet? If the writer only wants to remove duplicate rows from a CSV, wouldn't he be better off opening the CSV in Calc, pasting the Calc file into a BASE table, creating a SELECT DISTINCT query, creating a table from that query, putting that query into Calc then saving the new Calc file as a CSV?

[mbnoimi]

Has this been solved yet?

No this problem still exist and I couldn't find any solution for it

If the writer only wants to remove duplicate rows from a CSV

This is exactly what I need.

wouldn't he be better off opening the CSV in Calc, pasting the Calc file into a BASE table

As I mentioned in this link I couldn't open CSV file by Calc because it has many rows included so I forced to use Base instead but the problem I faced in Base is:

How I can remove these duplicates?

please if you've any solution for this issue post it in this topic or in my topic at Calc forum.

[evwool]

I don't know if this is what you wanted but first of all I slid the table into a clean database so it became an unlinked table (don't know if this was necessary or not), then I created a new table with (some of) the same fields as the old one but with its own Autonumber field. I appended the current table into that (by dragging it onto the table icon) so that the current primary key field (Email) became just a normal field. This may take away your Primary Key issue (didn't quite get what that was about.)

I think you are really saying that the rows aren't actually duplicates, only the telephone numbers may be duplicates. Is that what you meant in your other post by 'The consistent field is the telephone number field.'? And you want to delete one of those records which contains the same telephone number as another record? Presumably it doesn't matter which one you delete. Is that right?

My SQL isn't skillful enough to create the correct DELETE statement but the idea would be to delete the record with the highest Primary Key Number where the count of the Telephone number was more than 1. I've put in a qry which isolates the records with duplicate keys but I cant figure out how to get the Max CustID of each group without Base throwing a fit!
I presume that whoever set up the db in the first place used the email address as the Primary key field because they didn't realise that there were other ways (indexes) of ensuring that duplicate data was not entered into a field. Otherwise I can't think of any reason why someone would use such a changeable field as a Primary Key field. Once you have got rid of these duplicates, you could then, presumably, set up an index to ensure that duplicate telephone numbers (and emails) can't be added in future?

[evwool]

Personally, rather than deleting them, I'd have been tempted to have a second table, linked to this one, where those extra contact names/emails etc can be stored. A form for entering the main contact would have a table grid next to which would be a linked table grid for entering these extra contacts who would share telephone number and address but would have different names, perhaps department numbers, in the address, dialling extensions and emails. One of the things I really admire about base is this ability to put 2 table grids side by side in a single form.

[evwool]

If you can get at a PC with Access on it, Access is good at importing long csv files. You can slide a closed Access table onto a Base database's Table page and it will import well. You can append it onto a blank Base table so that you can get an Autonumber Primary Key. Another possiblity is to import chunks of the CSV into separate calc spreadsheets and then paste them, one by one, into your Base table.