QuoteStyle.None doesn't work

[eniX]

Sample data look like following:

column_1;column_2;column_3;
A;1;1
B;2;2
C;"3;3
D;4;4

I would like to load this data and ignore quotes, beause quotes are in the data, it's not a field qualifier.  I tried to use following M code:

= Table.PromoteHeaders(
    Csv.Document
        (File.Contents(file_path),
        [
            Delimiter=";", 
            Columns=3, 
            Encoding=1252, 
            QuoteStyle=QuoteStyle.None
        ]
    )
)

 msdn says:"QuoteStyle.None (default): Field qualifiers are not used. "

but when I use it, the third row (C) is not separated properly. All the data resides in the second column ("3;3).

Why is QuoteStyle.None not working?

[v-jiascu-msft · Accepted solution]

Hi @eniX,

I have reported this issue to the Product Team: CRI 78250455. I will update the latest news here.

Best Regards,

Dale

[v-jiascu-msft]

Hi @eniX,

We can do it easily with the built-in function. Please refer to the following details.

Edit Queries -> Home ->Split Column function,

Use Split Column by Delimiter as below:

Best Regards,

Dale

[eniX]

Thank you,

I'm aware of the split function, but  I have csv data and I wanted to load it directly as csv and not as text and splitting after...what about QuoteStyle? Is it broken?

[v-jiascu-msft · Accepted solution]

Hi @eniX,

I have reported this issue to the Product Team: CRI 78250455. I will update the latest news here.

Best Regards,

Dale

[Ehren · Accepted solution]

Hi @eniX. If you look at the latest documentation in the product (by creating a new blank query and typing = Csv.Document), you should see an updated description for QuoteStyle. The MSDN documentation hasn't yet been corrected, but I'm following up on this.

Despite its name, the QuoteStyle option in Csv.Document controls how linebreaks within quotes are handled, not whether or not quote characters are supported.

To ignore quotes altogether, you'll need to use the Split Column approach suggested by @v-jiascu-msft.

Ehren

[eniX]

Thank you for clarification & the trick!

[eniX]

no one?

[JGroenouwe]

Hi,

I Encountered the same issue as You did.  My import is a folder where every day new data is added as a new text file. in that case we can see progress made in time. Because this is 'snapshotting' i'm sure nothing has been changed that would change the graphics in the past.

but for every record (its about Purchase Requisitions) there is a short text where they can fill in whatever they want. NOw the standard option in PowerBI will trigger the CSV import. When impporting the table data for the row below is imported in the correct columns until the TXZ01 column. Every other character after the " will be filled in that column. the split of the "| " is not used anymore. somehow the import of CSV cannot cope with an "

The table date is as following:

|BANFN |BNFPO|BSART|BSTYP|LOEKZ|STATU|FRGKZ|FRGZU|EKGRP|ERNAM |ERDAT |AFNAM |TXZ01 |MATNR |WERKS|LGORT|MATKL | MENGE|MEINS|BUMNG|BADAT |LPEIN|
|12345678 | 10|NB |B | |B | | |D11 |NAME|06.08.2021| |"GM6,R31_3"",900#,GK RJT OCT,A182M Gr.F5|B41660093|JGER| |PD | 1,00 |ST |0,00 |06.08.2021|1

The standard CSV Import looks like this:

Because i'm importing from a Folder, there are some extra functions like a Transform function, parameter and Sample files created. In the Transform File function I changed the import From :
 Source = Csv.Document(#"EBAN Parameter",[Delimiter="|", Columns=37, Encoding=1252, QuoteStyle=QuoteStyle.None])

To:      Source = Table.FromColumns({Lines.FromBinary((#"EBAN Parameter"), null, null, 1252)})

Then in the Import query i added a Split Column:

#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Table Column1", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), {"Column1.1", "Column1.2", ....[until]...., "Column1.38"}),

and then of course changed all the columns and so on to work again.

now it shows like:

CSV files allways are a pain in the ass when there are characters available in columns we can also use as delimiter or as Quotes for Text. Somehow after 3 decades of programming still there is no fundamental change in new software and I'm still creating my own imports as I did 3 decades ago. 

Hope this helps solving your issue. Unfortunately the standard CSV import won't work.