Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to export NA strings to CSV using R Script?

I'm exporting country codes from Power Query through R Script using write.csv(). Problem is that I have correct text NA in dataset column which refers to country Namibia. I want to keep its semant...
Need Help
dax's avatar
dax
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous , 

I test this in my environment, I find that when I use write.csv directly in R, it will export data directly without missing data. When I use "write.csv(dataset,"C:\\test\\r export1.csv",na="")", it will convert NA to null, but you could add '' on NA to treat it as string.

Or you also could provide your simple sample data and your R script, then I will test this in my envrironment.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I have the following script

    let
    Source = Table.FromRecords({
    [country_code = "NA", country_name = "Namibia"],
    [country_code = null, country_name = "Undefined code"]
    }),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"country_code", type text}, {"country_name", type text}}),
    CSVWrite = R.Execute("write.csv(dataset, file = ""C:\\Temp\\write_csv_test.csv"", quote = TRUE, na="""")#(lf)output <- dataset",[dataset=#"Changed Type"]),
    #"Expanded Value" = Table.ExpandTableColumn(Table.SelectColumns(CSVWrite,{"Value"}), "Value", {"country_code", "country_name"}, {"country_code", "country_name"})
    in
    #"Expanded Value"

    Output file looks like:

    "","country_code","country_name"
    "1",,"Namibia"
    "2",,"Undefined code"
     

    "Expanded Value" also looks like:

    country_code country_name
    null Namibia
    null Undefined code

    It differs from your. I havePBi Version: 2.81.5831.821 64-bit  and R Open  3.5.3 .