Unanswered

Office Script convert csv to excel - Date format

(1) Share

Report

Posted on by Jorge_

Hi, I do use following script to convert a csv to an excel file using power automate.

function main(workbook: ExcelScript.Workbook, csv: string) {
 let sheet = workbook.getWorksheet("Sheet1"); 
 /* Convert the CSV data into a 2D array. */
 // Trim the trailing new line.
 csv = csv.trim();
 // Split each line into a row.
 let rows = csv.split("\r\n");
 rows.forEach((value, index) => {
 /*
 * For each row, match the comma-separated sections.
 * For more information on how to use regular expressions to parse CSV files,
 * see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753
 */
 let row = value.match(/(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g);

 // Remove the preceding comma.
 row.forEach((cell, index) => {
 row[index] = cell.indexOf(",") === 0 ? cell.substr(1) : cell;
 });	

 // Create a 2D-array with one row.
 let data: string[][] = [];
 data.push(row);
 // Put the data in the worksheet.
 let range = sheet.getRangeByIndexes(index, 0, 1, data[0].length);
 range.setValues(data);
 });

 // Add any formatting or table creation that you want.
}

However, when working with dates is incorrectly converting dates with format dd/mm/yyyy

When date is: 02/12/2021 -> It converts to 2021-07-12T00:00:00.000Z

But when date is 21/12/2021 0> It correctly converts to 21/12/2021

Do you know how can I edit the script to work correctly with dates with format dd/mm/yyyy?

Thanks

Categories:

Using flows

I have the same question (0)

All responses (5)

Answers (0)

Yutao Huang Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Just to make sure, in your first example (the incorrect one), does it convert 02/12/2021 to 2021-07-12T00:00:00.000Z or 2021-02-12T00:00:00.000Z? I assume it's the latter, right?

And did you set any number format for the cells that will contain the dates?

Was this reply helpful? Yes No
Jorge_ on at

Like (0)

Report
Copy link

Link copied!

Hi,
Thanks for your answer.

Yes its the later, 2021-02-12T00:00:00.000Z

I cannot set cell format as I received the .csv file by email, and Power Automate takes it to convert to Excel.

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Without verifying with your actual environment/settings, here is just my guess:

You'll need to set the custom number format to the target workbook your script is running against, not the original CSV file. I believe you can do that directly in your Office Scripts code by using range.setNumberFormat("dd/mm/yyyy"), where range is the cell you want to put the date value in.
If the original date value in the CSV file is in the "dd/mm/yyyy" format, you may need to first parse it and convert into a string in either "mm/dd/yyyy" or "yyyy-mm-dd" before setting into a cell. Based on my own testing, it seems these are at least the two formats that Excel can recognize as date values. And unfortunately, Excel won't treat input text in the "dd/mm/yyyy" as a date (unless the numbers happen to also make sense for mm/dd/yyyy) and won't apply your custom date format.
Here is one possible way to convert a string from "dd/mm/yyyy" to "mm/dd/yyyy":
function convertDateValue(originalDateValue) { let array = originalDateValue.split("/"); return `${array[1]}/${array[0]}/${array[2]}`; }

Was this reply helpful? Yes No
ishwariya11195 2 on at

Like (0)

Report
Copy link

Link copied!

@Yutao Thanks Yutao for the solution. I tried to apply the same solution suggested by you for getting the proper date as "dd/mm/yyyy" but result not as expected. PFA. Kindly help.
Office Script:

Output:

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@ishwariya11195 - I have a feeling the culprit is the letter "T" in the original date time data. If you can replace that "T" with a space " " before setting it to a cell, that might just work (probably even without the need to explicitly set the number format).

For example, from `2023-08-30T00:00:00` to 2023-08-30 00:00:00`.

Can you give it a try and see how it goes?

Was this reply helpful? Yes No