Converting excel to csv -> date format
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
andresayang · 2025-07-07 22:16

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
CSVParams.FormatSettings.ShortDateFormat:=
'dd/mm/yyyy';resworkbook.WriteToFile(outfilename, sfCSV); 

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 · 2025-07-08 04:40

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
CSVParams.FormatSettings.ShortDateFormat := 'dd/mm/yyyy'; 
CSVParams.FormatSettings.DateSeparator := '/';

----------------------------------------------------------------------
Thaddy · 2025-07-08 04:46

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 · 2025-07-08 10:52

Hi,

As Thaddy says,

Quote 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var MyDate:
TDateTime; StringDate: String; ... StringDate := FormatDateTime('YYYY-
MM-DD HH:MM:SS', MyDate); 
B->

----------------------------------------------------------------------
wp · 2025-07-08 11:17

Quote from: paweld on July 08, 2025, 06:40:58 amCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
CSVParams.FormatSettings.ShortDateFormat := 'dd/mm/yyyy'; 
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
codeCode: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i { Write
date/time values in the same way they are displayed in the sheet
}procedure TsCSVWriter.WriteDateTime(AStream: TStream; const ARow,
ACol: Cardinal;  const AValue: TDateTime; ACell: PCell);var  s:
String;  sheet: TsWorksheet;  cell: PCell;  nf: TsNumberFormat;  nfs:
String;  nfp: TsNumFormatParser;begin  Unused(AStream);  sheet :=
FWorksheet as TsWorksheet;  cell := sheet.FindCell(ARow, ACol);  if
cell = nil then    s := ''  else  begin    sheet.ReadNumFormat(cell,
nf, nfs);    if nf = nfCustom then    begin      nfp :=
TsNumFormatParser.Create(nfs, FFormatSettings);      nfs :=
nfp.FormatString;      nfp.Free;    end else      nfs :=
BuildDateTimeFormatString(nf, FFormatSettings);    s :=
FormatDateTime(nfs, AValue, FFormatSettings);    s :=
ConvertEncoding(s, EncodingUTF8, FEncoding);  end; 
FCSVBuilder.AppendCell(s);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 · 2025-07-08 11:54

Hi,

@wp: thanks for your code.

This one: Code: Pascal  [Select][+][-]window.onload = function(){var
x1 = document.getElementById("main_content_section"); if (x1) { var x
= document.getElementsByClassName("geshi");for (var i = 0; i      
CSVParams.FormatSettings.ShortDateFormat := 'dd/mm/yyyy';     
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 · 2025-07-08 12:11

Quote from: andresayang on July 08, 2025, 01:54:13 pmI 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 · 2025-07-08 12:13

@wp,

Yes, this is what I just notice.

Thanks again for your fix.

Cheers

----------------------------------------------------------------------
paweld · 2025-07-08 12:21

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  fs:
TFormatSettings;begin  fs := DefaultFormatSettings; 
fs.ShortDateFormat := 'yy/dd/mm';  WriteLn(DateToStr(Now, fs));  //
--> 25-08-07  fs.ShortDateFormat := 'yy/dd/mm';  fs.DateSeparator :=
'/';  WriteLn(DateToStr(Now, fs)); // --> 25/08/07  fs.ShortDateFormat
:= 'yy/dd/mm';  fs.DateSeparator := '@';  WriteLn(DateToStr(Now, fs));
// --> 25@08@07  fs.ShortDateFormat := 'yy-dd#mm'; 
WriteLn(DateToStr(Now, fs)); // --> 25-08#07  fs.ShortDateFormat :=
'yy"/"dd"/"mm';  WriteLn(DateToStr(Now, fs)); // --> 25/08/07end;

----------------------------------------------------------------------
andresayang · 2025-07-08 14:31

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][+][-]window.onload = function(){var
x1 = document.getElementById("main_content_section"); if (x1) { var x
= document.getElementsByClassName("geshi");for (var i = 0; i        
outworksheet.CopyRow(0, 0, inworksheet);       
outworksheet.CopyRow(j, k, inworksheet); 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 · 2025-07-08 14:45

Quote from: andresayang on July 08, 2025, 04:31:01 pmHowever, 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
project1;uses  FPSpreadsheet, fpsTypes, fpsUtils, fpsCSV;var  b:
TsWorkbook;  sh: TsWorksheet;begin  b := TsWorkbook.Create;  try    sh
:= b.Addworksheet('sheet1');    sh.WriteDateTime(0, 0, 44000,
nfLongDate);    sh.WriteDateTime(1, 0, 44000, nfShortDate);   
sh.WriteDateTime(2, 0, 44000, 'dd"/"mm"/"yyyy');    
CSVParams.FormatSettings.LongDayNames[5] := 'thursDAY';   
CSVParams.FormatSettings.LongDateFormat := 'dddd", "yyyy"|"mm"|"dd'; 
 CSVParams.FormatSettings.ShortDateFormat := 'yyyy/mm/dd';   
CSVParams.FormatSettings.DateSeparator := '*';    
b.WriteToFile('test.csv', true);  finally    b.Free;  end;end.
Quote from: andresayang on July 08, 2025, 04:31:01 pmI have a second
trouble (or maybe I miss understood) does the following:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i        
outworksheet.CopyRow(0, 0, inworksheet);       
outworksheet.CopyRow(j, k, inworksheet); 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 · 2025-07-08 15:22

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 · 2025-07-08 16:42

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:
Quote2020*06*18
2020*06*18
18/06/2020
On windows:
QuotethursDAY, 2020|06|18
2020-06-18
18/06/2020

So from windows results, using the following code:Code: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i        
inworksheet.CopyRow(0, 0, outworksheet);        for l:= j to k do
inworksheet.CopyRow(l, 1 + (l - j), outworksheet); // 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// Unfortunately,
when I save the file to csv, the resulting csv is empty// If you have
2-3 minutes, I upload excel file and my code in zip file// The code is
debugged on linux and cross-compiled to run on windows

Many thanks for your help

Cheers

----------------------------------------------------------------------
wp · 2025-07-08 18:31

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
project1;uses  SysUtils, FPSpreadsheet, fpsTypes, fpsUtils,
fpsCSV;var  b: TsWorkbook;  sh: TsWorksheet;begin  b :=
TsWorkbook.Create;  try    b.FormatSettings.LongDateFormat := 'dddd,
dd/mm/yyyy';    b.FormatSettings.ShortDateFormat := 'dd/mm/yyyy';   
b.FormatSettings.DateSeparator := '/';     sh :=
b.Addworksheet('sheet1');    sh.WriteDateTime(0, 0, 44000,
nfLongDate);    sh.WriteDateTime(1, 0, 44000, nfShortDate);   
sh.WriteDateTime(2, 0, 44000, 'dd"/"mm"/"yyyy');   
b.WriteToFile('test.csv', true);  finally    b.Free;  end;end.       
   
Quote from: wp on July 08, 2025, 04:45:46 pmoutworksheet.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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
procedure CopyRow(AFromRow, AToRow: Cardinal; AFromWorksheet:
TsWorksheet = nil); 
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
function CopyCell(AFromRow, AFromCol, AToRow, AToCol: Cardinal;     
AFromWorksheet: TsWorksheet = nil): PCell; overload;

----------------------------------------------------------------------
andresayang · 2025-07-08 19:28

Hi,

QuoteBut 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
function CopyCell(AFromRow, AFromCol, AToRow, AToCol: Cardinal;     
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)

QuoteDepending 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
CSVParams.FormatSettings.DateSeparator:= '/'; 
CSVParams.FormatSettings.ShortDateFormat:= 'dd/mm/yyyy'; 
CSVParams.FormatSettings.ShortTimeFormat:= 'hh:mm';   outworkbook:=
TsWorkbook.Create;  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

----------------------------------------------------------------------
wp · 2025-07-08 21:29

Quote from: andresayang on July 08, 2025, 09:28:59 pmQuoteDepending 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
CSVParams.FormatSettings.DateSeparator:= '/'; 
CSVParams.FormatSettings.ShortDateFormat:= 'dd/mm/yyyy'; 
CSVParams.FormatSettings.ShortTimeFormat:= 'hh:mm';   outworkbook:=
TsWorkbook.Create;  outworkbook.FormatSettings:=
CSVParams.FormatSettings;When you do it this way, the other fields of
the CSVParams.FormatSettings and (since you assign them to the
workbook's FormatSettings) are empty and you may get unexpected
behaviour. The CSVParams should be changed only when the workbook
exists because the empty fields of the CSVParams.FormatSettings will
be filled by the workbook's FormatSettings fields.

Better: Either initialize all fields of the CSVParams.FormatSettings
at the beginnung (CSVParams.FormatSettings := DefaultFormatSettings),
or leave the CSVParams alone and only set the workbook's
FormatSettings:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   b :=
TsWorkbook.Create;  try    b.FormatSettings :=
CSVParams.FormatSettings;    b.FormatSettings.LongDateFormat := 'dddd,
dd/mm/yyyy';    b.FormatSettings.ShortDateFormat := 'dd/mm/yyyy';   
b.FormatSettings.DateSeparator := '/';

----------------------------------------------------------------------
andresayang · 2025-07-08 23:44

@wp,

Many thanks for your codes samples.
I did manage to obtain what I wanted using this example Code: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
sh.WriteDateTime(2, 0, 44000, 'dd"/"mm"/"yyyy'); which seems to be the
only one which works on both linux and windows.
I did apply same code sample for times.

Cheers

QuoteThe CSVParams should be changed only when the workbook exists
because the empty fields of the CSVParams.FormatSettings will be
filled by the workbook's FormatSettings fields.I guess this was my
mistakes.

Well, for the moment, I have a working solution.
I will try to review the code once I have some spare time (I do this
code to reduce my daily working load, this simple code save me 1 or 2
hours a day)

And again, many thanks for you help.