CSV File Format Checker

Question

CSV File Format Checker

Ted Zatopek Old

Ten Centuries

Points: 1299
More actions
November 5, 2008 at 12:34 pm

#131971

I'm looking for a tool (freeware would be preferred) that will allow you to setup a format for CSV file (date fields, number fields, etc.) and then run a comparison of the expected format to a an actual file.
It would give results in the form of which rows in the files had an issue, which columns in those rows had the issue, and what the issue was.
So if a CSV file is supposed to have an amount value in the Amount column, and Row 3 has a value of "fc", then it would generate an error. Ex: Row "3" Column "Amount" has a bad value "fc" or something very similar.
Any help is appreciated.
...thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Just use BCP to run it into a temporary staging table with the correct switches set to offload any bad rows into an error file. You should set the "Maximum number of errors" switch to more rows than you expect in the file.

For more info on BCP, please refer to Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ted Zatopek Old Ten Centuries Points: 1299 More actions · Answer 2

Thanks for your response.

Yep, we considered that and that is what we'll do if we have to. We'll then have to write a mechanism to do datatype checking and give results.

I was hoping this was a "solved" problem that would shortcut that effort. We can't be the only shop that has to deal with integration using CSV files that are easy to not format "correctly".

Thanks again.