Trouble uploading single column csv to bigquery with split columns

0
votes

I'm trying to upload a dataset to bigquery so that i can query the data. The dataset is currently in a csv, with all the data for each row in one column, split by commas. I want to have the data split into columns using the comma as a delimiter.

When trying to upload using autodetect schema, the 10 columns have been detected, but are called 'string_0, string_1, string_2 etc' and the rows still have all the data in the first column.

When trying to upload by manually inputting the schema, i get these errors:

CSV table encountered too many errors, giving up. Rows: 1; errors: 1.

CSV table references column position 9, but line starting at position:117 contains only 1 columns.

On both occasions I set header rows to skip = 1

Here's an image of the dataset.

Dataset

Any help would be really appreciated!

1
sqlgoogle-bigquery
take a look at this: cloud.google.com/dataprep/docs/html/… - Jim L
Assuming you're creating the table using the GCP console, check that the File format is set to "CSV", "Schema" is set to "auto detect" and under Advanced options that "Field delimiter" is set to "Comma" and "Header rows to skip" is set to 1. - Nathan Griffiths
your CSV has invalid un-escaped data at the position the parser gives you the hint, check your file with closer look - Pentium10

1 Answers

0
votes

I see here a three potential reasons for the error you're hitting:

  • Source data CSV file structural problem - the file does not correspond to the RFC 4180 specification prerequisites, i.e. used untypical line-breaks(line delimiters);
  • Bigquery sink table schema mismatch - i.e. missing a dedicated column for a particular input data;
  • Bigquery schema type mismatch - parsing a table column that owns a type that differs from input one.

Please find also more particularities for Bigquery auto-detect schema method, loading CSV format data, that can help you to solve above mentioned issue.