Post reply

Using OpenDataSource - SQL School Video

  • April 7, 2009 at 4:50 pm

    #155528

    Comments posted to this topic are about the item Using OpenDataSource - SQL School Video

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • April 9, 2009 at 6:30 am

    #974971

    Good video.

  • April 9, 2009 at 7:47 am

    #975046

    Hi Andy,

    I love this short video training. Gets right to the point and shows you how to do something. I heard you mention a video on linked servers, where is that located?

    Have a God One,

    SQL Batman

    My email is bedgar@aeshr.com

  • April 9, 2009 at 11:21 am

    #975269

    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.

  • April 9, 2009 at 2:00 pm

    #975409

    Nice Video,

    Informative however the video was not that clear. Images were a bit hazzy. Should have previewed before he posted it.

  • April 15, 2009 at 2:20 am

    #977895

    Good video (apart from the blurriness), thanks.

    But it would be useful for this and all other videos if you could make the code available (apologies if it is and I've missed it).

  • June 8, 2009 at 10:42 am

    #1006179

    Hi Andy,

    Did you eventually get the answer to your question? I am looking to do something very similar and would really appreciate some tips if you got yours working.

    Thank you,

    Melissa

  • October 15, 2009 at 3:08 pm

    #1066561

    Hi Andy,

    I've got this error while trying to OPENDATASOURCE:

    "Msg 7403, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered."

    Where do I register it?

    Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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