Exporting CSV pasting multiple rows in 1st cell when opened in Excel.

[SritejaGolla]

When I exporting the table visualisation to CSV file, on opening with Excel, I observed many values are pasted in 1st cell in the Excel.

Note: I used TOCSV dax to create a measure and used the same in table visualisation.

Is there any way that I can download the CSV with each row pasted in correct format

[Ritaf1983 · Accepted solution]

Hi @SritejaGolla 

 The issue arises because when you use the TOCSV function in a DAX measure, it concatenates all the values into a single string with a specified delimiter. When you export this to a CSV and open it in Excel, Excel treats the entire string as a single cell because it doesn't recognize the delimiters within the string as separating individual columns.

Unfortunately, there isn’t a direct way in Power BI to convert this TOCSV string into separate columns in the exported CSV via a DAX measure. The TOCSV function is intended to create a single string output, which is why Excel places it all in one cell.

Workaround:

Given this limitation, here are a couple of alternative approaches you might consider:

1. Use a Regular Table Visualization for Exporting

If the goal is to have each value in its correct cell in the CSV:

• Remove the TOCSV Measure: Instead of using a TOCSV measure, place the individual columns directly in the table visualization.
• Export the Table Visualization: When you export the table visual to CSV using Power BI’s built-in export feature, each field in the table will be exported as a separate column in the CSV file.
• Open in Excel: When you open this CSV in Excel, each value will appear in the correct column.

2. Manual Processing in Excel

If you must use TOCSV and need to separate values after exporting:

• Custom Delimiter: Ensure that the delimiter used in TOCSV is a character that isn’t present in your data (e.g., |).

• Export to CSV: After exporting the visualization, open the CSV in Excel.

• Text to Columns: Use Excel’s Text to Columns feature to split the values into separate columns based on your chosen delimiter.

  Steps in Excel:

  • Highlight the column containing the TOCSV output.
  • Go to the Data tab.
  • Select Text to Columns.
  • Choose Delimited and click Next.
  • Select the delimiter you used in TOCSV (e.g., |).
  • Click Finish.
    

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly