How To Remove Extra Column From Datatable When I Have Taken All Columns From Excel File

Question

0.00/5 (No votes)

See more:

Hi,

I have taken all columns from xls file. Actually xls file cointain 10 columns but i received more than 10 columns like F12,F13 Like this. So, how i can received exact 10 columns.

<code>

C#

public static string[] GetColumnsName(DataTable dt)
        {
            string[] columnNames = null;
            if (dt != null && dt.Rows.Count > 0)
            {
               columnNames = dt.Columns.Cast<DataColumn>()
                               .Select(x => x.ColumnName)
                               .ToArray();
            }
            return columnNames;
        }

Posted 11-May-15 20:16pm

AshishvermaMCA

Add a Solution

Comments

Naveen Kumar Tiwari 12-May-15 2:38am

You can achieve this using excel C# interop class..

Kornfeld Eliyahu Peter 12-May-15 2:40am

Can you show the exact query you run to get dt?

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 12-May-15 3:00am

You need to apply logic where you get that DataTable dt.

AshishvermaMCA 12-May-15 3:18am

I am using this code to take data from excel file-

oledbConn.Open();
object dt = oledbConn.GetSchema("Tables").Rows[0]["TABLE_NAME"];

OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + dt.ToString() + "]", oledbConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
oleda.Fill(ds, "WellCareMCD");
oledbConn.Close();

2 solutions

Solution 1

Follow this link...May this help You....
How to Fixed the Excel Column Width[^]

Happy Coding

Posted 11-May-15 20:40pm

Naveen Kumar Tiwari

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)

Maciej Los · Accepted Answer · 2015-05-11T21:26:00

Solution 2

AshishvermaMCA wrote in comment:

I am using this code to take data from excel file-

C#

oledbConn.Open();
object dt = oledbConn.GetSchema("Tables").Rows[0]["TABLE_NAME"];
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + dt.ToString() + "]", oledbConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
oleda.Fill(ds, "WellCareMCD");
oledbConn.Close();

Replace:

C#

OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + dt.ToString() + "]", oledbConn);

with:

C#

OleDbCommand cmd = new OleDbCommand("SELECT ColumnName1, ColumnName2, ColumnNameX FROM [" + dt.ToString() + "]", oledbConn);

EDIT
For dynamic version, please see:
Excel Add-in Framework for Validating and Exporting Data[^]
It might be helpful too: Reading an Excel Sheet into a DataTable, generic method[^]

Posted 11-May-15 21:26pm

Maciej Los

Updated 12-May-15 1:00am

v2

Comments

AshishvermaMCA 12-May-15 3:42am

No I can't replace - SELECT * FROM [" + dt.ToString() + "] with SELECT ColumnName1, ColumnName2, ColumnNameX FROM [" + dt.ToString() + "]

Actually my objective is When i am going to upload Excel file need to check excel columns is exact with my column which is used in code like
ds.Tables[0].Rows[i]["ColumnName1"].ToString();

AshishvermaMCA 12-May-15 3:44am

Hi Maciej Los,
I will face one more problem when i an using OleDbCommand cmd = new OleDbCommand("SELECT ColumnName1, ColumnName2, ColumnNameX FROM [" + dt.ToString() + "]", oledbConn);

because if user change the column name in Excel file then how i will handle this.

Maciej Los 12-May-15 3:50am

OK, you need to use "dynamic" version. I'll try to update my answer.

AshishvermaMCA 12-May-15 8:00am

Thanks Maciej Los
For giving solution.

Maciej Los 12-May-15 8:28am

You're very welcome ;)