Hi,

I have modified the ImportData procedure of IB_Import.pas to validate items from the ascii file that are going into a date colunmn and throw an exception if they are not valid.

Note :

- I don't guarantee this code is correct.
- I haven't added a property to IB_Import to make this optional, values going into date columns will always be
checked
- dates will only be checked if the dateformat is dfDMY, dfMDY, dfYMD. If the dateformat is dfWindows there will
be no checking.
- Interestingly although without this modification an invalid date doesn't cause an error, an invalid Float value
does, that's because eventually a StrToFloat function is called.
- I thought using StrToDate to throw an exception on invalid dates would be much easier than pulling the string
apart and using EncodeDate as I do, but StrToDate needs a TFormatSettings format parameter and I didn't want
to convert between the IB_Import dateformat and a TFormatSettings. This may be a better way though.
- Any feedback appreciated, if people want this integrated more properly into IB_Import I am happy to do so when I have more time.

Cheers
SamH

(begin line 1200 of IB_Import.pas)

if AsciiFields[ii] = '' then
DSql.Params[p].Clear
else
try
// If the column is a date then check the value being inserted from the ascii file is
// indeed a date.
// EncodeDate will throw an EConvertError exception if a value is not a date.
// The exception will bubble up to user code, to be handled there.
if (DSql.Params[p].sqltype = SQL_DATE_) or (DSql.Params[p].sqltype = SQL_DATE) or
(DSql.Params[p].sqltype = SQL_TYPE_DATE_) or (DSql.Params[p].sqltype = SQL_TYPE_DATE) then begin
case FDateFormat of
//encodeDate expects (Year, Month, Day)

// date is in format '27-10-2009' (date seperator might be different char, no effect)
dfDMY: EncodeDate(StrToInt(copy(AsciiFields[ii], 7,4)), StrToInt(copy(AsciiFields[ii], 4,2)), StrToInt(copy(AsciiFields[ii], 1,2)));
// date is in format '10-27-2009' (date seperator might be different char, no effect)
dfMDY: EncodeDate(StrToInt(copy(AsciiFields[ii], 7,4)), StrToInt(copy(AsciiFields[ii], 1,2)), StrToInt(copy(AsciiFields[ii], 4,2)));
// date is in format '2009-10-27' (date seperator might be different char, no effect)
dfYMD: EncodeDate(StrToInt(copy(AsciiFields[ii], 1,4)), StrToInt(copy(AsciiFields[ii], 6,2)), StrToInt(copy(AsciiFields[ii], 9,2)));
end;
end;
except
raise EIB_Import.Create(AsciiFields[ii] + ' is not a valid date');
end;

DSql.Params[p].AsString := AsciiFields[ii];
inc(p);
end;


-- In IBObjects@yahoogroups.com, "Jason Wharton" <supportlist@...> wrote:

>
> Samh,
>
> > Any assistance with this issue/suggestions is appreciated, I am working to
> > a deadline and need to get this problem solved.
> >
> > If the first row in the csv file contains an invalid date then that date
> > will end up in the database table date column as null, eg :
> >
> > 1,sdfsdf,ELLEN,HUNTER,February
> > 2,07/26/2007,RACHEL,BREWER,March
> > 3,12/21/2007,ELSIE,MEDINA,July
> > 4,01/02/2009,JESSICA,DOUGLAS,January
> >
> > Row 1 is inserted with a null date.
> >
> > If the invalid date is not on the first row it will be given the date
> > value of the preceeding row eg :
> >
> > 1,12/21/2007,ELLEN,HUNTER,February
> > 2,sdsd,RACHEL,BREWER,March
> > 3,12/21/2007,ELSIE,MEDINA,July
> > 4,01/02/2009,JESSICA,DOUGLAS,January
> >
> > Row 2 is inserted with a date value of 12/21/2007, it has inherited that
> > date value from row 1!
> >
> > What I would like to happen is for the TIB_Import to throw an exception or
> > report an error if a value in the csv going into a
> > date column is not a valid date.
> >
> > I have taken a look at IB_Import.pas and think the relevant area is
> > around line 1203 :
> > DSql.Params[p].AsString := AsciiFields[ii];
> >
> > I can hack around in there and try and add some type checking (in my case
> > I only need it for dates), but i wonder if there is already a mechanism
> > available.
>
> I think hacking around in there is going to be your best bet at this point.
> I am not aware of any other mechanism to deal with this. It would be nice to
> have a setting to go either way on this. Many wouldn't want the import
> halted over bad data but others may absolutely want it halted if there is
> bad data.
>
> Let me know what you come up with. It might be something that can remain a
> permanent feature so people in the future will be able to deal with this
> better.
>
> Thanks,
> Jason Wharton
>