4

We have a file that looks like this:

Col1,Col2,Col3,Col4,Col5
"Hello,",I,",am",some,data!

It therefore has the following 'properties':

  • Comma-separated
  • Double-quote column delimiter
  • Commas in some of the columns

Now, I am not sure if it's actually possible to ingest this with PolyBase, but wondered if there was a way?

The error we are seeing at present is "Could not find a delimiter after quote".. which i guess is because after the double quote it is hitting what is an expected delimiter..

Here is our current file format, for completeness:

CREATE EXTERNAL FILE FORMAT Comma
WITH (FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS(
          FIELD_TERMINATOR = ',',
          STRING_DELIMITER = '"',
    )
)
hiroki
  • 434
  • 2
  • 15
m1nkeh
  • 1,337
  • 23
  • 45

2 Answers2

1

Specify it in hex instead. 

STRING_DELIMITER = '0x22'

(Based on the problem that someone described at the end of https://msdn.microsoft.com/en-au/library/dn935026.aspx )

Rob Farley
  • 15,625
  • 5
  • 44
  • 58
  • partially correct, but still i had issues with things, so went for the ORC format in the end, ty! – m1nkeh Aug 03 '16 at 13:17
1

Sorted this out in the end by adding an intermediary step to convert the file from csv to ORC format..

It's a bit clunky (as it leaves a mess of a copy behind), but the PolyBase then does work with the fileformat:

CREATE EXTERNAL FILE FORMAT Orc
WITH (FORMAT_TYPE = ORC)

works for now, until it is addressed by the product team: https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/10600132-polybase-allow-field-row-terminators-within-strin

m1nkeh
  • 1,337
  • 23
  • 45