What does this error mean: Required column value for column index: 8 is missing in row starting at position: 0

Question

I'm attempting to upload a CSV file (which is an output from a BCP command) to BigQuery using the gcloud CLI BQ Load command. I have already uploaded a custom schema file. (was having major issues with Autodetect).

One resource suggested this could be a datatype mismatch. However, the table from the SQL DB lists the column as a decimal, so in my schema file I have listed it as FLOAT since decimal is not a supported data type.

I couldn't find any documentation for what the error means and what I can do to resolve it.

Answer 1

What does this error mean? It means, in this context, a value is REQUIRED for a given column index and one was not found. (By the way, columns are usually 0 indexed, meaning a fault at column index 8 is most likely referring to column number 9)

This can be caused by myriad of different issues, of which I experienced two.

1. Incorrectly categorizing NULL columns as NOT NULL. After exporting the schema, in JSON, from SSMS, I needed to clean it up for BQ and in doing so I assigned IS_NULLABLE:NO to MODE:NULLABLE and IS_NULLABLE:YES to MODE:REQUIRED. These values should've been reversed. This caused the error because there were NULL columns where BQ expected a REQUIRED value.
2. Using the wrong delimiter The file I was outputting was not only comma-delimited but also tab-delimited. I was only able to validate this by using the Get Data tool in Excel and importing the data that way, after which I saw the error for tabs inside the cells.

After outputting with a pipe ( | ) delimiter, I was finally able to successfully load the file into BigQuery without any errors.