Skip to main content

Notifications

Power Automate - Using Flows
Unanswered

Office Script convert csv to excel - Date format

(1) ShareShare
ReportReport
Posted on by

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

  • Yutao Huang Profile Picture
    Yutao Huang on at
    Re: Office Script convert csv to excel - Date format

    @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?

  • ishwariya11195 Profile Picture
    ishwariya11195 2 on at
    Re: Office Script convert csv to excel - Date format

    @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:

     

    ishwariya11195_0-1698410240012.png

     

    Output:

     

    ishwariya11195_1-1698410296307.png

     

     

     

  • Yutao Huang Profile Picture
    Yutao Huang on at
    Re: Office Script convert csv to excel - Date format

    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]}`;
    }

     

     

  • Jorge_ Profile Picture
    Jorge_ on at
    Re: Office Script convert csv to excel - Date format

    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.

  • Yutao Huang Profile Picture
    Yutao Huang on at
    Re: Office Script convert csv to excel - Date format

    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?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard

Featured topics

Restore a deleted flow