-1

Although I am a great fan of stackoverflow and I found several good solutions, this is my first question. I am writing a Delphitool (D6... old but still working!) for making it easier to administrate our students at school.

Delphi reads some (even older...) Visual Foxpro Dbase tables and collects the needed data in a query. This data should be exported to Excel. "Theoretically" everything is working, but I have several problems:

  1. query fields are padded with lots of leading spaces, this seems to be a FoxPro issue (I found this: http://www.ml-consult.co.uk/foxst-36.htm, don't know how to work with it)
  2. trimming through sql-commands does not work (whitespace is created by foxpro query, the tables are "clean")
  3. tried to get things done in Delphi with the help of "onGetText" Method of field names - that helps, but within the dataset the spaces are untouched (only their field values in the grid are correct) - also no help because exporting to delphi uses the query
  4. so I have to get rid of the whitespaces from Delphi to Excel. I use this: Exporting data from a DBGrid to Excel and thought I could execute Excel's trim-funtion to manipulate the fields. So my question is: how can I execute vba-code? I found this: Trim Cells using VBA in Excel

Any ideas? Thanks in advance!

PS: I also looked for a simple "replace all spaces but not within words" way, thought of regexp, but found no way to get this done from within my Delphi code.

As there is not very much text in the excel sheet (100 lines, 4 columns), I could walk through every single cell... but that seems to be not very elegant. How does Trim work in Excel VBA? How to trim text in a column with VBA - Excel

So there's the code for simple replacement of all spaces - unfortunately it also replaces necesserary space between 2 words in a cell: 

 ovExcelApp := CreateOleObject('Excel.Application');
   try
   ovExcelApp.displayAlerts := false;
   ovExcelWorkbook := ovExcelApp.WorkBooks.Add;
   ovWS := ovExcelWorkbook.Worksheets.Item[1]; 
   ovWS.Activate;
   ovWS.Select;
   ovWS.Cells.NumberFormat := AnsiChar('@');
   ovRange := ovWS.Range['A1', 'A1']; //go to first cell
   ovRange.Resize[Data.RecordCount, Data.Fields.Count];
   ovRange.CopyFromRecordset(Data, Data.RecordCount, Data.Fields.Count); 
   ovExcelApp.Cells.Replace(
       What := ' ',
       Replacement := '', 
       LookAt := 2, 
       SearchOrder := 1, 
       MatchCase := False,
       SearchFormat := False,
       ReplaceFormat := False
       );
   end;
   // How could I do a regexp (only replace leading spaces) or do a trim over all cells withing ovWS?
   ovRange.EntireColumn.AutoFit;
   ovWS.SaveAs(DestName, 1, '', '', False, False);

Trying to trim field values brings an error:

procedure Tf_dbftools.qADO_einzelnAfterOpen(DataSet: TDataSet);
begin
  with qADO_einzeln do begin
    first;
    while not eof do begin
      edit;
      Fields[0].AsString := trim(fields[0].asstring);
      Fields[1].AsString := trim(fields[1].asstring);
      Fields[2].AsString := trim(fields[2].asstring);
      Fields[3].AsString := trim(fields[3].asstring);
      next;
    end;
    //post -> error
  end;
end;

I just need to edit the data (from different tables) in the qADO Query resp. dataset to get things correctly exported to Excel. Do I really need to copy the data into a clientDataset to edit them?

Community
  • 1
  • 1
Heiko
  • 13
  • 6
  • Why don't just trim() in Delphi **code** when the data is on its way from DBase to Excel? – Jan Doggen Mar 30 '16 at 13:00
  • Thanks for your answer, Jan. But using CopyFromRecordset method, I only have one _Recordset object.. how could I access the field values? – Heiko Mar 30 '16 at 13:18
  • .. tried to use the "AfterOpen" method and do some aodquery.edit and then Fields[0].asString := trim(Fields[0].asString.. adoquery.post... but no help, can not write values back into the query.. :-\ – Heiko Mar 30 '16 at 13:55
  • is it possible to issue an update to VFP to cleanse the data before you read it? – Hugh Jones Mar 30 '16 at 14:51
  • @Hugh - How could this be done? I downloaded the latest Foxpro Client from here http://www.microsoft.com/download/en/details.aspx?id=14839 - is there a way to disable padding? On the other hand: I am just looking for a way to manipulate data either in the local dataset (without writing it back... all found information deals with writing back values using update statements.. I just need to be able to trim strings.. arrrrghhh... ) or in Excel... – Heiko Mar 30 '16 at 16:28
  • Difficult to know how to help when there's no code. – David Heffernan Mar 30 '16 at 18:15
  • 1) How are you accessing the VFP data? I've never encountered the issue you describe. 2) *Trying to trim field values brings error*. What *error*? What **specific problem** are you having? 3) No, you don't need to copy the data into a CDS to edit them, but you shouldn't need to be editing them in the first place. It appears you're reading the VFP data wrong, but as you've provided no information about how you're doing so it's difficult to determine what that might be. 4) You don't have to resize the range, and you don't have to do it like you are by repeatedly doing so. (continued) – Ken White Mar 31 '16 at 00:26
  • Use a variant array, populate it with your data (trimming it in the Delphi code if needed as doing so), and provide that array to a single cell range in Excel (the top col/row coordinate) and Excel will handle the rest. – Ken White Mar 31 '16 at 00:28
  • @Heiko - `update table set field = trim(field)` - or at least something like it - depends what the VFP equivalent of `trim()` is – Hugh Jones Mar 31 '16 at 08:46
  • Thank you for helping, guys! As I stated, I found my provided solution. There are only few cells (max 300 rows, 5 cols) so importing "manually" is acceptable to me. I was really spending many hours trying to find a workaround: bypassing the VFP padding problem (which is the reason for all that), manipulating data within the dataset of my adoquery, trimming data within excel - but none of this worked. So, just for the record: @Ken 1) I use TADOConnection and TADOQuery. All fields in the recordset are padded to a certain field length, although (@Hugh) data in the original tables are ok. – Heiko Apr 02 '16 at 12:01
  • @ Hugh: I tried your idea of doubling fields and simply use the calculated (trimmed) copy. This also does the job (thanks!), but still I think this is definitely not the "ideal" way.. – Heiko Apr 02 '16 at 12:08

1 Answers1

0

Finally I chose this (simple...) solution by copying every cell value manually so I can use Delphi's trim function:

.. some code as above, but now "data" is a TADOQuery:

 data.First;
    for RowIndex := 0 to Data.RecordCount - 1 do begin
      for ColumnIndex := 0 to data.Fields.Count - 1 do begin
        ovWS.Cells[RowIndex+1, ColumnIndex+1].Value := trim(data.Fields[columnIndex].AsString);

        next ;
      end;
    data.Next;
    next;

(... but still wondering if there is a simpler and "cleaner" way... besides the fact that padding in Foxpro is horrible!)

Heiko
  • 13
  • 6
  • another possibility might be to create a calculated field for each field requiring manipulation - you would put your trim statement into the `OnCalcFields()` method. This would mean the dataset would then have 2 'versions' of each datum – Hugh Jones Mar 31 '16 at 08:49