[Base] Linked Table from CSV

[wquatan]

I have an ODB with a table which is an external (linked-to) CSV-file.

I can’t find a way to force the Field-Type settings to be used for that file.
I have a problem with the Field-type determination.

For a certain column, the content may be numeric or text
Example
OK: When for that column in row 4 its text, the field-type is Text[VARCHAR]
OK: When for that column in row 10 its text, the field-type is Text[VARCHAR]
NOK: When for that column in row 270 its text, the field-type is Decimal[DECIMAL]
NOK: When for that column the rows 3301-3310 are text, the field-type is Decimal[DECIMAL]

As a result, in the last two examples, text-values get lost

How can I force the Field-Type ?

[PYS]

Hi

I had written a FAQ about it. It shows how to create the table structure and link the data file.

Maybe that will help you?

Regards

[Ratslinger]

@PYS Thank you much for the link. I would never have known without this. Don’t know if I would ever use but good to have in toolbox.

[wquatan]

Thank you !

I didn’t use SQL to link to a CSV probably the reason why I wasn’t able to modify the Field-Types (Field-Names / Types generated automatically, Names upon line 1)

I think your example will do what I need.

Is there a way to skip the first line in the linked-csv ?.
Mine has fieldnames (generated by the source application) in the first row.
For fields defined as Numeric, line 1 of course generates an error

[Ratslinger]

A field (column) in a table can only be of one type. It cannot be defined as different types depending upon the row. All data can be in a text field (varchar) and then dealt with from there.

Not sure what you mean by linked-to CSV file. You can CREATE a table (read only) from a CSV file but it is not really linked. The data cannot be modified in any way.

[wquatan]

My question was misunderstood. I know very well the type is the same for a column, but the problem is that the type determined automatically for the column concerned isn’t correct → see examples

With linked-to CSV is was indicating that the data of the table are stored in an external CSV-file (and not imported from a CSV-file)

[Ratslinger]

Having worked with Base for a few years, I know of no way to “Link” to a CSV file. If you know of a way could you please explain.

[wquatan]

Till now I have been using : New → Database → Select “Connect to an exsisting Database” → Choose for “Text”
It’s more flexible and easier then via SQL, but with the problem I described in the start of this Topic

[wquatan]

@PYS
I think your example will do what I need.

Is there a way to skip the first line in the linked-csv ?. Mine has fieldnames (generated by the source application) in the first row. For fields defined as Numeric, line 1 of course generates an error