can dbt seed be used with a Pipe delimited csv files?

user_16761731 0
Viewed 622

We have over a dozen tables we have built that are perfect candidates for dbt seed and it is working great. We do have two files with addresses with commas in them though. I tried to use a pipe delimited file but get a syntax error saying it found an unexpected '|'.

I have searched the internet, getdbt and stackoverflow and don't find any reference to possibly declaring a delimiter in the dbt_project.yml file. Can we use a pipe delimiter in the csv file instead of a comma? Thanks.

dbt
2 Answers

Just quote the values like so:

col_1,col_2,col_3
value,"value, with comma",another value
user_46919200

Unfortunately, this is an issue which I've brought up to the dbt team but has yet to be resolved.

See: dbt-core issue #3990

That issue thread has more details into the underlying issues with the agate library's csv function, kwargs, etc.

In the interim, Jeremy was helpful in giving some options which make it possible to parse "|" or other delimiter options via the dbt-external-tables package which you can find more information about here:

See: dbt-external-tables issue #72

The file_format property, used as the input to create external table statements, accepts a string of any length. So today, you could pass it:

sources:
  - name: my_external_source
    tables:
      - name: my_external_tbl
        external:
          location: "@my_stage"
          file_format: "( type = csv field_delimiter = 'aa' record_delimiter = 'aabb' )"

The package macros will template this out as:

create or replace external table my_external_source.my_external_tbl
  with location = '@my_stage'
  file_format = ( type = csv field_delimiter = 'aa' record_delimiter = 'aabb' )

Hope that helps, I find the support for "|" delimited files to be relatively weak in general since they seem to be only used heavily in certain industries (financial) or locales.

Will update answer if anything the above changes but this is current information as of dbt-core v1.0 & dbt-external-tables v0.1.2

user_30733400
Related