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

[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