CSV to table not working from Lakehouse File section

[Mauro89]

Hi community,

I have a CSV file which I manually uploaded to a Lakehouse files section.

Neither creating a delta table directly or accessing the file from a Dataflow Gen2 does not work as the separation is not determined correctly.

BUT, if I import the CSV file via the import in Power BI Desktop, it works perfectly.

Any idea why this is the case?

Thanks!

[v-kpoloju-msft]

Hi @Mauro89,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @nielsvdc,  for his inputs on this thread.

Has your issue been resolved? If the response provided by the community member @nielsvdc, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[Mauro89]

Hi @v-kpoloju-msft,

sadly the provided tips do not work. Still not able to load this specific CSV file from OneLake. 
How to proceed if no proper answer is given or solution exists?

Besr regards!

[nielsvdc]

Please use a tool like vscode or Notepad++ to see what the encoding of the file is. This might give you a hint about how to solve your problem. If the encoding is not UTF-8 or the delimiters or not a commonly used delimiter, you probably need to configure the Power Query M code accordingly.

As we are not able to look with you at your specific situation, you unfortunately need to figure out the solution for your particular case.

[Mauro89 · Accepted solution]

The encoding is UTF-8, very standard. I guess there might me another issue which is just not known currently. For now as non of the mentioned options actually solved the issue I see the workaround using the inport directly in Power BI as only solution. 

Thanks and best regards. 

[nielsvdc]

@Mauro89, it is strange that Power BI Desktop automatically detects the delimiters and Dataflow gen2 doesn't, as they are both using the Power Query M engine.

One thing you can try, is to import the file into Power BI Desktop using Power Query M and then from the advanged editor copy the code to a Dataflow blank query and see if it works. Or just compare the code from Power BI Desktop and Dataflow gen2. In Desktop the engine might detect the delimiters and file encoding that may not be detected in the dataflow.

Hope this helps. If so, please give a Kudos 👍 or mark as Accepted Solution ✔️.

[Mauro89]

@nielsvdc excelent idea. In the meantime I also tried to take the CSV in a Dataflow but not from the Lakehouse rather then from my local file share. Also this way works. 

So it seems as only the way of getting the CSV located in the Lakehouse Files section does not work in the same way as with Power Query either in PBI Desktop or Dataflow.

If anyone has an idea why this is the case, feel free to comment.

Thanks!

[nielsvdc]

When you import a CSV file using a Dataflow from a lakehouse files folder, dataflow will generate the following steps:

1. Source > Authenticate to Fabric Lakehouses
2. Navigation > Filter workspace
3. Navigation 1 > Filter lakehouse ID
4. Navigation 2 > Filter Files folder
5. Navigation 3 > Filter CSV file
6. Imported CSV > Import the CSV document using Csv.Document function

When step 6 is not created automatically, then probably the CSV file has different encoding or delimiter than the default (encoding: UTF-8, delimiter: ",") and dataflow does not recognize one of them to be sure to make the step automatically.

In these cases you can create step 6 manually by using the code:

Csv.Document(#"Navigation 3", [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.None])

 You need to change the delimiter, encoding and quotestyle manually to meet the file's specification.

Check the possible options for Csv.Document parameters.

Hope this helps. If so, please give a Kudos 👍 or mark as Accepted Solution ✔️.