Data doesn't show up in Data Viewer

[OlivierBinette]

Trying to visualize a 1Gb csv file, the data doesn't show up:

FYI I quite like the open-source 'Tad' data viewer that is super fast: https://www.tadviewer.com/

[jennybc]

Is the csv file in question publicly available?

[OlivierBinette]

Hi @jennybc, the file can be found here: https://s3.amazonaws.com/data.patentsview.org/download/g_patent.tsv.zip Thank you!

[jennybc]

I note that it's a .tsv not .csv but honestly I don't know this feature well enough to know if that should matter. But here's what I see in the developer console:

Error: Conversion Error: CSV Error on Line: 8123387
Original Line: "D1000000"	"design"	"2023-09-26"	"Dispensing comb"	"S1"	1	0	"ipg230926.xml"
Error when converting column "patent_id". Could not convert string "D1000000" to 'BIGINT'
Column patent_id is being converted as type BIGINT
This type was auto-detected from the CSV file.
Possible solutions:
* Override the type for this column manually by setting the type explicitly, e.g. types={'patent_id': 'VARCHAR'}
* Set the sample size to a larger value to enable the auto-detection to scan more values, e.g. sample_size=-1
* Use a COPY statement to automatically derive types from an existing table.
  file=/Users/jenny/Downloads/g_patent.tsv
  delimiter = 	 (Set By User)
  quote = " (Auto-Detected)
  escape = " (Auto-Detected)
  new_line = \n (Auto-Detected)
  header = true (Auto-Detected)
  skip_rows = 0 (Auto-Detected)
  comment = \0 (Auto-Detected)
  date_format =  (Auto-Detected)
  timestamp_format =  (Auto-Detected)
  null_padding=0
  sample_size=20480
  ignore_errors=false
  all_varchar=0
    at jr.runQuery (/Applications/Positron.app/Contents/Resources/app/extensions/positron-duckdb/node_modules/@duckdb/duckdb-wasm/dist/duckdb-node-eh.worker.cjs:70:80327)
    at Pn.onMessage (/Applications/Positron.app/Contents/Resources/app/extensions/positron-duckdb/node_modules/@duckdb/duckdb-wasm/dist/duckdb-node-eh.worker.cjs:70:68102)
    at globalThis.onmessage (/Applications/Positron.app/Contents/Resources/app/extensions/positron-duckdb/node_modules/@duckdb/duckdb-wasm/dist/duckdb-node-eh.worker.cjs:70:91337)
    at dispatchEvent (/Applications/Positron.app/Contents/Resources/app/extensions/positron-duckdb/node_modules/web-worker/cjs/node.js:31:32)
    at MessagePort.<anonymous> (/Applications/Positron.app/Contents/Resources/app/extensions/positron-duckdb/node_modules/web-worker/cjs/node.js:152:25)
    at [nodejs.internal.kHybridDispatch] (node:internal/event_target:820:20)
    at MessagePort.<anonymous> (node:internal/per_context/messageport:23:28)

@wesm Does the above give you any insight into what's gone wrong?

[wesm]

It gives me some clues -- I can convert this to an issue and work out a solution.

[OlivierBinette]

Many csv readers fail to recognize the patent_id field as a string, since I think the first few million rows look like integers. "Tad" (https://www.tadviewer.com/) has been amazing as a tool for people to easily view these types of files.

[jennybc]

I know from my vroom life (an R package that reads csv files, among other things), we got a lot of type guessing improvement from peeking at evenly spread locations in the file and always including the last line of the file in that phase.

[OlivierBinette]

Something particular about this TSV file is that it quotes non-numeric field values, and leaves numeric values unquoted. Maybe the quoting can be used to help with the type inference. I typically read-in all fields as strings and parse types later. It avoids any issues.

[wesm]

I opened #5746, so I can close this discussion. Thank you for reporting this and providing helpful pointers on how to fix it!