Error uploading Excel file from ASP.NET application to MySQL

Question

I'm working on a web application (ASP.NET) with a MySQL database. When I try to upload an Excel file (.xlsx) via this application I have no issues. The issue begins when the file is more than 24904 records.

At that point I get the following error:

This table contains cells that are outside the range of cells defined in this spreadsheet.

and it writes the first 24904 records.

I tried to split the load in multiples batches and it did not work.

Any thoughts?

Dim connExcel As New System.Data.OleDb.OleDbConnection(conStr)
Dim cmdExcel As New System.Data.OleDb.OleDbCommand()
Dim dt As New DataTable()
Dim dataset As New DataSet
Dim x As Integer = 2
Dim y As Integer = 20001
Dim range As String = "A" + x.ToString + ":" + "I" + y.ToString

cmdExcel.Connection = connExcel

If erro = 0 Then
    Try

        For i As Integer = 0 To 50
            connExcel.Open()

            dataset.Reset()
            dataset.Clear()

            Dim oda As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$" & range.ToString & "]", connExcel)
            oda.TableMappings.Add("Table", "ExcelTest")

            oda.Fill(dataset)
            connExcel.Close()

            If dataset.Tables(0).Rows.Count > 0 Then
                SendToDB(dataset)
            Else
                i = 50
            End If

            x = x + 20000
            y = y + 20000

            range = "A" + x.ToString + ":" + "I" + y.ToString
        Next

        Label7.Visible = True
        Label7.Text = "The information has been written successfully from 0 to " + y.ToString

    Catch ex As Exception
        Label9.Visible = True
        Label9.Text = "Database Error 2:" + ex.Message
        connExcel.Close()
    End Try
End If

Have you verified that the data on row 24905 has no extraneous data outside the defined header columns? — dhirschl, Nov 13 '12 at 02:25
dhirschl - I checked the line and it does not have anything weird. I deleted it and got the same issues with the next line — Orlando Lares, Nov 13 '12 at 14:13
Tim Williams - I get the error readingthe excel after the record 24904, which seems to be a limitation of some sort. — Orlando Lares, Nov 13 '12 at 14:15
did u try putting extra rows after that to a new sheet ? (Page1,Page 2) likewise? — Rohith Nair, Dec 19 '12 at 11:01
you had specified the excel range upto 20001,that may be reason,since it is not accepting more records.Try to use like this range = xlWorkSheet.UsedRange; — MahaSwetha, Jan 10 '13 at 08:59
What I suggested to you is try to import the csv data directly by Mysql command to see if it is ok. for example:`LOAD DATA INFILE 'path/file.txt' INTO TABLE your_table; ` — Joe.wang, Jan 16 '13 at 09:38

score 1 · Answer 1 · answered Aug 22 '16 at 13:54

I Don't Use OLDB or excel since its create many issue while reading excel

I use ExcelDataReader https://github.com/ExcelDataReader/ExcelDataReader

Try This

Install-Package ExcelDataReader

public static DataSet GetExcelDataSet(string filePath)
    {
        FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

        IExcelDataReader excelReader;
        if (Path.GetExtension(filePath) == ".xls")
        {
            //Choose one of either 1 or 2
            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
        }
        else
        {
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
        }
        //Choose one of either 3, 4, or 5
        //3. DataSet - The result of each spreadsheet will be created in the result.Tables

        ////////DataSet result = excelReader.AsDataSet();

        //4. DataSet - Create column names from first row
        excelReader.IsFirstRowAsColumnNames = true;
        DataSet result = excelReader.AsDataSet();

        //5. Data Reader methods
        //while (excelReader.Read())
        //{
        //    //excelReader.GetInt32(0);
        //}

        //6. Free resources (IExcelDataReader is IDisposable)
        excelReader.Close();
        return result;
    }

score 0 · Answer 2 · answered Jan 17 '13 at 21:06

I suspect you've hit an Excel limit (like the number of table mappings you can add)

Try starting your loop count at a higher number and see if it's the count of how many times you do this rather than the data that causes the problem. Maybe something like:

Dim x As Integer = 40002
Dim y As Integer = 60001

score 0 · Answer 3 · answered Mar 14 '14 at 10:36

0

you can reset your range......by adding counter variable....increment it for specific range...and reset it...

answered Mar 14 '14 at 10:36

firefly

419
1
8
18

score 0 · Answer 4 · edited Jul 02 '15 at 09:50

You also can use LOAD DATA in a file query to load excel file to the database. The syntax for the query is:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

sams · Answer 5 · 2015-11-28T06:22:27.167

0

Remove range use Sheet1 ...and retest Dim oda As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", connExcel)

edited Nov 28 '15 at 06:22

answered Nov 28 '15 at 05:17

sams

21
8

score 0 · Answer 6 · answered May 13 '16 at 11:21

The Excel limit is bigger than that. You have cells containing either hidden characters or spaces that fall outside of the specified range (as the error message states) check the columns to the right of your 'last' column on the error rows. I think you'll find they contain something.

An easy way to test, make a fresh new file with 50 000 records, filled with new data. I think you'll find it works.

Error uploading Excel file from ASP.NET application to MySQL

6 Answers6