OpenRowset with a non .csv or .txt extension

• Doug Blystone

  Ten Centuries

  Points: 1144

  More actions

  July 18, 2014 at 4:52 am

  #310124

  My goal is to have a file (it comes from another system) that I want to import via an OPENROWSET style query.

  The query would look like this:

  select [NoName] from openrowset('MSDASQL'

  ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)};

  DefaultDir=c:\filedir'

  ,'select * from "file.lst"')

  If I make the file a .csv it works fine. However, if it has a not CSV or TXT extension it throws the following error and cannot seem to find a solution to it.

  OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only.". Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

  In addition, (although I can probably find this elsewhere), I need to have the first line 'BLANK' so that it does not miss data (there is no header row). Is there a way to use OPENROWSET without BULK to basically include all rows as data?