excel delimiting not working on a regular basis

Question

0.00/5 (No votes)

See more:

Hi All,

I have an excel workbook delimited by "|" and i am using the following code to read the file using the following code, somedays back it was working fine but not now. I am using excel interop 12.0 and my file is of xlsx format

Please help

private void button2_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == "")
            {
                MessageBox.Show("Folder not selected", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }
            else
            {
                try
                {
                    string path = textBox1.Text;
                    string Filename = path.Substring(path.LastIndexOf('\\') + 1);
                    using (SqlConnection Connection = ConnectionManager.GetConnection())
                    {
                        Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
                        Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(Filename, 0, true, 6, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "|", true, false, 0, true, false, false);
                        Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
                        Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
                        int rowIndex = 1;
                        int colIndex1 = 1;
                    //int index = 0;
                    a: while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2 != null)
                        {
                            //int cindex = 0;
                            tblColumns.Clear();
                            while (colIndex1 < 7)
                            {
                                //colIndex1 = colIndex1 + cindex;
                                if (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2 == null)
                                {
                                    tblColumns.Add("");
                                }
                                else
                                {
                                    string val1 = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2.ToString();
                                    tblColumns.Add(val1);
                                }
                                colIndex1++;
                            }
                            string query = "";
                            query = "insert into singlebarcode values (";
                            foreach (string line in tblColumns)
                            {
                                query = query + "'" + line + "',";
                            }
                            string query1 = query.Substring(0, query.Length - 1);
                            query2 = query1 + ")";
                            SqlCommand command = new SqlCommand(query2, Connection);
                            command.CommandType = CommandType.Text;
                            command.ExecuteNonQuery();
                            count++;
                            rowIndex++;
                            colIndex1 = 1;
                            goto a;
                        }
                        listBox1.Items.Add(count + " rows inserted");
                        ExcelObj.Workbooks.Close();
                        ExcelObj.Quit();
                    }
                    
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    ExcelObj.Quit();
                }
                
            }
        }

[edit]"ignore HTML..." option disabled, code blocks tidied up - OriignalGriff[/edit]

Posted 19-Apr-11 20:12pm

Eager2Lern

Updated 19-Apr-11 20:16pm

OriginalGriff

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2011-04-19T20:26:00

Two things:
1) SQL syntax.
Your insert statement is:

string query = "";
query = "insert into singlebarcode values (";
foreach (string line in tblColumns)
{
    query = query + "'" + line + "',";
}
string query1 = query.Substring(0, query.Length - 1);
query2 = query1 + ")";

Which amounts to:

string query2 = "INSERT INTO singlebarcode VALUES ('from excel')";

SQL syntax for an insert command requires the columns list:

string query2 = "INSERT INTO singlebarcode (myColumnName) VALUES ('from excel')";

2) Do not do it that way anyway! You leave yourself wide open for what is called an SQL Injection attack (Google "Bobby Tables" for find out what I mean). Use parametrized queries instead:

string query = "INSERT INTO singlebarcode (myColumn1, myColumn2) VALUES (@MC1, @MC2)";
SqlCommand command = new SqlCommand(query, Connection);
int i = 1;
foreach (string line in tblColumns)
{
    command.Parameters.AddWithValue("@MC" + i.ToString(), line);
    i++;
}

I suspect that you need to insert each line from the Excel file into a different row in you DB, but without seeing your DB layout, I can't tell.

Kim Togo · Answer 2 · 2011-04-19T20:21:00

What exception are you getting in the try catch ?

In this line of code. You have to escape the string value in variable "line". Else when variable "line" haves a value of "It's all about love" you will end up with a SQL statement that is invalid.

C#

foreach (string line in tblColumns)
{
  query = query + "'" + line + "',";
}

The best way to build your SQL statement is using a StringBuilder and SqlParamenter.

excel delimiting not working on a regular basis

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0