Using OpenDataSource - SQL School Video

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Using OpenDataSource - SQL School Video

Using OpenDataSource - SQL School Video

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Tuesday, April 07, 2009 4:50 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #692618

wadhwa.harish

Posted Thursday, April 09, 2009 6:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 10:45 AM
Points: 6, Visits: 10

Good video.

Post #693917

bedgar

Posted Thursday, April 09, 2009 7:47 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 06, 2009 7:59 AM
Points: 58, Visits: 12

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

Post #694000

Andy Torgerson

Posted Thursday, April 09, 2009 11:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:40 PM
Points: 184, Visits: 229

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.

Post #694259

sherif.hamawy

Posted Thursday, April 09, 2009 2:00 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 16, 2009 1:14 PM
Points: 5, Visits: 11

Nice Video,

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

Post #694415

SQLSimon

Posted Wednesday, April 15, 2009 2:20 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:09 AM
Points: 23, Visits: 616

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).

Post #697310

etudiante

Posted Monday, June 08, 2009 10:42 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, October 15, 2009 9:24 PM
Points: 11, Visits: 144

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

Post #730815

nroudak

Posted Thursday, October 15, 2009 3:08 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 8:12 AM
Points: 19, Visits: 119

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

Post #803802

« Prev Topic | Next Topic »

Permissions