Cannot export TSV nor copy them

[DynV]

I've looked at a few pages how to import TSVs into OO Base and the one that seemed to make most sense to me was to create a Base with Select database, currently the 1st step, choosing Connect to an existing database, currently the 3rd radio button, then Text (from the drop-down). I seemed to have done the process adequately as the end result is similar to what I get from OO Calc importing the TSV.

My 1st attempt to export that non-standard Base exported was through menu > Tools > SQL... and tried to use the command SCRIPT 'C:\file.sql', which I tried multiple paths and even redid it by 1st opening Base in administrator mode (which should squash any file writing permission concerns) to no avail.

I then use a standard Base, that is using HSQLDB, currently the 1st step choosing the 1st radio, which I want to do my main work with, and have the non-standard drop (as in drag & drop) the seeming successfully imported tables in its Tables section (of the standard Base). The wizard come up and there's a process to add a primary key but when it seems the copying will be done, there's an error message about the copying not being done. I open the table that I hoped would have been properly copied, and the definition seem to be adequate but there's no data. I then tried another dragging but that time the option is Append data (as opposed to the 1st try that was Definition and data), then match the fields and go through the wizard but I get a similar copying failure error message.

Oh! And I did start with the OO Calc import copy-paste the data into the standard Base but unfortunately not the whole data gets copied (only 285 of the 375 rows) and I tired copy-pasting from Calc just the missing data in the last row of the table but only the 1st row 1st column gets pasted that way.

Thank you kindly for your help

[Villeroy]

viewtopic.php?f=61&t=87872&p=481484#p412647

Download and open the database I've linked. Go to the tables section with 2 views and one "Export" table which represents a text file.
Copy one of the view icons, select the Export icon, paste and confirm the dialog without further intervention. This dumps a text file in the same directory as the database file.
"vAll" prints all records. "vReport" is filtered by a filtering form.

[Tutorial] Using csv/text files as editable data source.

[DynV]

I did as instructed and I end up with "export.csv" which look like a plain CSV. I'm not looking to export to the CSV format.

I have a TSV that I want to import in a standard OO Base. I did try the import the TSV to OO Calc then copy-paste its content to Base but as in the OP that failed. I then opened the TSV directly, the non-standard / non-HSQLDB way, hoping to export to the .sql format which I could import into a standard Base but I failed to do so. I then tried drag & dropping tables from the non-standard to the standard Base, which failed.

I have a couple .tsv and I want them imported into a standard Base DB.

[UnklDonald418]

In my experience import errors are almost always caused by text fields in the source file that are longer than the size allocated in the destination column/field.
If I recall correctly the Base import wizard defaults to VARCHAR(50). Look for the longest string in your source file and make sure the destination column/field is large enough to hold it. Depending on your data set, sometimes multiple columns/fields are involved.

[Villeroy]

You are free to define tab separators and tsv suffix among other things. There are thousands of different types of csv files out there. We can not build examples for all of them. HSQL will always export dates and times in ISO format, decimal numbers with decimal point.

[DynV]

donald: After you advice I decided to restart, thus go for the seemingly most recommended of importing flat-file DB, through OO Calc, so I did it again. For the table that did have > 300 char long, I trimmed it to be < 200 char as it seems the default varchar is seemingly 254 char long, and without editing the column types, it was already varchar. After the edit, copy-pasting from Calc worked fine. An issue is that all the columns were set to varchar during the copy-pasting but in reality 2 of them are in fact decimals and are required but if I edit it to make the corresponding change, it gives me an error that it can't and asks me if the column should be deleted then appended (which I refused).

For the TSV that don't have anything long, I think the longest string is 20 char long. I was properly imported, including the right types. The only problem is that the index includes a column that it shouldn't in addition to the primary key that the pasting wizard added. I've tried to edit it and have the inappropriate field set to nothing but trying to save it I get an error that the index name can't be found and in square brackets right after there's a drop command including that name. Should I delete the index then make a new one? I don't want to mess the table so I'd prefer to not do it trial & error.

The 1st paragraph is giving more of an issue, so please address it adequately before getting to the 2nd.

Villeroy: I cannot export the non-HSQLDB to SQL, the issue is exporting to SQL, I don'T want to export that non-standard DB to CSV or any other flat-file DB. I'm either very confused by what you're attempting to show me or you don't understand the problem, in which case I'd advise consulting the OP and my 2nd post in this thread.

[Villeroy]

Don't tell us what you don't have. If it is connectable with Base, there is certainly a way to run INSERT and UPDATE commands. My HSQL example is not related to any particular type of database. It is a HSQL example just because we can wrap a HSQLDB into a database document.
Until now, I have no idea about the data you have to deal with nor the actual database software you use.

[UnklDonald418]

An Embedded Base database allows you to easily Rename tables, so when doing something similar to what you are attempting I rename my original table to something like Table1_original so I know not to do any editing on that table.
Using copy and paste the Copy Table dialog in Base should be able to resolve most of your issues, plus it allows trial and error without disturbing the original table.
The second page (Apply Columns) of the dialog lets you choose which columns to copy, and what order they will appear in the new table.
The third page (Type formatting) allows you to choose the format of each of the columns. You should be able to convert the text fields containing numeric strings in the source table to Decimal with a specified precision and save them in the destination table.

Once you have the table(s) you need you can delete any extra tables you created. Then at Tool>SQL execute

SHUTDOWN COMPACT;

then close the database and the space used by the deleted tables will be recovered.

[DynV]

See attached for this thread this is what I mean in this thread by OO Base non-standard DB.

[Villeroy]

You can not do much with this kind of database connection. It is read-only, good enough to pull some data into office documents. HSQL can handle text tables with read and write access and enough built-in functions to convert weird strings into correct data types.

[DynV]

An isn't an SQL dump a correct data type, or the equivalent to? Can't you help me do it?

[Villeroy]

SQL dump from what? You need a database programm to generate an SQL dump or you need to write your own program.nly
Why SQL dump? A relational database can handle SQL dumps. Which database are you using?

[DynV]

Which database are you using?

Please see the image a couple posts ago.

[Villeroy]

Text(tsv) ---> {Text Driver} --> {Base} --> {Office Documents}
Text(tsv) <--> {HSQL Driver} <--> {Base} <--> {Input Forms} and {Office Documents}

--> read
<--> read+write

[DynV]

I don't see why OO Base with a text driver can't do an SQL dump. Oh well!