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

FROM OPENDATASOURCE with csv file Expand / Collapse
Author
Message
Posted Tuesday, March 28, 2006 2:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:11 AM
Points: 384, Visits: 821

Hi all,

Im trying to convert a stored procedure that reads from Excel to one that reads from csv. For Excel is uses FROM OPENDATASOURCE so If possible this is the only bit I want to modify (least change = least risk). Problem is I cannot get the syntax right.

My .csv looks like this

'Col1','Col2'
'1314','0508ODK01A'
'1314','0508ODK01A'
'1314','0508ODK01A'

path and name are C:\Temp\Test.csv

 

My query looks like this

SELECT
 CAST([Col1] AS VARCHAR(50)),                        
 CAST([Col2] AS VARCHAR(50))                  
FROM
 OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
 'Data Source="C:\Temp";User ID=Admin;Password=;Extended properties=''TEXT;HDR=YES''')..."Test.csv"

Error message is this:

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'Test.csv'.  The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error:  OLE DB provider does not contain the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Test.csv'].

I have a bit of time presssure on this so I think I've missed something obvious!

Help ...

Post #268878
Posted Tuesday, March 28, 2006 10:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 30, 2013 8:10 AM
Points: 469, Visits: 301

You ll have to use a different driver to connect to csv file.

This is new command :

select * from OpenRowset('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Temp;Extended properties=''ColNameHeader=True;Format=CSVDelimited;''','select * from Test.csv')

 

If you want more formating you ll need to use a schema.ini file

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

 

schema.ini (same folder as Test.csv file!)

[test.csv]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=ANSI
Col1=COL1 Char Width 255
Col2=COL2 Char Width 255

 

Regards,

Vasc




Kindest Regards,

Vasc
Post #269083
Posted Tuesday, March 28, 2006 6:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 29, 2006 7:27 AM
Points: 3, Visits: 1

You might want to considder using BCP to grab each line of the file into a single column.

if you do thst you can parse the CSV first to sort out any errors before you do the actualy import.




Alistair Warburton
Post #269188
Posted Wednesday, March 29, 2006 3:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:11 AM
Points: 384, Visits: 821

Hi all - thanks for the help.

I eventually bit the bullet and went down the DTS route.

Vasc - Thank for the info on changes to the driver - it looks like it just got more work!

Allen

 

 

Post #269286
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse