Forum Discussion

Mauro89's avatar
Mauro89
Icon for Super User rankSuper User
10 months ago
Solved

CSV to table not working from Lakehouse File section

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 se...
one lake
  • Mauro89's avatar
    Mauro89
    9 months ago

    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's avatar
nielsvdc
Icon for Super User rankSuper User
10 months ago

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's avatar
Mauro89
Icon for Super User rankSuper User
9 months ago

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's avatar
    nielsvdc
    Icon for Super User rankSuper User
    9 months ago

    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 ✔️.