I am getting an error when trying to save an XLS to .DBF file.

Glen Childs

Hello, I am getting an error when trying to save my XLS to .DBF. The error is: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'. I am getting the error at this line of code: var cnn = new OleDbConnection(cnnStr);

//Connection string for SQL
var cnnStr = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\file.xls;ReadOnly=1;ColNameHeader=False";

        //Creates a new OleDbConnection with an argument of cnnStr
        var cnn = new OleDbConnection(cnnStr);

        //creates new datatable in memory to store the read excel spreadsheet.
        var dt = new DataTable();

        try
        {
            //Opens the new connection called "cnn". 
            cnn.Open();

            string sqlConnectionString = "Data Source=Zach-PC;Initial Catalog=master;Integrated Security=True";


            var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
            //string worksheet = schemaTable.Rows\[worksheetNumber - 1\]\["table\_name"\].ToString().Replace("'", "");
            string sql = String.Format("select \* from \[{0}\]", sqlConnectionString);
            var da = new OleDbDataAdapter(sql, cnn);
            da.Fill(dt);
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            // close resources
            cnn.Close();
        }

Any pointers? ;P

Regards, Glen

Richard Deeming

That looks like an ODBC connection string. Try one of the OLEDB Excel connection strings from: http://www.connectionstrings.com/excel/[^] For example:

var cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\file.xls;Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Glen Childs

Thanks solved that error, I am getting a different error now tho :confused: error: 'DataSource=excelFilePath;InitialCatalog=master;IntegratedSecurity' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

Lost User

DataSource is wrong, it should be Data Source: two words.

Veni, vidi, abiit domum

Glen Childs

Okay thanks, same error occurs though with Data Source as two words.

Lost User

The rest of the information in that statement does not look like the example that was suggested to you.

Veni, vidi, abiit domum

Jason Gleim

Connection strings are provider-specific. The sting you have there uses an Excel file as the "data store" but you are passing SQL-server parameters with InitialCatalog and IntegratedSecurity. Those aren't parameters the "Excel data provider" understands.

Richard Deeming

You've opened an OLEDB connection to an Excel spreadsheet, and you're now trying to execute the query:

select * from [Data Source=Zach-PC;Initial Catalog=master;Integrated Security=True]

That's not a valid query. A valid query would look something like:

select * from [SheetName$Range]

Perhaps if you explain what you're trying to achieve, we might be able to help. :)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Glen Childs

I am trying to achieve an application that is able to read in an Excel Spreadsheet (.XLS) and be able to convert/save out to a Database file (.DBF). Regards,

Richard Deeming

Start by reading the sheet:

static DataTable LoadExcelSheet(string fileName, int worksheetNumber, bool headers)
{
if (string.IsNullOrEmpty(fileName)) throw new ArgumentNullException("fileName");
if (!File.Exists(fileName)) throw new FileNotFoundException(null, fileName);

var cnnStr = string.Format(
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\\"Excel 8.0;HDR={1};IMEX=1\\"", 
    fileName, 
    headers ? "Yes" : "No");

using (var cnn = new OleDbConnection(cnnStr))
{
    cnn.Open();

    var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    
    // NB: The schema table often contains two rows per sheet;
    // you need to ignore any which end with "$":
    var sheetRow = schemaTable.AsEnumerable()
        .Where(row => !((string)row\["TABLE\_NAME"\]).EndsWith("$"))
        .Skip(worksheetNumber).FirstOrDefault();
    
    if (sheetRow == null) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");

    string sql = string.Format("SELECT \* FROM \[{0}$\]", sheetRow\["TABLE\_NAME"\]);
    var da = new OleDbDataAdapter(sql, cnn);

    var dt = new DataTable();
    da.Fill(dt);
    return dt;
}

}

That will give you a DataTable containing the data from the Excel sheet. You'll then need to use a SqlConnection and a SqlDataAdapter to update your SQL database.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Glen Childs

Okay Thanks will try that! :-D