Forum Discussion
Exporting CSV pasting multiple rows in 1st cell when opened in Excel.
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
Hi SritejaGolla
The issue arises because when you use the
TOCSVfunction 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
TOCSVstring into separate columns in the exported CSV via a DAX measure. TheTOCSVfunction 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
TOCSVMeasure: Instead of using aTOCSVmeasure, 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
TOCSVand need to separate values after exporting:-
Custom Delimiter: Ensure that the delimiter used in
TOCSVis 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
TOCSVoutput. - 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
- Highlight the column containing the
- Remove the
1 Reply
Hi SritejaGolla
The issue arises because when you use the
TOCSVfunction 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
TOCSVstring into separate columns in the exported CSV via a DAX measure. TheTOCSVfunction 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
TOCSVMeasure: Instead of using aTOCSVmeasure, 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
TOCSVand need to separate values after exporting:-
Custom Delimiter: Ensure that the delimiter used in
TOCSVis 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
TOCSVoutput. - 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
- Highlight the column containing the
- Remove the