Export Data with different data format in CSV / XLSX (V8)

[rtimagine]

Hi there team,

I have found a problem exporting data. I have created some Custom SQL Reports and they all work fine (as i watch then in my screen). But when I export the data (with classic CSV format), or in the new V8 XLS format, it takes the value format of the database, and I want to export the data in another format that I can choose in my SQL script. For instance:

tabSales Invoice Item.qty as “Cantidad:Float/Sales Invoice Item:100”,

In the DB, tabSales Invoice Item.qty is in the format 1.7 but I want it to be exportes as float with 1,7. I can’t find the way to export the data fromstrong text “.” to “,”.

Another example:

tabSales Invoice.posting_date as “Fecha:Date/Sales Invoice:120”,

Here the posting_date is in YYYY-MM-DD by default but I want to change it to DD-MM-YYYY, if I choose the type in Date I can see it well in my report on screen (Cause the date format in ERPNext configuration is right), but when I export the data with CSV/XLS it shows the original type YYYY-MM-DD.

In summary, if I change the data type in the SQL scripts, it works well on reports on Screen, but when you export the data from ERP, it takes the original value from the database instead of the SQL.

Any idea about how to fix this?

I’ve been trying to fix it with pyexcel after the export, but this is not easy to manage and is not a good solution.

Kind Regards,

Rubén

[rtimagine]

In Summary, another way more simple to ask this:

Where can I change the format the csv or xls uses to export data? For:

• Change the dataformat to DDMMYYYY instead of YYYYMMDD
• Change the float value to “,” instead of “.”

Kind Regards,

Rubén

[rtimagine]

Hi there,

I have found this similar post:

Custom Report format Date

Dear All, I have a custom report and need to format the datetime from YYY-MM-DD to DD-MM-YYYY … I have tried to format with SQL format(date, ‘DD-MM-YYYY’) but when showing the report wrong format …any help? I have already setup the system to work on Portuguese Region and date format (DD-MM-YYYY) This is the SQL I have on the custom report SELECT name as “Fatura N:data:100”, nome_mesa as “Mesa N:data:150”, hora_atendimento as “Hora atendida:datetime:200”, status_atendimento as “Status:dat…

where rmetha says:<<Datetime formatting is done in the clientside (JS) and automatically chooses the date format settings of the user>>

And thats truth, it works well in the custom SQL reports if you chose the correct type in the SELECT (for instance Date instead of Data, or Float instead of Data), but when you export the data, then it exports the Raw data from the database always in YY-MM-DDDD and with “.” as separator in floats.

So, this makes very difficult to manage the information you export in all the countries that we use other data formats, or if you want to integrate this exported information in other applications.

Is it possible to make the CSV or XLXS export tool export the data in other formats by default, maybe hardcoding it?

Kind Regards,

Rubén