Connect existing csv/text as editable data source

[Soundsailor]

Hello:

I'm trying to follow the tutorial here: http://user.services.openoffice.org/en/ ... 83&t=23260

I've successfully created the table using the following statement:

Code: Select all

CREATE TEXT TABLE "ECData" ("ID" INTEGER PRIMARY KEY,"GEOTYPE" VARCHAR(2), "GEOTYPE_MEANING" VARCHAR(400), "ST" VARCHAR(2), "COUNTY" VARCHAR(3), "PLACE" VARCHAR(5), "CONSCITY" VARCHAR(3), "CSA" VARCHAR(3), "MSA" VARCHAR(5), "MD" VARCHAR(5), "CENREG" VARCHAR(1), "COMMREG" VARCHAR(1), "GEO_ID" VARCHAR(20), "GEOGRAPHY" VARCHAR(1000), "SECTOR" VARCHAR(2), "SECTOR_MEANING" VARCHAR(4000), "NAICS2007" VARCHAR(8), "NAICS2007_MEANING" VARCHAR(255), "OPTAX" VARCHAR(2), "OPTAX_MEANING" VARCHAR(400), "FOOTID" VARCHAR(3), "FOOTID_MEANING" VARCHAR(400), "YEAR" VARCHAR(4), "YEAR_MEANING" VARCHAR(400), "ESTAB" INTEGER, "ESTAB_F" VARCHAR(3), "RCPTOT" INTEGER, "RCPTOT_F" VARCHAR(3), "PAYANN" INTEGER, "PAYANN_F" VARCHAR(3), "EMP" INTEGER, "EMP_F" VARCHAR(3), "NESTAB" INTEGER, "NESTAB_F" VARCHAR(3), "NRCPTOT" INTEGER, "NRCPTOT_F" VARCHAR(3));

Next I wanted to SET the table source using:

Code: Select all

SET TABLE "ECData" SOURCE "EC0700A1.dat;fs=|;ignore_first=true";

However I get the following error:

Code: Select all

1: bad TEXT table source file - line number: 1 java.lang.NumberFormatException: For input string: "N" in statement [SET TABLE "ECData" SOURCE "EC0700A1.dat;fs=|;ignore_first=true"]

The source table is delimited using the | character with column headings in the first row. I can access the table in read only mode when I set it up as an external data source. However I can't query the table to extract the data I need when it is in read only mode.

I'd appreciate any help you can give.

[Villeroy]

Seems to be a string "N" where there should be some number.

[Soundsailor]

Could that be a problem with the CREATE TEXT TABLE command? I used the table definition file supplied with the table for the column names and data types. I used INTEGER for the data type NUMBER in the definitions. Would DOUBLE be a better choice?

[Villeroy]

CREATE TEXT TABLE creates an ordinary, indexable database table with field types and everything. SET TABLE SOURCE reads the data into the database table. Invalid data won't be accepted.
Load the csv into a spreadsheet (where each cell can take anything) and find the inconsistencies.