Reading data with MSDASQL/OPENROWSET - periods in the filename

• murthyvs

  SSCommitted

  Points: 1799

  More actions

  September 29, 2021 at 6:50 pm

  #3934775

  Hi all,

  I ran into an issue this morning to read the data using OPENROWSET

  I was able to read the file and count the records if the file didn't have periods. Example:

  select count(*)

  FROM OPENROWSET('MSDASQL','Driver={Microsoft Access Text Driver (*.txt, *.csv)}; Extended Properties="text; HDR=YES; FMT=Delimited";

  DefaultDir=X:\folder\sub folder\;',

  'SELECT * FROM [file_no_periods.csv]');

  but when the file had periods in the file name, I get the following error.

  select count(*)

  FROM OPENROWSET('MSDASQL','Driver={Microsoft Access Text Driver (*.txt, *.csv)}; Extended Properties="text; HDR=YES; FMT=Delimited";

  DefaultDir=X:\folder\sub folder\;',

  'SELECT * FROM [file.abc.csv]');

  Error:

  OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] The Microsoft Access database engine could not find the object 'file.abc.csv'. Make sure the object exists and that you spell its name and the path name correctly. If 'file.abc.csv' is not a local object, check your network connection or contact the server administrator.".

  Msg 7350, Level 16, State 2, Line 30

  Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

  The SQL server has access to the file etc.

  I tried searching quite a bit but didn't find a solution.

  Appreciate your help!

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  September 30, 2021 at 7:10 pm

  #3935207

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• October 12, 2021 at 6:14 am

  This was removed by the editor as SPAM