How to import multiple text files with quotation mark inside, from a folder

[tdxw]

Hi,

I have a folder where everyday new text files are saved.
I setup a powerquery, which imports all the files content in excel with filename in 1st column and the text rows in 2nd column.
Everything was going fine, but oneday there was one file where quotation mark was present inside and that created issue.

I found some fix to ignore quotation, but it was importing a specific file, not multiple files from a folder;
it was not importing the filename also.

Is there any way to tell powerquery to ignore quotation mark and import all the rows as it is; from multiple files in a folder?

Thanks

[tdxw · Accepted solution]

It seems I found a solution.
https://community.powerbi.com/t5/Desktop/ignoring-Line-breaks-within-quotes-while-importing-folder/m...

After opening the actual query, click "Transform Sample File", then click gear button on source (under applied steps) on right pane, then select open file as "Text file" from drop down.

This resolved the issue of " quotation.

[v-yalanwu-msft]

Hi, @tdxw ;
May be could try to CSV;

Power Query will treat CSVs as structured files with a comma as a delimiter—a special case of a text file. If you choose a text file, Power Query will automatically attempt to determine if it has delimiter separated values, and what that delimiter is. If it can infer a delimiter, it will automatically treat it as a structured data source.

And If you're dealing with large CSV files in the Power Query Online editor, you might receive an Internal Error. We suggest you work with a smaller sized CSV file first, apply the steps in the editor, and once you're done, change the path to the bigger CSV file. This method lets you work more efficiently and reduces your chances of encountering a timeout in the online editor. We don't expect you to encounter this error during refresh time, as we allow for a longer timeout duration.

Power Query Text/CSV connector - Power Query | Microsoft Learn

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tdxw]

Hi,

Thank you for the reply.

But I am actually importing multiple files from single folder. So, I want to "Get data from folder".

PQ is messing up if there is a " (Quotation character).