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
@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?
@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:
Without verifying with your actual environment/settings, here is just my guess:
function convertDateValue(originalDateValue) {
let array = originalDateValue.split("/");
return `${array[1]}/${array[0]}/${array[2]}`;
}
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.
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?