Good Morning @inviridi,
Swift as usual, @jayeff!
I looked at the file you provided. There are couple things to note:
When format (json / csv) isn’t specified CrateDB tries to guess from the file extension. If this is not possible CrateDB falls back to JSON. In your case it tries to import the records from test.txt as JSON which will not work.
You can either rename test.txt to test.csv so that CrateDB can derive the format from the extension or explicitly define the format on the
COPY FROM with the
COPY <tablename> FROM 'file:///path/to/test.txt'
WITH (format = 'csv')
Imported data will not be validated or casted to the correct type. Therefore for imports CrateDB can only handle unix timestamp in milliseconds. This means instead of a ISO8601/RFC3339 timestamp (e.g. “2021-07-06 18:56:05”) you need to use the unix epoch time in milliseconds (e.g. “1625597765000”).
Commas are not an issue but I have mixed case columns. Is this a problem?
Make sure that casing of columns match between CrateDB and csv file. E.g. when you have “ts_mi,I_L1” in csv you should have columns “I_L1” and “ts_mi” defined for the CrateDB table. When using "“ts_mi,i_l1” you should have columns “i_l1” and “ts_mi” in CrateDB.
Also note the
RETURN SUMMARY at the end of the
COPY FROM. This will return additional information and error messages which helps with debugging an import.
I hope this solves your importing woes