none
Cloudyn - Eliminate or change thousands separator from csv export RRS feed

  • Question

  • Hi everyone,

    to fulfill our customer's request, we automatically export every day a csv file from a Cloudyn report and then import it in a file Excel and in a database.

    When it comes to export the Azure consumption (in USD), Cloudyn does a weird thing. Both the field separator and the thousands separator are a comma ",". So, when we try to import the csv file in Excel or inside our database, a number like 1,800 is split into two columns: 1 and 800. This is wrong as that's a unique field.

    My question is: how can I remove the thousands separator (eg: 1,800 is only 1800) or, alternatively, how can I change the field separator and set a different symbol than comma?

    Thank you in advance

    Monday, May 27, 2019 3:20 PM

All replies

  • Cloudyn exports all values surrounded by double quotes. That's why even though the thousands separator and field separator are comma, excel is able to open the file correctly. In most databases there's an option to specify that the fields to be loaded are surrounded by specific delimiter. If the DB you are using does not have this option, you can always run a global replacement on a CSV file to replace sequences of "," (three characters) with whatever delimiter you want.

    As a workaround you can try the following:

    • Export csv file, open it in Excel
    • Highlight all or only numbers. With right click on a mouse  
    • Press Format Cells
    • In the Format Cells menu chose Numbers.
      Check that "Use 1000 Separator (,)" is not marked. Press ok   
    • It should remove the separator: 
    Thursday, May 30, 2019 5:34 AM
    Moderator