mORMot Open Source

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

CSV text format

When using the function TSQLTableJSON.GetCSVValues I want the TTimeLog values to be formated as ISO 8601 text instead of the raw value. Is there a simple way to do this?

Also it would be nice if enumeration fields could be formated to make them more readable in CSV.

Thanks,
Esmond

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: CSV text format

It is not possible by now.

In fact, CSV export is for row data, similar to the DB level, not to be human-readable.

You can create a ticket as Feature Request if you really need it.

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: CSV text format

I'm not sure it is worth adding as a feature as everyone will probably want to customize the format differently.

Instead I've had a go at writing a version for my situation:

procedure TableToCSV(Table: TSQLTableJSON; CSV: TFileStream);
var
  i, row, col, FMax: integer;
  W: TTextWriter;
  s : string;
  E: PEnumType;
  EP: PShortString;
  P: TSQLPropInfo;
  Sets, SetMax: cardinal;
  SetItemName, RawSetItemName: RawUTF8;
begin
  if (Table.FieldCount<=0) or (Table.RowCount<=0) then
    exit;
  W := TTextWriter.Create(CSV,8196);
  FMax := Table.FieldCount -1;
  try
    W.AddShort(#$ef#$bb#$bf); // add UTF-8 Byte Order Mark
    for row := 0 to Table.RowCount -1 do
      for col := 0 to FMax do begin
        case Table.ExpandAsString(Row,Col,database,s) of
          sftBoolean: if s = '0' then
                        W.AddString('False')
                      else
                        W.AddString('True');
          sftTimeLog: if Table.GetU(row,col) <> '120795955200' then
                        W.AddString(QuotedStr(stringtoUTF8(s),'"'))
                      else
                        W.AddString('""');
          sftSet: begin
                    P := Table.QueryRecordType.RecordProps.Fields.List[col-1];
                    E := (P as TSQLPropInfoRTTISet).SetEnumType;
                    if E^.MaxValue>31 then // up to 32 elements in tkSet (GetOrdValue)
                      SetMax := 31
                    else
                      SetMax := E^.MaxValue;
                    Sets := GetInteger(pointer(s));
                    EP := @E^.NameList;
                    W.Add('"');
                    for i := 0 to SetMax do begin
                      if GetBit(Sets, i) then begin
                        setItemName := '';
                        RawSetItemName := ShortStringToAnsi7String(EP^);
                        SetLength(setItemName,PInteger(PtrInt(RawSetItemName)-sizeof(integer))^*2);
                        SetLength(setItemName,UnCamelCase(pointer(setItemName),TrimLeftLowerCase(RawSetItemName)));
                        W.AddString(setItemName);
                        W.Add(',');
                      end;
                      inc(PtrInt(EP),ord(EP^[0])+1); // next enumeration item
                    end;
                    W.CancelLastComma;
                    W.Add('"');
                  end;
          else
            W.AddString(QuotedStr(stringtoUTF8(s),'"'));
        end;
        if col=FMax then
          W.AddCR
        else
          W.Add(',');
      end;
    W.Flush;
  finally
    W.Free;
  end;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: CSV text format

Thanks for the input.