FROM OPENDATASOURCE with csv file

• allen davidson

  SSCertifiable

  Points: 7042

  More actions

  March 28, 2006 at 2:43 am

  #116665

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

• Vasc

  SSCarpal Tunnel

  Points: 4643

  More actions

  March 28, 2006 at 10:52 am

  #629259

  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

• Alistair Warburton

  SSC Journeyman

  Points: 77

  More actions

  March 28, 2006 at 6:39 pm

  #629330

  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

• allen davidson

  SSCertifiable

  Points: 7042

  More actions

  March 29, 2006 at 3:11 am

  #629411

  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