Problems with date column when converting an excel file to CSV

[andre.f.pires]

Hey everyone,

I’m having problems when converting an excel file to a csv file.

The excel file contains the column “Date” witch has the format “dd/MM/yyyy”:

I’m using the “Read Range” activity and storing the output in a Data table, and then I’m using the activity “Write CSV” to generate the CSV file. At face value everything looks OK, but if we edit the file with something like Notepad++, we can see that some of the records don’t look correct:

I’ve messed around with different encodings, but so far no luck. Any idea what might be the problem? Might there be something wrong with the original excel file?

I’m also including the excel witch I used to generate the CSV file.

Thank you.

adagioFinal.xlsx (168.1 KB)

[prasath17 (Guru)]

@andre.f.pires

Might there be something wrong with the original excel file?

I see the same records which you highlighted in the original file itself.

If you see the below records closely…there are two formats…

one is d/M/yyyy(1/2/2021) and other one is d/MM/yyyy(13/02/2021) - you might have check the how the original file get generated.

Hope this helps…

[Shahab_Farooqui]

@andre.f.pires Your spreadsheet is not properly formatted, Please format all cells of your spreadsheet.

[andre.f.pires]

I apologize, don’t know how I’ve missed that.

I’ve been taking a look at the sequence and supposedly I’m making sure to paste the data in the format I want:

[andre.f.pires]

Right, so basically I think I know where the problem is happening but I don’t know why.

Just before I paste the data to my excel file I have 5 different data tables.

   -Parsys_code
  - Allotment_code
  - Room_code
  - Date
  - Action
  - Min_nights

I do this procedure for several hotels so before I Write Range, I check to see if the excel file exists. If it doesn’t I use Write Range, if it does I read range the file before hand and do a count of it so I know from witch cell to paste the next information.

I’ve found that the dates are incorrectly formatted every time after the first time I write to the excel file.

Here’s the excel file I’m reading:

Here’s the .csv generated from the file:

So basically, after the first time I write to the excel, the dates are always incorrectly formatted. Also, I tried outputting dtDate everytime just before the Write Range activity and it seems correctly formatted.

I apologize if this is confusing, if need be I can share my project.

Thank you.

[Shahab_Farooqui]

Format the column after every write range.

[andre.f.pires]

Could you please give me an example on how to do so?

Thank you.

[andre.f.pires]

Hey everyone, I fixed the problem.

Basically, after the first time I write to my excel file, I was using write range activities in Excel Application Scope. For some reason that was messing with the Date format. I just changed those to Write Range activities outside of Excel Application Scope and that seems to have fixed it.

Nevertheless, thank you for all your suggestions.