Solved: Error while reading table: error message: Data bet... - Page 2

vigneswar17 · 12-28-2023 04:49 AM

Hi Folks,

I have a file sample.txt with the below content,

"501286 - Contract Info" "No Chain" "12/03/2023" "Products" "Fee Adj - Manual" 5000.00
"114173 - Contract Info" "No Chain" "12/03/2023" "Products" "Flat Fee Adj - Manual" 863.03
"114174 - Contract Info" "No Chain" "12/03/2023" "Products" "Flat Fee Adj - Manual" 95.89

I'm trying to read the file as an external table with one column raw_data_txt but I'm getting below error,

Error while reading table:
dba_raw_sample_t_external_tables, error message: Data between close quote
character (") and field separator.; line_number: 2 byte_offset_to_start_of_line:
7 column_index: 0 column_name: "raw_data_txt" column_type: STRING value: "501286
- Contract..." File: gs://ad-us-est4-01-gcs/invoker/app/que/file/sample.txt
Failure details:
- gs://adh-nz-dwh-us-
est4-01-gcs/invokerjob/apps/queue/files/sample.txt: Error
while reading data, error message: Data between close quote
character (") and field separator.; line_number: 2
byte_offset_to_start_of_line: 7 column_index: 0 column_name:
"raw_data_txt" column_type: STRING value: "501286 - Contract..."

Any suggestions to fix this are much appreciated.

kolban

The problem is the existence of the quote character in your data. The LOAD DATA SQL (by default) looks for quote characters in a special way. You can disable that. The following SQL worked for me to load your data:

load data overwrite `test.onecol` (
  raw STRING
)
from files(
  format="csv",
  uris=["gs://kolban-csv1/test1.csv"],
  quote="\000"
)

and for an external table:

create or replace external table `test.onecol` (
  raw STRING
)
options (
  format="csv",
  uris=["gs://kolban-csv1/test1.csv"],
  quote="\000"
)

View solution in original post

Error while reading table: error message: Data between close quote character (") and field separator