Steve Stout (12/10/2007)
By 'bad data' I mean data that is not in the correct format. For instance, in the above line item import, I ran across 3 rows which had '.5' for the quantity. As this is a float value in SQL and not an integer it made the cursor stop each time. I altered the original data to '1' and restarted the cursor. I am running into a lot of data that is blank/null when it should not be. Or has invalid values.The data was pulled from a FileMaker Pro 4.0 database which seems to have extreemly simplistic data types. A 'Number' field can be a Integer or a Float for example. A Text field can be a varchar of any length. FileMaker does not make you specify the data lengths anywhere it seems, so I'm having to find out through trial and error where I need to tweak the lengths of my varchars.
If I'm processing this garbage data one row at a time and marking each row as 'done', when it stops I can find where it stopped fast and look at the data on that row to see what is happening.
I'll try implementing the suggestions above to see if I can allow for all the variables I'm dealing with. Thanks for the input! 🙂
You have your data types, you have your business rules regarding the imported data.
Right?
So, just put the checks for those rules in WHERE clause:
WHERE Quantity NOT LIKE '%[^0-9]%' -- this will leave only rows with integer quantity
AND EXISTS (select 1 from Products P ON P.ProductCode = F.item_code)
AND {whatever else you need to check}
If you want to look at rejected lines store it in "Failed_FileMaker" table:
INSERT INTO dbo.Failed_FileMaker (...)
SELECT ...
WHERE Quantity LIKE '%[^0-9]%' OR Quantity = '' OR Quantity IS NULL
OR NOT EXISTS (select 1 from Products P ON P.ProductCode = F.item_code)
OR .... {whatever else you need to check}
_____________
Code for TallyGenerator