Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using OpenDataSource - SQL School Video Expand / Collapse
Author
Message
Posted Tuesday, April 7, 2009 4:50 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 1:44 PM
Points: 6,783, Visits: 1,882
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
Post #692618
Posted Thursday, April 9, 2009 6:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 10:45 AM
Points: 6, Visits: 10
Good video.
Post #693917
Posted Thursday, April 9, 2009 7:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 6, 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
Posted Thursday, April 9, 2009 11:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 2:02 PM
Points: 184, Visits: 246
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
Posted Thursday, April 9, 2009 2:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, April 15, 2009 2:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:08 AM
Points: 25, Visits: 745
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
Posted Monday, June 8, 2009 10:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, October 15, 2009 3:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 16, 2013 10:51 AM
Points: 25, Visits: 126
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 »

Add to briefcase

Permissions Expand / Collapse