Excel Date format

Automation Studio
1

Hi

I have an excel sheet containing multiple rows which have different dates, say 9/3/2020-15/3/2020. I have declared the data type as datetime. I added another column to write the date into it if a condition is met inside the excel loop. Here are two issues I’m facing trying to implement this process:

// My date column is datearrival and column I’m writing to is Testdate
// Date format in the excel file is DD/MM/YYYY

  1. When I use if condition inside the excel loop as if datearrival.equals(9/3/2020), then some output, I don’t get the desired results. Every row fails this condition even though there are rows containing this date. Why is this happening? Is there any other condition which I should use or any specific date format that should be present?

  2. Instead of writing to a column in the same excel sheet, I wanted to create a datatable and export as an excel sheet. But all the columns created in the excel file have the format: general and not the data type I specified in the AE platform. Is there a way to export using the data types used in the AE platform? If the type in AE is str, datetime, int32 the excel cell should also be str, date and number…

Thanks for the help.

2

Hi,

Your use case will not be achieved by Excel Loop. Try using Advanced Loop Activity using Excel option

image

image

image

4

Hi Amit,

Thanks for the solution.
I do have another query though. I extracted data tables from a pdf and I got it in CSV format and the dates are in DD/MM/YY. However, when I use the file operation to convert the file to an xlsx format, the date format gets messed up to MM/DD/YY. Hence dates that come after the 12th become invalid. I manually saved the CSV as an xlsx to see if it works and it does. There is no error. But file operations activity doesn’t seem to work. Why is this so?

I thought I’ll use the ‘save as’ option in file operations but that works only for xlsx and word.

My system time settings are also set at DD/MM/YY.

5

Hi,

PFB, steps to extract date from Dummy PDF and the date was in DD-MM-YYYY format.
Extracted file was in .csv format and converted to .xlsx format used File operations

Step 1- Extract data using PDF Extractor
image

Step 2- Once the data is extracted as a .csv file , Use file operation activity
image

image
image1359×663 83.1 KB

Test run the process it will create a copy of csv file in xlsx format, As shown in the screenshot this saves the files in other format in my case it didn’t mess up the date column format.

Hope this helps.

Thanks,
Anshu

6

Hi Anshu,

The problem still persists for me. When I manually save the file in excel from csv to xlsx the issue doesn’t arise though.
While installing the AE CE version, my system settings were set to MM/DD/YY. After that I had changed the settings to DD/MM/YY. Could this be a probable reason for the error?