none
Detecting Delimiter RRS feed

  • Question

  • Hi guys

    I'm creating a workbook that will consume any text file, detect the delimiter and then run a Table.Profile.  However I'm at a loss as to how I can the trigger delimiter detection that PQ performs when first connecting to a text file.  Suggestions?

    I understand this may be possible in Excel 2016 by leveraging the PQ object model but unfortunately I'm stuck in 2013.

    Thanks,
    Simon

    Friday, August 19, 2016 4:29 PM

Answers

  • Hi Simon. The information we use to drive delimiter detection should be available via the M binary result returned by File.Contents/Web.Contents. If we detect that the file is possibly a text/delimited file, the binary result should contain a number of metadata fields that start with the prefix "Csv.".

    Ehren

    Tuesday, August 30, 2016 7:14 PM
    Owner
  • Ehren was thinking exactly what I was thinking, but when I tried to create an example like

    = Value.Metadata(File.Contents("\\jeffreed-dev6\c$\SampleData\regular.csv"))

    I found that the same metadata that PQ is using to decide which delimiter to use is not available. It turns out it is a problem in the way the metadata is being added.  We are looking at ways to make this available, but the work isn't booked yet.

    Very sorry, but the O16 VBA Object Model doesn't add any help here. 

    Right now there isn't a way to access this information

    -jeff

    Thursday, September 1, 2016 10:09 PM

All replies

  • Hi Simon. The information we use to drive delimiter detection should be available via the M binary result returned by File.Contents/Web.Contents. If we detect that the file is possibly a text/delimited file, the binary result should contain a number of metadata fields that start with the prefix "Csv.".

    Ehren

    Tuesday, August 30, 2016 7:14 PM
    Owner
  • Ehren was thinking exactly what I was thinking, but when I tried to create an example like

    = Value.Metadata(File.Contents("\\jeffreed-dev6\c$\SampleData\regular.csv"))

    I found that the same metadata that PQ is using to decide which delimiter to use is not available. It turns out it is a problem in the way the metadata is being added.  We are looking at ways to make this available, but the work isn't booked yet.

    Very sorry, but the O16 VBA Object Model doesn't add any help here. 

    Right now there isn't a way to access this information

    -jeff

    Thursday, September 1, 2016 10:09 PM
  • Thanks for the reply guys!
    Saturday, September 10, 2016 10:49 PM