PBI not understanding delimiter "," for csv file

[nok]

Hey!

I was connecting normally to a CSV file stored in the SharePoint folder. However, I noticed that the automatic extraction of this file now had a column that was previously called "ID" now called "NewID," and as a result, my PBI wouldn't update.

So I tried a simple test: accessing the old CSV, renaming the column to "NewID" just to check. However, when I tried to change the column name in the csv file back to "ID," PBI no longer understood my CSV's delimator and now groups all 7 columns in this CSV into a single column, as if there were no delimator at all. What could I have done to break this CSV?

The ID column is the first column of my csv file and the parameters used in the csv import are:

[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None]

[V-yubandi-msft]

Hi @nok ,
Could you let me know if your issue has been resolved or if you still need any more information? If you need further help, please let us know.

[V-yubandi-msft]

Hi @nok ,
I wanted to check if you had the opportunity to review the information provided by @Shubham_rai955 . Please feel free to contact us if you have any further questions.

Thank you and continue using Microsoft Fabric Community Forum.

[Shubham_rai955]

The issue described in your post is caused by double quotation marks around each field in your CSV file, a format not accepted by Power BI and other standard CSV parsers, which expect values to be surrounded by a single pair of quotes.

• Correct CSV format: "ID","Ev","Event",...

• Problem format (delimiters error): """ID""","""Ev""","""Event""",...

To Fix:

1. Find and replace double quotes:

   • Open the file in Notepad++ or another capable text editor.

   • Use the Replace function:

     • Find: ""

     • Replace with: "

     • Repeat multiple times if needed until only single quotes remain.

2. Check for field separators and preserve single quotes:

   • After replace, each value should be quoted like "ID","Ev",...

3. Save as UTF-8 (without BOM) as needed.

You do not need to process in Power BI, just fix the CSV manually with a bulk find-and-replace. This should remove the double quotes and restore the original delimiter format your Power BI model accepts, preventing further delimiter errors.

If your CSV export process is generating files with double quotes, check the export settings of your source application and ensure it uses standard CSV quoting.The main issue is that your problematic CSV now has every field surrounded by double sets of quotes (e.g., """ID"""), while a standard CSV should only have one set (e.g., "ID").

To fix this:

• Open the file in Notepad++.

• Use Replace: Find "" and replace with nothing, or with " (repeat until fields look like "ID","Ev",...).

• Make sure you don’t accidentally remove legitimate single quotes.

• Save as UTF-8 (without BOM) if needed.

There’s no direct Excel or Notepad method to bulk-fix this, so Notepad++ or similar bulk editors are best for correcting double quotes. This manual fix prevents Power BI delimiter errors and restores the expected format for your data model.

[Shubham_rai955]

It looks like when you renamed the column back to "ID," the CSV might have been saved with a different encoding or separator. Sometimes Excel replaces commas with semicolons depending on your regional settings, or it saves the file in UTF-8 with BOM, which Power BI can misread.

Try this:

1. Open the CSV in Notepad (not Excel).

2. Check if commas are still used as delimiters.

3. Save the file again using UTF-8 (without BOM).

4. In Power BI, re-import it using “Delimiter = ,” instead of detecting automatically.

That should fix the issue.


Please kudos and accept as a solution if this solves the issue.

[nok]

Hi, @Shubham_rai955 thanks for the quick reply!

I opened the old file (which is giving the error) in notepad, as you mentioned, and noticed that it has two quotation marks surrounding each text, just like that: ""ID"". In the files that don't give the error, there's only one quotation mark surrounding the text. This is probably the source of the error.

But this file is already in UTF-8 (without BOM). Is there any other format that removes these double quotes it's showing now? or something I can do in the csv to return it to the original delimator format (without having to do any processing in PBI)?

Example of how the file worked:
"ID","Ev","Event","Level","Process","Date","Country"
"066","Hojker","A","Not validated","Pending","02/02/2024, 16:51","Turkey"

Example of how it looks now (raising a delimiter error):
"""ID"",""Ev"",""Event"",""Level"",""Process"",""Date"",""Country"""
"098,""Allen Hoj"",""Ok"",""Process"",""Vasp"",""21/01/2025, 02:07"",""Chile"""