Forum Discussion
Helper IIIExporting a CSV file from a Power BI table
I have a query that creates a table view from joining two tables that are uploaded from a file folder to Power BI and transformed to the correct format. I would like to know the easiest way to save this table view as an Excel CSV file. It needs to export in the following format when opened with notepad (example of the headers and first record listed of many - basically this is the standard for CSV but the inverted commas need to sppear around each field and if null it will appear as ""):
"National Staff Id","NI Number","Title","Forename","Surname","Known As","Previous Name","Date of Birth","Gender Narrative","Pay Number","Employment Status","Work Type","Start Date","Date Left","National Staff ID (Manager)","Employer Name","Division CHP Name","Directorate","Department","Sub Department","Base Location Description","NOT USED - Work Email Address","Default Role","Contract Type Description","Contracted WTE","Job Title","Job Family","Job Sub Family","Main Email Address"
"1234567","ZZ12345678","Mr","Joe","Bloggs","","","01/01/1987","Male","N1234567","CURRENT","","01/01/2018","","","NHS TIMBUKTOO","Corporate Services","HR","Payroll","Invoicing","Timbuktoo Royal Infirmary","","True","Permanent","100","Nurse","NURSING AND MIDWIFERY","","[email protected]"
I am sure this is likely fairly easy in Power BI but not sure how to do it. I can test on my current table then open the CSV in notepad to check the format is correct.
Hi 83dons ,
To export a Power BI table as a CSV file with each field enclosed in double quotes and nulls appearing as empty double quotes (""), the easiest and cleanest method is to use an R or Python script within Power BI. Power BI itself doesn't natively export formatted CSVs in the exact structure you described—especially with forced quoting and custom null handling—so scripting is the way to go. Assuming you've already created your final, transformed table in Power BI (let's call it FinalTable), you can export it using the R script editor by selecting "Run R Script" from the Transform tab and pasting the code below. Replace the file path with your preferred location:
write.table( FinalTable, file = "C:/Users/YourUsername/Documents/export.csv", sep = ",", quote = TRUE, na = '""', row.names = FALSE, col.names = TRUE, qmethod = "double", fileEncoding = "UTF-8" )If you prefer using Python, use this version instead, placing it in the Python script editor:
import pandas as pd FinalTable.to_csv( r"C:\Users\YourUsername\Documents\export.csv", sep=',', quotechar='"', quoting=1, index=False, na_rep='""', encoding='utf-8' )This approach guarantees that each field is wrapped in quotes and nulls are shown as "", making the output match your exact Notepad-ready specification.
Best regards,
4 Replies
- DataNinja777
Super User
Hi 83dons ,
To export a Power BI table as a CSV file with each field enclosed in double quotes and nulls appearing as empty double quotes (""), the easiest and cleanest method is to use an R or Python script within Power BI. Power BI itself doesn't natively export formatted CSVs in the exact structure you described—especially with forced quoting and custom null handling—so scripting is the way to go. Assuming you've already created your final, transformed table in Power BI (let's call it FinalTable), you can export it using the R script editor by selecting "Run R Script" from the Transform tab and pasting the code below. Replace the file path with your preferred location:
write.table( FinalTable, file = "C:/Users/YourUsername/Documents/export.csv", sep = ",", quote = TRUE, na = '""', row.names = FALSE, col.names = TRUE, qmethod = "double", fileEncoding = "UTF-8" )If you prefer using Python, use this version instead, placing it in the Python script editor:
import pandas as pd FinalTable.to_csv( r"C:\Users\YourUsername\Documents\export.csv", sep=',', quotechar='"', quoting=1, index=False, na_rep='""', encoding='utf-8' )This approach guarantees that each field is wrapped in quotes and nulls are shown as "", making the output match your exact Notepad-ready specification.
Best regards,
83donsHi DataNinja777 thanks again for this, looks like its just the thing! I think I have managed to install Python well not getting the warning now in the query editor box for Python.
However, I changed the path which looks right but now its querying the name of the table/query? I changed it from what you had to what I have (Sheet1).
- 83dons
Hi DataNinja777 Solved that part, you need to use 'dataset' not Sheet1 clue was in the comment they put in!
So now its exporting ok but I have 6 inverted commas instead of 2 in the notepad view! I think the script needs altered slightly for the null values. The values that are non-null are displaying ok with quotes round them. Any ideas ?
- 83dons
Update DataNinja777 : removing the double quotes works for null values:
import pandas as pd
dataset.to_csv(
r"REMOVED",
sep=',',
quotechar='"',
quoting=1,
index=False,
na_rep='',
encoding='utf-8'
)Only issue with the final CSV file now is that date type fields are not pulling through instead I get "Microsoft.OleDb.Date" instead of expected e.g. "01/02/2023". Is there a way round this in the import properties above or do I have to change the formatting of my table columns from date to text or something?