June 10, 2002 at 6:29 am
I am loading data from a text file into a database table, the text file has three fields in the following format :-
1,2,3
4,5,6
Is there any way to trap as an error if there are insufficient fields in the text file e.g.
1,2,3
4
This loads into the table without error as :-
123
4<Null><Null>
I can't trap the error on the database side with non-null columns as the following situation is valid :-
1,2,3
4,,
Which also loads into the table as :-
123
4<Null><Null>
I'm going to parse the file using vb script and the file system object which is going to slow down processing, does
anyone know of a simpler method?
Regards,
Andy Jones
.
June 10, 2002 at 7:10 am
How about loading it into a working table nulls and all, then you can use a single select to identify all the bad rows, or just exclude them when you do the final insert into the "good" table?
Andy
June 10, 2002 at 7:22 am
Yes, but you still cannot distinguish between
1,2,3
4
and
1,2,3
4,,
as they will appear identical in the working table. The text files are produced by a third party app. and I want to trap that the file was written correctly and also that it hasn't been truncated in transit.
Regards,
Andy Jones
.
June 10, 2002 at 7:45 am
Are you likely to have a problem with the 3rd party app? If you have to validate at the text file level, I dont see a way of doing it short of parsing every line. Trapping for truncation may not be enough, you probably want to do some kind of check for the number of records supposed to be received vs actually received. A header record is a common way of doing this (or a trailer record that indicates true end of file), though its easier to parse if its stored separately.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply