from Hacker News

Delimited files are hell– a comparison of methods to read bad files

by jasonpbecker on 1/22/23, 1:47 PM with 9 comments

• by jasonpbecker on 1/22/23, 3:34 PM

  I tried `polars` via Explorer from Elixir, and it shit the bed in an entirely different way:

  ``` df = Explorer.DataFrame.from_csv(filename = "my_file.txt", delimiter: "|", infer_schema_length: nil) {:error, {:polars, "Could not parse `OTHER` as dtype Int64 at column 3.\nThe current offset in the file is 4447442 bytes.\n\nConsider specifying the correct dtype, increasing\nthe number of records used to infer the schema,\nrunning the parser with `ignore_parser_errors=true`\nor adding `OTHER` to the `null_values` list."}} ```

  Note, I added `infer_schema_length: nil` assuming that the data type discovery via sampling was just less good in `polars`, since this would have it read the whole file before determine types, but it still failed.

• by af3d on 1/22/23, 2:43 PM

  Relying on undefined behaviour can't really be considered much of a solution. Any changes to one of those third-party libraries could possibly break your application without warning. I would suggest inserting a sanitization routine right there into the stack to parse and transform the data file accordingly. For the sake of posterity, emitting logs of every "questionable" entry along the way wouldn't be a bad idea either.

• by nuc1e0n on 1/23/23, 6:36 AM

  The authors assessment that using binary files would prevent such issues is flat out wrong. If a file is corrupt, as in his data, then undefined behaviour is all you can expect. No matter if the file format is text or binary based.

  The thing that would prevent such issues is validation of the data you accept.

• by jasonpbecker on 1/22/23, 1:47 PM

  A comparison of duckdb, PostgreSQL, pandas, readr, and fread when reading a delimited file with strange data.