0

I am using ms excel 2007. and i convert one excel sheet which has date column in the format (DD/MM/YYYY) for example. 30/11/2014 when i convert this excel sheet to csv, the csv file the date column data changed like (MM/DD/YYYY). 11/30/2014

I am using the following coding to convert excel to csv.

xlSheet.SaveAs("sheet1.csv", 
FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, 
Local:=True, ReadOnlyRecommended:=True)

(But it is worked correctly in system which has excel 2010 version.) Can Anyone Help me what's going wrong with this?..

Sathish
  • 4,419
  • 4
  • 30
  • 59
  • @VladimirOselsky sir did its correctly work in excel 2010. not work in 2007 – Sathish Dec 24 '14 at 13:06
  • Vladimir is correct. There is a discrepancy on the machines between your date settings in Excel, and your short date settings in your Windows Control Panel regional settings. This is a common mistake. – Ron Rosenfeld Dec 24 '14 at 13:34
  • How do you know that the csv file date format changed? Did you open the csv file in Notepad? OR merely in Excel? If the latter, try "Importing" the file and specifying the date format of DMY when the text import wizard opens. – Ron Rosenfeld Dec 24 '14 at 13:37
  • open csv file in excel. its show like this 11-30-2014. but my system short date format is dd-MM-yyyy @RonRosenfeld – Sathish Dec 24 '14 at 13:38
  • Is this all happening on same machine? – Ron Rosenfeld Dec 24 '14 at 13:42
  • No. Excel 2010 work in another machine 2007 in another machine – Sathish Dec 24 '14 at 13:45
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/67622/discussion-between-sathish-and-ron-rosenfeld). – Sathish Dec 24 '14 at 13:47
  • @Sathish this is documented behavior, check the [SaveAs](http://msdn.microsoft.com/en-us/library/office/ff841185.aspx) reference, on the TextCodePage parameter. Excel uses the *system locale* for the date format – Panagiotis Kanavos Dec 24 '14 at 14:04

1 Answers1

3

This happens because of the Region Settings on your machine. If you go to Control Panel and change region setting to output date in dd/mm/yyyy format then you should be able to save date in desired format in .CSV. When you go to export data from excel it looks in region setting to get format for date field.