none
Microsoft.ACE.OLEDB.12.0 provider and text file without extension RRS feed

  • Question

  • Windows 7 64-bit. Trying to read text file with C#

    ConnectionString

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestFiles;Extended Properties="text;HDR=NO;FMT=Delimited(|)";

    using(System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
    {
                    conn.Open();
                    System.Data.OleDb.OleDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [DataFile]";
                    string bc = (string)cmd.ExecuteScalar();
    }

    An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
    Additional information: Cannot update. Database or object is read-only.

    It looks like I have to modify DisabledExtensions at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Text

    How can I add null file extension?

    Friday, October 7, 2016 1:10 PM

Answers

  • Hi,

    Thanks for the sharing.

    To be honest, our forum is used for discussing questions about Office Object Model, since your issue is related to SQL Server, I suggest you post on SQL server forum.

    Sorry for any inconvenience and have a nice day! 

    Regards,

    Celeste

    • Marked as answer by dburtsev Tuesday, October 11, 2016 4:25 PM
    Tuesday, October 11, 2016 2:22 PM
    Moderator

All replies

  • Windows 7 64-bit. Trying to read text file with C#

    ConnectionString

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\TestFiles;Extended Properties="text;HDR=NO;FMT=Delimited(|)";

    using(System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
    {
                    conn.Open();
                    System.Data.OleDb.OleDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [DataFile]";
                    string bc = (string)cmd.ExecuteScalar();
    }

    An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
    Additional information: Cannot update. Database or object is read-only.

    It looks like I have to modify DisabledExtensions at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Text

    How can I add null file extension?

    • Merged by Kevin Linq Monday, October 10, 2016 7:47 AM the two threads are the same
    Friday, October 7, 2016 1:11 PM
  • Hi,

    What is your DataFile? The extension depends on your DataFile.

    I suggest you check the extension of files for your "datafile". Compare them with registry value in DisabledExtensions. And add the extension of file which causes the issue in the reg. 

    Monday, October 10, 2016 6:43 AM
    Moderator
  • >What is your DataFile

    It is a text file without extension. This is how vendor send it to us. No extension. 

    This is file name example: ABC_DEF_01102016
    • Edited by dburtsev Monday, October 10, 2016 1:44 PM
    Monday, October 10, 2016 1:28 PM
  • Hi,

    To my knowledge, we could not use files without extension in the connection string.

    If there is no extension of the file, which software do you use to open the text file manually?

    Since you confirm it is a text file, I suggest you add the extension such as .txt .xml .html , try to open it manually to check if the data could be normally displayed. Then use the full name(with extension) of the file in the string. Also, I think you could contact with your vendor to get correct extension to open the file.

     

    Besides, if you just want to read the file, I suggest you use File.ReadAllText Method, it could read a text file without extension.

     

    Tuesday, October 11, 2016 2:37 AM
    Moderator
  • >To my knowledge, we could not use files without extension in the connection string.

    Setting Options Programmatically for the Text File Driver

    To display files without extensions in the Define Text Format dialog box, "*." must be added to the Extensions List.

    https://msdn.microsoft.com/en-us/library/ms714864(v=vs.85).aspx

    > which software do you use to open the text file

    SQL Server bcp Utility with format file.

    >I suggest you add the extension such as .txt .xml .html 

    This are files from the vendor. I prefer do not change them for security reason.

    > I suggest you use File.ReadAllText Method,

    We need open this file as a table and run several SQL statements against it. Also we need to check datatype for each field. I can generate  Schema.ini for each file. 

    Tuesday, October 11, 2016 1:52 PM
  • Hi,

    Thanks for the sharing.

    To be honest, our forum is used for discussing questions about Office Object Model, since your issue is related to SQL Server, I suggest you post on SQL server forum.

    Sorry for any inconvenience and have a nice day! 

    Regards,

    Celeste

    • Marked as answer by dburtsev Tuesday, October 11, 2016 4:25 PM
    Tuesday, October 11, 2016 2:22 PM
    Moderator
  • Microsoft.ACE.OLEDB.12.0 driver is part of Microsoft Office. It came from AccessDatabaseEngine_x64.exe which is Microsoft Access Database Engine Redistributable, not SQL Server. ACE means Access Connectivity Engine, not SQL Server Connectivity Engine. 

    I am asking question about HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ key.

    I will ask this question on SQL server forum.


    Tuesday, October 11, 2016 4:25 PM