Help with TFDBatchMove fields

[Gary 18]

I have an app that has been working fine for some time. I use a TFDBatchMove to import csv info from a GPS provider. Recently one vehicle's hardware malfunctioned and now for those 3 days the value in the 'Arrival Time' field has '--' instead of a time value. Not a big deal, I can deal with it in code, the problem is I can't get the TFDBatchMoveTextReader to let this field change to atString. I have changed it everywhere I can think of and even removed and added it back I still get this error when executing the TFDBatchMove "Bad Text value [--] format for mapping item [Arrival Time]. '--' is not a valid time.

 

The changes I have made:

TFDBatchMoveTextReader Fields Changed 'Arrival Time' to atString with FieldSize 15.

TFDBatchMoveDataSetWriter is set to a Virtual table vtRawDayData. I changed it's field 'ArrivalTime' to TStringFiels Size 15, 

Made sure it's fieldDefs was set properly as well.

 

csv field is 'Arrival Time' with space vtRawDatDate field is 'ArrivalTime' no space and works fine if I delete the offending rows from the csv

 

I still get the time format error

 

Any ideas short of deleting and redoing the whole chain of components?

 

The offending dates are in September and each week I download the full extent of their allowable data (4 months), The oldest date is in June currently, so even though it's not a big deal to edit out those dates I have to do it each week until I get past September 7-9. Of course if it happens again I would like to already have dealt with it in the app.

 

[Anders Melander 1815]

I haven't used that particular component, as I have my own CSV import library, but I suggest you examine the DFM file in text mode (Alt+F12 in the IDE) to verify that all properties are as you expect them to be.

[Gary 18]

I have found TFDBatchMove to really be troublesome, probably my ignorance, I would like to be able to control a fields import and am sure one of the properties let you do this but have been unable to find any understandable documentation or examples. It seems that everyone has their own csv import component, so the lack of some public getit project seems odd. I guess I'll have to roll my own as well

[Anders Melander 1815]

1 hour ago, Gary said:

I guess I'll have to roll my own as well

Here's some code to get you started:

https://bitbucket.org/anders_melander/better-translation-manager/src/master/Source/amDataCsvReader.pas

 

...used in this unit:

https://bitbucket.org/anders_melander/better-translation-manager/src/master/Source/amLocalization.Dialog.Import.CSV.pas

[Gary 18]

Thank You

[weirdo12 21]

14 hours ago, Gary said:

I would like to be able to control a fields import and am sure one of the properties let you do this but have been unable to find any understandable documentation or examples.

Do you use GuessFormat() prior to doing the TFDBatchMove.Execute?

[Gary 18]

Yes, get crazy results if not

[weirdo12 21]

1 minute ago, Gary said:

Yes, get crazy results if not

What I do after GuessFormat() is fix all the types that GuessFormat() assumes to match the destination. The main issue I had was float columns being detected as integers.

 

Do you use the TDBatchMove.OnWriteValue event?

 

void MapTextReaderTypesToDestination(TFDTextFields* text_reader_, TFields* dest_)
{
	for (int i = 0; i < text_reader_->Count; ++i)
	{
		auto field_ {dest_->FindField(text_reader_->Items[i]->FieldName)};

		// I don't like the looks of indenting the switch statement below
		// inside of an if statement ;-)

		if (field_ == nullptr) {
			continue;
		}

        switch (field_->DataType)
		{
		case ftAutoInc:
			 text_reader_->Items[i]->DataType = TFDTextDataType::atLongInt;
			 break;
		case ftBCD:
		case ftCurrency:
		case TFieldType::ftExtended:
		case ftFloat:
		case ftFMTBcd:
		case TFieldType::ftSingle:
			/*
			// If there is a single 0 in a column that is one of
			// the above field types (e.g. import file contains "hello",0,1,0)
			// GuessFormat will think the 0, 1 and 0 values
			// columns are integer and there will be an error
			// during import if we don't specify the correct type
			*/
			text_reader_->Items[i]->DataType = TFDTextDataType::atFloat;
			break;
		case ftDate:
			text_reader_->Items[i]->DataType = TFDTextDataType::atDate;
			break;
		case ftDateTime:
		case ftTimeStamp:
		case ftTimeStampOffset:
			text_reader_->Items[i]->DataType = TFDTextDataType::atDateTime;
			break;
		case ftString:
		case ftWideString:
			text_reader_->Items[i]->DataType = TFDTextDataType::atString;
			break;
		case ftTime:
			text_reader_->Items[i]->DataType = TFDTextDataType::atTime;
			break;
		default:
			;
		}
	}
}

 

[Gary 18]

I had difficulty understanding how to use the events and couldn't find any examples. I found the help to be vague even the parameters.

 

I did find a commercial product from scalabium.com the import component has this event:

Declaration
property OnBeforeRecordEvent: TBeforeRecordEvent;
Description
This event allow to define global values for each parsed value before applying to fields. Also here you can skip some row from loading (just by your custom condition).
The Fields parameter is a list of parsed field names.
The Values parameter is a variant array with parsed value for each field name.
To skip a row from loading just assign a False value to Accept parameter. By default the 
Accept is True.

 

Looks like exactly what I need! I can just skip the offending record, even log and display how many were skipped. 50 EUR with source. I'll post how it goes.

 

Thank you both for the help I will still experiment with your advice

[weirdo12 21]

6 minutes ago, Gary said:

I had difficulty understanding how to use the events and couldn't find any examples. I found the help to be vague even the parameters.

Here's an example:

 

void __fastcall TDMImportExport::FDBatchMoveImportWriteValue(TObject *ASender, TFDBatchMoveMappingItem *AItem,
		  Variant &AValue)
{
	switch (AItem->DestField->DataType)
	{
	case ftGuid:
		AValue = DMDatabase->GUIDFromServer();
		break;
	case ftString:
	case ftWideString:
		break;
	case ftAutoInc:
		if (Variants::VarIsNull(AValue)) {
			AValue = FAutoIncVal++;
		}
		break;
	case ftDate:
	case ftDateTime:
	case ftTime:
		// NULL could be valid. Only reformat dates and times
		// that aren't NULL.
		if (!Variants::VarIsNull(AValue))
		{
			try {
				AValue = Sysutils::StrToDateTime(AValue);
			}
			catch (...) {
			}
		}
		break;
	default:
		break;
	}
}
//---------------------------------------------------------------------------

You could look for the '--' string here.

[Gary 18]

@wierdo12 Those examples really helped, thanks!

 

Adding code to the events really slowed the import down, but at least it's working. I'll take out the logging and see how it goes.

 

I didn't realize that GuessFormat actually changed field defs. I was executing it in design mode and that's what was keeping the date field in the dfm as I would save after running in design mode.

 So these changes worked:

Reset my fields after Guess changes them

bmFleetDay.GuessFormat;
    txrdrFleetDayData.DataDef.Fields[13].DataType := atString;
    txrdrFleetDayData.DataDef.Fields[13].FieldSize := 15;
    txrdrFleetDayData.DataDef.Fields[26].DataType := atString;
    txrdrFleetDayData.DataDef.Fields[26].FieldSize := 15;
    { TODO : Surround with exception handler }
    result := bmFleetDay.Execute <> 0;

 

procedure TdmLocations.bmFleetDayWriteValue(ASender: TObject; AItem:
    TFDBatchMoveMappingItem; var AValue: Variant);
begin
  if (AItem.DestField.FieldName = 'StartTime') or
    (AItem.DestField.FieldName = 'ArrivalTime') then
    if not VarIsNull(AValue) then
      if AValue = '--' then
      begin
      SiMain.LogMessage(Format('Field: %s Value: %s',
        [AItem.DestField.FieldName, AValue]));
        FSkipRecord := True;
      end;
end;

Skip record

procedure TdmLocations.bmFleetDayWriteRecord(ASender: TObject; var AAction:
    TFDBatchMoveAction);
begin
  if FSkipRecord then
  begin
    SiMain.LogMessage('Skipping Record');
    AAction := paSkip;
    FSkipRecord := False;
  end;
end;

I think I'll track skipped records and show user a message with count of corrupted rows

 

Thanks again for the help

[Lajos Juhász 295]

I have never tried batchmove. A simple optimization would be not to use AItem.DestField.FieldName in that event, instead you could try AItem.Index or AItem.Destfield.

[weirdo12 21]

12 hours ago, Gary said:

Thanks again for the help

I actually missed something important: before you call TFDBatchMove.Execute call TFDBatchMove.Analyze.Clear or your changes might be overwritten.

 

From the docs:

 

"When the Analyze property value is not empty, then the batch
move component tries to automatically recognize the data source
format by calling the GuessFormat() method as part of the
Execute method call."

 

Something else I discovered last week is that WriteValue is triggered twice for any field that is part of a primary key.

 

https://embt.atlassian.net/servicedesk/customer/portal/1/RSS-2064

 

 

Edited October 14 by weirdo12

[Gary 18]

TFDBatchMove.Analyze.Clear

Does not compile. There is an AnalyzeSample Integer property that can be set -1 or 0;

Things are working well without it though.

[weirdo12 21]

3 hours ago, Gary said:

TFDBatchMove.Analyze.Clear

Does not compile. There is an AnalyzeSample Integer property that can be set -1 or 0;

Things are working well without it though.

My Delphi cannot be trusted! I'm sure someone can suggest the correct code.

[Gary 18]

A little more reading:

 

Documentation:

procedure GuessFormat(AAnalyze: TFDBatchMoveAnalyze = [taDelimSep, taHeader, taFields]);

Another option is to use the Analyze property. When Analyze is not empty, the GuessFormat method is automatically called by the Execute method.

 

taFields worked for me. Setting the property or passing in the GuessFormat parameter works the same.

  bmTest2.Analyze := [taFields];
//  bmTest2.GuessFormat([taDelimSep]);
  bmTest2.Execute;

If any has problems understanding what's going on maybe my mistakes will help.

Firstly what is happening in the WriteValue event. I kept trying to access the Source values in the AItem Var. I looked at the event as saying the AValue var is empty, you need to get the value from the source, perform any conversions and load into AValue. Also, I had no experience with Variants, so when I continually got A/V when accessing the AItem.Source values I thought it had something to do with the way I was access the Variant. What happens and makes perfect sense after a little thought is The AValue is already loaded and the TFDBatchMove has performed the conversion, now you can determine the DataType according to what is expected in the AItem.DestinationField and any further processing can be done. In my case TFDBatchMove was trying to convert an unexpected value '--' to a Time DataType. I had to fix the problem upstream, when the GuessFormat procedure was changing the String field to Time. Wierdo12 helped with the suggestion of resetting the field type after the GuessFormat call. I think that Analyze property or GuessFormat with the taField value may not change the DataType, I was calling it with empty param. We'll see.

[weirdo12 21]

11 hours ago, Gary said:

 I think that Analyze property or GuessFormat with the taField value may not change the DataType, I was calling it with empty param. We'll see.

Calling it with TFDBatchMove.GuesFormat is the same as calling it like this: TFDBatchMove.GuessFormat([taDelimSep, taHeader, taFields]). By default, the procedure is called will all of the analyze options.

procedure GuessFormat(AAnalyze: TFDBatchMoveAnalyze = [taDelimSep, taHeader, taFields])

And after calling TFDBatchMove.GuessFormat with no arguments, the TFDBatchMove.Analyze property will has all those values - taDelimSep, taHeader, taFields.

 

If it didn't have any default argument values it would be declared like this:

procedure GuessFormat(AAnalyze: TFDBatchMoveAnalyze)

https://docwiki.embarcadero.com/RADStudio/Sydney/en/Parameters_(Delphi)#Default_Parameters

 

So if you call TFDBatchMove.GuessFormat and the don't clear TFDBatchMove.Analyze prior to calling TFDBatchMove.Execute, TFDBatchMove.GuessFormat is called again using the values from TFDBatchMove.Analyze, undoing any changes you might have made to the field types after the first call to TFDBatchMove.GuessFormat.

 

 

Edited October 15 by weirdo12