topic Re: error message: CSV table references column position in Talend Studio

error message: CSV table references column position

Anonymous — Mon, 16 Sep 2019 08:21:00 GMT

Ok, so I'm trying out Talend as it seems like the right option for what I need (getting some local DB from multiple locations and of multiple types back to a singular data warehouse). So I am just messing about to try to understand it. I had a local CSV file on my desktop and I have been trying to just insert it into BigQuery. Excluding the mime.type warning I am a bit stuck on the 6 column thing. I eyeballed the txt that was generated and there are a few lines with 201804 but they aren't at the top (halfway through and the full value is cut off). I am not sure why this is happening and I'm at the stage of just randomly changing CSV options thinking that might help. Sorry if this isn't the right place to get help but I am very newbish with this and I'm just trying to figure out if it's the right thing or not to use for my use case.

[statistics] connecting to socket on port 3747
[statistics] connected
[WARN ]: org.jets3t.service.utils.Mimetypes - Unable to find 'mime.types' file in classpath
Exception in component tBigQueryOutput_1_tBQBE (TestCSVtoBigQuery)
java.lang.RuntimeException: Job failed: BigQueryError{reason=invalid, location=gs://shared/biquery_bulk.txt, message=Error while reading data, error message: CSV table references column position 6, but line starting at position:20804 contains only 6 columns.}
at local_project.testcsvtobigquery_0_1.TestCSVtoBigQuery.tFileInputDelimited_1Process(TestCSVtoBigQuery.java:2259)
at local_project.testcsvtobigquery_0_1.TestCSVtoBigQuery.runJobInTOS(TestCSVtoBigQuery.java:2609)
at local_project.testcsvtobigquery_0_1.TestCSVtoBigQuery.main(TestCSVtoBigQuery.java:2459)
[statistics] disconnected

Re: error message: CSV table references column position

fdenis — Mon, 16 Sep 2019 11:48:22 GMT

hi,
can you share a pics of your job and of cvs file component.

Re: error message: CSV table references column position

Anonymous — Mon, 16 Sep 2019 23:39:20 GMT

Im not sure which screenshots you want but here are the first 2, probably shouldn't share the other configurations I suppose even if it's just a test.

Australia - Eastern states saleyard cattle indicators.csv

Re: error message: CSV table references column position

fdenis — Tue, 17 Sep 2019 08:54:09 GMT

try to remove csv option or set then as "\""
in java \is the escape char.
if it's no working give me 2 lines of your CSV file.
(if you are fluent in java you can click on code tab)

Re: error message: CSV table references column position

Anonymous — Fri, 20 Sep 2019 01:27:34 GMT

Sorry for the delay in replying. Nup still the same issue. I have managed to connect at one point (and pass in a bunch of empty rows) so it's definitely the content parsing. I just don't understand how to determine which part it is having a problem with as I don't think it's the first rows. I've attached the CSV here.

Australia - Eastern states saleyard cattle indicators.csv

Re: error message: CSV table references column position

fdenis — Fri, 20 Sep 2019 10:54:21 GMT

remove csv option you have no text enclosure.

or rebuild your job, it's working fine

Re: error message: CSV table references column position

Anonymous — Sun, 22 Sep 2019 23:59:36 GMT

I get the same error, I tried to change all the types to string like you had too and got compile errors about not being able to convert a float to string.

Re: error message: CSV table references column position

fdenis — Mon, 23 Sep 2019 09:54:19 GMT

can you run your job step by step. error may be link to your project configuration.

Re: error message: CSV table references column position

Anonymous — Sun, 29 Sep 2019 23:45:27 GMT

If I remove the 'tBigQueryOutput_1' it will run all the previous stages through fine. I can even find the file in the storage on GCP. It's just the last step which near as I understand it gets caught during parsing. I had made an error previously and it created all the rows in a table as blanks so my creds are fine too. It's this particular error

Re: error message: CSV table references column position

fdenis — Mon, 30 Sep 2019 08:49:28 GMT

so did you still have problems?

Re: error message: CSV table references column position

Caudalie_Tal01 — Wed, 20 Jul 2022 12:15:49 GMT

Hello,

After 3 years from the last answer, I had the same problem to load data from csv file in the table in bigquery.

It was because my table in bigquery didn't have exactly the same columns that my csv file.

So i have deleted the table in bigquery and the component tBiqQueryOutput have created the new table in bq.

🙂