Converting excel to csv -> date format

[andresayang]

Hello
I've try several things before asking.
I use fpspreadsheet to convert an excel worksheet to csv.

I want my date to have dd/mm/yyyy format in resulting csv file.

Up to now, I have try:

Code: Pascal  [Select][+][-]

1. CSVParams.FormatSettings.ShortDateFormat:= 'dd/mm/yyyy';
2. resworkbook.WriteToFile(outfilename, sfCSV);
3.  

If I run on linux, it is ok, but when I run on windows, my dates are dd-mm-yyyy.

So this is comming from windows locales setting.

How to force csv output to have correct format ?

(I also already try to assing resworkbookFormatSettings.ShortDateFormat:= 'dd/mm/yyyy'; before writing to csv, but it doesn't works)

Thanks

[paweld]

Code: Pascal  [Select][+][-]

1.   CSVParams.FormatSettings.ShortDateFormat := 'dd/mm/yyyy';
2.   CSVParams.FormatSettings.DateSeparator := '/';

[Thaddy]

Note it is better to store any dates in yyyymmdd format because you can then sort on date correctly.
Dates should only be converted to display format when.... well, displayed...
This is a common mistake though. So make yourself familiar with good practice and store in yyyymmdd which maps to both TDateTime and Unix datetime and has natural sort order, even as text. So it sorts without conversion.

[Dzandaa]

Hi,

As Thaddy says,

it is better to store any dates in yyyymmdd format because you can then sort on date correctly.

To store Date in a SQLite db, I use:

Code: Pascal  [Select][+][-]

1. var
2.  MyDate: TDateTime;
3.  StringDate: String;
4.  ...
5.  StringDate := FormatDateTime('YYYY-MM-DD HH:MM:SS', MyDate);
6.  

B->

[wp]

Code: Pascal  [Select][+][-]

1.   CSVParams.FormatSettings.ShortDateFormat := 'dd/mm/yyyy';
2.   CSVParams.FormatSettings.DateSeparator := '/';

Unfortunately there is a bug in the csv writer which causes this code to be ignored. Please use the current version in CCR (use SVN or download the zipped snapshot from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/ to get the latest version), or patch your current version manually as follows:

• Load unit fpscsv.pas (in source/common of the fpspreadsheet installation) and replace TsCSVWriter.WriteDateTime by the following code

Code: Pascal  [Select][+][-]

1. { Write date/time values in the same way they are displayed in the sheet }
2. procedure TsCSVWriter.WriteDateTime(AStream: TStream; const ARow, ACol: Cardinal;
3.   const AValue: TDateTime; ACell: PCell);
4. var
5.   s: String;
6.   sheet: TsWorksheet;
7.   cell: PCell;
8.   nf: TsNumberFormat;
9.   nfs: String;
10.   nfp: TsNumFormatParser;
11. begin
12.   Unused(AStream);
13.   sheet := FWorksheet as TsWorksheet;
14.   cell := sheet.FindCell(ARow, ACol);
15.   if cell = nil then
16.     s := ''
17.   else
18.   begin
19.     sheet.ReadNumFormat(cell, nf, nfs);
20.     if nf = nfCustom then
21.     begin
22.       nfp := TsNumFormatParser.Create(nfs, FFormatSettings);
23.       nfs := nfp.FormatString;
24.       nfp.Free;
25.     end else
26.       nfs := BuildDateTimeFormatString(nf, FFormatSettings);
27.     s := FormatDateTime(nfs, AValue, FFormatSettings);
28.     s := ConvertEncoding(s, EncodingUTF8, FEncoding);
29.   end;
30.   FCSVBuilder.AppendCell(s);
31. end;

This fixes your issue, but leaves another issue with long date format, when they contain a comma:

• Load unit fpsNumFormat.pas (in the same folder), find the implementation of procedure TsNumFormatParser.CheckSection.
• In the block following the instruction "if (section^.Kind * [nfkDate, nfkTime] <> []) or isMonthMinute then" add the line "nfs := ReplaceText(nfs, '","', ',')" after "nfs := GetFormatString".

[andresayang]

Hi,

@wp: thanks for your code.

This one:

Code: Pascal  [Select][+][-]

1.       CSVParams.FormatSettings.ShortDateFormat := 'dd/mm/yyyy';
2.       CSVParams.FormatSettings.DateSeparator := '/';

"Unfortunately there is a bug in the csv writer which causes this code to be ignored": Ah ah, ok that is the reason why it wasn't working. 

Thanks a lot for the patch for "my" needs, really appreciate it (I just download r9793, previous version I have was r9792)

Cheers

[wp]

I think I already use latest svn version (Downloaded on 2025-07-05)

No, you need the one of today because I just fixed it.

[andresayang]

@wp,

Yes, this is what I just notice.

Thanks again for your fix.

Cheers

[paweld]

You must remember that the slash ( / ) used in the ShortDateFormat property is a universal separator, and the separator you explicitly indicate in the DateSeparator property will be inserted in its place, and if you do not set it, the default value from the system for this property will be taken.

So when you use the / character in the date format, remember that it will be replaced by a character from DateSeparator. Of course, there is an exception to this - putting a character between quotation marks ( " )

Code: Pascal  [Select][+][-]

1. var
2.   fs: TFormatSettings;
3. begin
4.   fs := DefaultFormatSettings;
5.   fs.ShortDateFormat := 'yy/dd/mm';
6.   WriteLn(DateToStr(Now, fs));  // --> 25-08-07
7.   fs.ShortDateFormat := 'yy/dd/mm';
8.   fs.DateSeparator := '/';
9.   WriteLn(DateToStr(Now, fs)); // --> 25/08/07
10.   fs.ShortDateFormat := 'yy/dd/mm';
11.   fs.DateSeparator := '@';
12.   WriteLn(DateToStr(Now, fs)); // --> 25@08@07
13.   fs.ShortDateFormat := 'yy-dd#mm';
14.   WriteLn(DateToStr(Now, fs)); // --> 25-08#07
15.   fs.ShortDateFormat := 'yy"/"dd"/"mm';
16.   WriteLn(DateToStr(Now, fs)); // --> 25/08/07
17. end;

[andresayang]

Thanks,

But i do not think my trouble comes from this.
However, fix from @wp doesn't seems to works.

I have a second trouble (or maybe I miss understood) does the following:

Code: Pascal  [Select][+][-]

1.         outworksheet.CopyRow(0, 0, inworksheet);
2.         outworksheet.CopyRow(j, k, inworksheet);
3.  

Should result with "outworksheet" containing row 0 and then rows j to k from "inworksheet" or do I miss something to copy those row from "inworksheet" to "outworksheet" ?

Thanks

[wp]

However, fix from @wp doesn't seems to works.

"Doesn't seem to work" is not a clear error description...

The following code was tested, and it works for me as expected as expected:

Code: Pascal  [Select][+][-]

1. program project1;
2. uses
3.   FPSpreadsheet, fpsTypes, fpsUtils, fpsCSV;
4. var
5.   b: TsWorkbook;
6.   sh: TsWorksheet;
7. begin
8.   b := TsWorkbook.Create;
9.   try
10.     sh := b.Addworksheet('sheet1');
11.     sh.WriteDateTime(0, 0, 44000, nfLongDate);
12.     sh.WriteDateTime(1, 0, 44000, nfShortDate);
13.     sh.WriteDateTime(2, 0, 44000, 'dd"/"mm"/"yyyy');
14.  
15.     CSVParams.FormatSettings.LongDayNames[5] := 'thursDAY';
16.     CSVParams.FormatSettings.LongDateFormat := 'dddd", "yyyy"|"mm"|"dd';
17.     CSVParams.FormatSettings.ShortDateFormat := 'yyyy/mm/dd';
18.     CSVParams.FormatSettings.DateSeparator := '*';
19.  
20.     b.WriteToFile('test.csv', true);
21.   finally
22.     b.Free;
23.   end;
24. end.

I have a second trouble (or maybe I miss understood) does the following:

Code: Pascal  [Select][+][-]

1.         outworksheet.CopyRow(0, 0, inworksheet);
2.         outworksheet.CopyRow(j, k, inworksheet);
3.  

Should result with "outworksheet" containing row 0 and then rows j to k from "inworksheet" or do I miss something to copy those row from "inworksheet" to "outworksheet" ?

outworksheet.CopyRow(0, 0, inworksheet) copies row 0 of the outworksheet to row 0 of the inworksheet.
outworksheet.CopyRow(j, k, inworksheet) copies row j of the outworksheet to row k of the inworksheet.
And when inworksheet is not specified (it is an optional parameter) the copy operation is meant to stay inside the outworksheet.

[Nicole]

There is a nasty bug in windows. It holds my hands since TM under Delphi XE3 and never left me.
Sometimes the data is zero. The developer of TM (Delphi component) tried all to fix it, but could not do it.
It is Windows, which gives in very rare and absolutely unpredictable cases a zero-date or an empty date.
Nothing could catch it. No check for zero, no check for being empty, no validity, nor format attempts.

The moment you are ready for a new debug check, - it suddenly works again.
It is very rare and absolutely erratic. To my mind, it has something to do with the area or format specifications.

I tried many, many hours to work around, my old posts here show it.
I gave up. It is only every some weeks or even months. I click away the exception and try again. 

[andresayang]

Hello

@WP, so I Use your project1 example, I guess that on windows and on linux, I should obtain exactly the same results.

Unfortunately, I do obtain the following:
On linux:

2020*06*18
2020*06*18
18/06/2020

On windows:

thursDAY, 2020|06|18
2020-06-18
18/06/2020

So from windows results, using the following code:

Code: Pascal  [Select][+][-]

1.     sh.WriteDateTime(2, 0, 44000, 'dd"/"mm"/"yyyy');

should solve my troubles.

For the second point: outworksheet.CopyRow(0, 0, inworksheet) copies row 0 of the outworksheet to row 0 of the inworksheet, So I did reverse, as I wanted to copy row 0 from inworksheet to outworksheet

So I have now:

Code: Pascal  [Select][+][-]

1.         inworksheet.CopyRow(0, 0, outworksheet);
2.         for l:= j to k do inworksheet.CopyRow(l, 1 + (l - j), outworksheet);
3.  
4. // I want to copy row 0 from "inworksheet" to  outworksheet and row j to k of inworksheet in row 1 to (1 + j - k) of outworksheet
5. // Unfortunately, when I save the file to csv, the resulting csv is empty
6. // If you have 2-3 minutes, I upload excel file and my code in zip file
7. // The code is debugged on linux and cross-compiled to run on windows

Many thanks for your help

Cheers

[wp]

The main problem with the CSVParams.FormatSettings is that the Short|LongDate|TimeFormat elements are not analyzed by the numberformat parser any more, and a lot of too simple assumptions are made to find the CSV format strings for the built-in formats. One issue results in the fact that Linux seems to use the same strings for the LongDateFormat and the ShortDateFormat (in contrast to Windows), and this confuses the format detection.

Depending on what you want to do with the worksheets before and after the CSV exports, you could also try to modify the workbook's format settings.

Code: Pascal  [Select][+][-]

1. program project1;
2. uses
3.   SysUtils, FPSpreadsheet, fpsTypes, fpsUtils, fpsCSV;
4. var
5.   b: TsWorkbook;
6.   sh: TsWorksheet;
7. begin
8.   b := TsWorkbook.Create;
9.   try
10.     b.FormatSettings.LongDateFormat := 'dddd, dd/mm/yyyy';
11.     b.FormatSettings.ShortDateFormat := 'dd/mm/yyyy';
12.     b.FormatSettings.DateSeparator := '/';
13.  
14.     sh := b.Addworksheet('sheet1');
15.     sh.WriteDateTime(0, 0, 44000, nfLongDate);
16.     sh.WriteDateTime(1, 0, 44000, nfShortDate);
17.     sh.WriteDateTime(2, 0, 44000, 'dd"/"mm"/"yyyy');
18.     b.WriteToFile('test.csv', true);
19.   finally
20.     b.Free;
21.   end;
22. end.            

outworksheet.CopyRow(0, 0, inworksheet) copies row 0 of the outworksheet to row 0 of the inworksheet.
outworksheet.CopyRow(j, k, inworksheet) copies row j of the outworksheet to row k of the inworksheet.

Now I added to the confusion myself - this is nonsense... outworksheet and inworksheet must be exchanged. Here is the signature of the CopyRow method:

Code: Pascal  [Select][+][-]

1.     procedure CopyRow(AFromRow, AToRow: Cardinal; AFromWorksheet: TsWorksheet = nil);
2.  

But I think the real problem is that you are misunderstanding what CopyRow is doing: You want to copy all cells within a row, but it is only meant to copy the "row record" (row height etc). In order to copy all cells you must iterate over all cells in the source worksheet's range and call CopyCell:

Code: Pascal  [Select][+][-]

1.     function CopyCell(AFromRow, AFromCol, AToRow, AToCol: Cardinal;
2.       AFromWorksheet: TsWorksheet = nil): PCell; overload;

[andresayang]

Hi,

But I think the real problem is that you are misunderstanding what CopyRow is doing: You want to copy all cells within a row, but it is only meant to copy the "row record" (row height etc). In order to copy all cells you must iterate over all cells in the source worksheet's range and call CopyCell:

Code: Pascal  [Select][+][-]

1.     function CopyCell(AFromRow, AFromCol, AToRow, AToCol: Cardinal;
2.       AFromWorksheet: TsWorksheet = nil): PCell; overload;

You are right, and all is more clear now, I simply thought that it will copy the whole row contents (kind of copy/paste). I was iterating before and though this was directly implemented by CopyRow Procedure.
The main trouble is that documentation is not yet fully implemented so I did miss understood what CopyRow was exactly doing. (Please do not see any blame on this, I really understand that you are doing your best to develop fp_spreadsheet, and I thank you a lot for it)

Depending on what you want to do with the worksheets before and after the CSV exports, you could also try to modify the workbook's format settings.

Unfortunately, I did already try this using this code:

Code: Pascal  [Select][+][-]

1.   CSVParams.FormatSettings.DateSeparator:= '/';
2.   CSVParams.FormatSettings.ShortDateFormat:= 'dd/mm/yyyy';
3.   CSVParams.FormatSettings.ShortTimeFormat:= 'hh:mm';
4.  
5.   outworkbook:= TsWorkbook.Create;
6.   outworkbook.FormatSettings:= CSVParams.FormatSettings;

and the result was not as expected.

I'll first re-code my small stuff, and try to debug the date fomatting troubles.

However thanks for all,

Cheers