0

I am using the code below to read data from a excel sheet and add the data into a data sheet but when reading the file and inspecting the data in the data-table some of the data is missing.. I have been looking at this and have no idea why this would be the case.

excelConnectionString =
                  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
                      " ;Extended Properties=Excel 12.0";
            try
            {
                // Create Connection to Excel Workbook
                using (OleDbConnection connection =
                new OleDbConnection(excelConnectionString))
                {
                    connection.Open();
                    System.Data.DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string[] excelSheet = new String[dt.Rows.Count];
                    int sheet = 0;
                    foreach (DataRow row in dt.Rows)
                    {
                        excelSheet[sheet] = row["Table_Name"].ToString();
                        sheet++;

                    }
                    for (int i = 0; i < excelSheet.Length; i++)
                    {
                        OleDbCommand command = new OleDbCommand
                             ("Select  * FROM [" + excelSheet[i] + "]", connection);

                        adapter.SelectCommand = command;
                        adapter.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }
1
  • Are you talking about the Data inside of Columns? Commented Nov 15, 2012 at 13:10

3 Answers 3

1

No one can reproduce your question and find out certain problem. But you can find the similar question here.

Read Excel file through Datagridview

Export Excel to Datatable

Sign up to request clarification or add additional context in comments.

Comments

0

Using OledbConnection to read in Excel Files you will run into a problem where by the limit on characters for each column is 255.

You could try adding "IMEX=1;" to the end of you connection string, but I'm not sure that will solve your issue.

1 Comment

Is there more than 255 character in any of the columns?
0

If you use OleDbConnection you will have the limitation of reading only 255 columns. I ran into this issue with big sheets and had to change the approach.

Link with a discussion about the issue: http://social.msdn.microsoft.com/Forums/office/en-US/0678e022-b435-4f47-a79c-73780301cfb3/using-ace-120-to-import-an-excel-worksheet-with-more-than-255-columns

It seems that's an old and known issue that will not be resolved for a while.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.