Xlsx to txt file conversion issue in production machine

Help Something Else
, ,
1

Hi Team, I have an excel file with few Invoice parameters. The Invoice date in the excel is in dd/mm/yyyy format as expected. While writing the excel data into txt file, the date format is getting auto-converted to MM/dd/yyyy which is giving me the issue.

I tried with logic by converting the datatable into text file using Linq and in the syntax, i used DateTime conversion to change the format to expected format which is dd/mm/yyyy.

It is working as expected in my Dev machine. but in Prod machine, it is not working

Excel screenshot: & txt file screenshot is added below for reference.

image
image1094×483 30.4 KB

Please help me why am i getting the issue only in Prod machine? In Dev it is working as expected (dd/mm/yyyy)

I have also checked the Regional datetime settings in both Dev & Prod. It is showing Singapore time

Screenshot is shown below for reference

image
image1113×858 17.2 KB

image
image1027×1003 38.8 KB

I have tried so many logics to convert the date format. All are working only in Dev.

Please help me what changes need to be done?

Thank you in advance,
Eshwar

2

@eshwarsai_ks,

What’s the activity you are using to output the excel to text file?

3

@ashokkarale Thanks for the response.

I used write text file activity to fill the data inside. Also I used LInq query and changed the column value into dd/mm/yyyy format. like below and then writing the data, it worked but only in Dev machine not working in Prod machine.

Linq query I used to write in text file below:

String.Join(Environment.NewLine,dt.AsEnumerable().Select(Function(r)String.Join(“,”, r.ItemArray.Select(Function(c, i) If(dt.Columns(i).ColumnName = “InvDate”,If(String.IsNullOrWhiteSpace(c.ToString.Trim),“”,
If(DateTime.TryParse(c.ToString.Trim, Nothing),DateTime.Parse(c.ToString.Trim).ToString(“dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture),c.ToString.Trim)),c.ToString.Trim)))))

So, I have tried multiple methods to change the date format, including:

  • Applying macros in Excel to format the date before exporting.

  • Using Excel VBA scripts to explicitly set the date format.

  • Modifying system/regional settings on the Dev machine for date interpretation.

  • Using LINQ queries to transform the date.

  • Formatting cells directly in the Excel template.

  • Enabling “Preserve Format” selection in Excel.

  • All of the above methods are working correctly in the Dev environment.

  • The macro is now successfully running in Prod, and the date format is reflecting as expected. but that too only few files it is getting reflected but not all files.

4

@eshwarsai_ks,

Can you try Output Datatable activity to generate output string out of the Datatable and see if the behaviour persists.

1 Like
5

Thanks for the idea. I will try this and get back to you shortly. It will work in Dev machine. But when I publish the package and see the output in prod machine. it is showing the incorrect format only.

Let me try this way and check if it is able to come right format.

Thank you very much once again for the response.

1 Like
6

Hello @eshwarsai_ks,

The issue happens in ‘Prod’ because the system or app is using a different culture, likely en-US, even though regional settings show Singapore. This causes dates to be formatted as MM/dd/yyyy.

Fix: Set the culture explicitly in your code:

using System.Globalization;
using System.Threading;

Thread.CurrentThread.CurrentCulture = new CultureInfo(“en-SG”);
Thread.CurrentThread.CurrentUICulture = new CultureInfo(“en-SG”);

7

@eshwarsai_ks While reading the range in excel file update the Read Formatting to “Display Value” in properties panel and try with “Output Data table” to get the date format as visible in the excel in Text.