Nice video!

I had a follow on question regarding using OpenDataSource to load a text file in CSV format, like this:

SELECT * INTO dbo.stage_text_data FROM

OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source = C:\;

Extended Properties = "Text;HDR=NO;"')...test#csv

I am trying to load the contents of a text file into a SQL Server staging table so that I can perform some data validation on it. I am using the syntax above to create this stage table.

In cases where the source CSV/text file is somehow invalid, like mismatched number of columns on each row, the statement above will still import it, simply creating the number of columns needed to equal to the max number of columns from the file.

I would like to be able to do some validation on this file before it is even imported, because if each row does not have the same number of columns, then I don't want to continue processing.

Example invalid file, in which there are only supposed to be 3 columns: first name, last name, phone number:

Andy,Smith,888-555-1234

Joe,Jones,999-555-4321,222-444-9898

You can see that the 2nd row has 4 columns, and I would like to simply fail importing the entire file. I have tried researching ways to accomplish validating this, but have not come up with anything.

Regards,

Andy T.