none
import export to excel - format issues RRS feed

  • Question

  •  

    Hi everyone,

     i am trying to export data to an excel sheet and the latter on up import the excel to the sqlserver database with the updates and inserts

    , the problem is that i am able to export it just fine but but now able to import it back again ..

    when i try to open a connection to the excel file this is what i get -- External table is not in the expected format. 

     

    Can someone plz tell me how  should i format my connection  string .. to access this file...

    OR show driect me how to create a true excel file  cause i ve tried exporting to  html and excel .. and now i am really lost

    by the way all these files have to be stored on the clients computer not on the server.

    here is my export method

     

     

    public void ExportToExcel(SqlDataReader dr, string fileName)

    {

    string filepath = getPath(fileName);

    string strLine ="";

    FileStream objFileStream;

    StreamWriter objStreamWriter;

    //Use FileSystem objects to create the .xls file.

    objFileStream = new FileStream(filepath, FileMode.OpenOrCreate, FileAccess.Write);

    objStreamWriter = new StreamWriter(objFileStream);

    //Enumerate the field names and the records that are used to build

    //the file.

    for (int i = 0; i <= dr.FieldCount - 1; i++)

    {

    strLine = strLine + dr.GetName(i).ToString() +
    Convert.ToChar(9);

    }

    //Write the field name information to the file.

    objStreamWriter.WriteLine(strLine);

    //Reinitialize the string for data.

    strLine = "";

    //Enumerate the database that is used to populate the file.

    while (dr.Read())

    {

    for (int i = 0; i <= dr.FieldCount - 1; i++)

    {

    strLine = strLine + dr.GetValue(i).ToString() +
    Convert.ToChar(9);

    }

    objStreamWriter.WriteLine(strLine);

    strLine =
    "";

    }

    //Clean up.

    dr.Close();

     

    objStreamWriter.Close();

    objFileStream.Close();

    }

     

    protected string getPath(string FileName) {

    path = FileManager1.currentPath();

    if (path.Length > 3)

    {

    return path + "\\" + FileName + ".xls";

    }

    else{return path + FileName + ".xls";

    }

     

    }

     

     And here  is the connection method where i am getting the error

    protected OleDbCommand ExcelConnection(string FilePath, string file)

    {

    string xConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+FilePath+@";Extended Properties=""Excel 8.0;HDR=YES;""";

    // create your excel connection object using the connection string

    cnn = new OleDbConnection(xConnStr);

    cnn.Open();  <-- here is were i am getting error: External table is not in the expected format.

    // file is just name them of the worksheet  - by the way when i tried this with html i got an error that worksheet does not exsist eventhough it did ..

    file = file.Replace(".xls", "").Trim();OleDbCommand objCommand = new OleDbCommand("SELECT * FROM ["+file+"$]", cnn);return objCommand;

    }

     

    Thank you

     Owais

    Thursday, October 9, 2008 8:56 PM

Answers

  • Ther problem is that you have created file that is not in XLS file format internally. Excel, as an application, will be able to open generated HTML file, and will recognize it as tabular data, but for the Jet provider you specified that file is in native binary Excel 8.0 format and, of cource, it fails to recognize it. I am not sure if you could open HTML with Jet. It used to be an article about supported Extended Properties, but I cannot find it anymore. What you could try is to specify HTML instead of Excel 8.0 in your connection string. It might work. If not, you would need to create actual Excel content.

     

    There are different ways to export data into Excel and you could find them here

     

    http://support.microsoft.com/kb/306022/

     

    1. Using Automation method gives you full control about what could be generated, but it is much slower than Jet and consumes a lot of memory. In addition Automation is COM-based.

     

    2. Using Jet OLEDB is pretty fast, but does not provide formatting capabilities, and if you do not need any formating, it could be what you need

     

    3. I believe XML format that described in article is not supported by Jet OLEDB provider.

     

    4. You can also try .NET components I have created to work with Excel (you can download them from my web site)


    Val Mazur
    Microsoft MVP

    Friday, October 10, 2008 11:41 AM
    Moderator
  • Find the below method for reading importing data from excel file which works fine

     

     

     

     public static DataSet ReadExcelDataSet(string FileName)
      {
       string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
    
       DataSet output = new DataSet();
    
       try
       {
        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
         conn.Open();
    
         DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    
         foreach (DataRow schemaRow in schemaTable.Rows)
         {
          string sheet = schemaRow["TABLE_NAME"].ToString();
    
          OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
          cmd.CommandType = CommandType.Text;
    
          DataTable outputTable = new DataTable(sheet);
          output.Tables.Add(outputTable);
          new OleDbDataAdapter(cmd).Fill(outputTable);
         }
        }
       }
       catch { }
    
       return output;
      }

    Friday, February 11, 2011 5:42 AM

All replies

  • Ther problem is that you have created file that is not in XLS file format internally. Excel, as an application, will be able to open generated HTML file, and will recognize it as tabular data, but for the Jet provider you specified that file is in native binary Excel 8.0 format and, of cource, it fails to recognize it. I am not sure if you could open HTML with Jet. It used to be an article about supported Extended Properties, but I cannot find it anymore. What you could try is to specify HTML instead of Excel 8.0 in your connection string. It might work. If not, you would need to create actual Excel content.

     

    There are different ways to export data into Excel and you could find them here

     

    http://support.microsoft.com/kb/306022/

     

    1. Using Automation method gives you full control about what could be generated, but it is much slower than Jet and consumes a lot of memory. In addition Automation is COM-based.

     

    2. Using Jet OLEDB is pretty fast, but does not provide formatting capabilities, and if you do not need any formating, it could be what you need

     

    3. I believe XML format that described in article is not supported by Jet OLEDB provider.

     

    4. You can also try .NET components I have created to work with Excel (you can download them from my web site)


    Val Mazur
    Microsoft MVP

    Friday, October 10, 2008 11:41 AM
    Moderator
  • Hi,
    Is there any way to use the Jet OLEDB connetion to import the data from the spreadsheet ML excel file (XML format) 2003?

    Thank you.
    Thursday, February 12, 2009 4:03 PM
  • Find the below method for reading importing data from excel file which works fine

     

     

     

     public static DataSet ReadExcelDataSet(string FileName)
      {
       string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
    
       DataSet output = new DataSet();
    
       try
       {
        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
         conn.Open();
    
         DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    
         foreach (DataRow schemaRow in schemaTable.Rows)
         {
          string sheet = schemaRow["TABLE_NAME"].ToString();
    
          OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
          cmd.CommandType = CommandType.Text;
    
          DataTable outputTable = new DataTable(sheet);
          output.Tables.Add(outputTable);
          new OleDbDataAdapter(cmd).Fill(outputTable);
         }
        }
       }
       catch { }
    
       return output;
      }

    Friday, February 11, 2011 5:42 AM
  • Hello,

    Please check the following thread which has a function to create and populate a sheet with data from a DataTable. To try this out take the results of your data reader and load them into a DataTable i.e. dt.Load(MyReader.ExecuteReader()). The function ExportToExcel uses early binding and works at a fast pace.

    http://social.msdn.microsoft.com/Forums/en-US/winforms/thread/4efb9942-4a29-44ce-b723-ad56b6e31533

    Mary's suggestion although cost money to own is a good consideration. I use Aspose Cells and after one project paid for itself. Since both allow you to try them for free it may be worth the effort.


    KSG

    Wednesday, May 2, 2012 5:16 PM