• 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