Column name mismatch in Excel while reading it using c#

Question

0.00/5 (No votes)

See more:

.NET1.0

.NET3.0

, +

Am reading one excel using c# code, and am saving it in to datatable
but the column heading in datatable is differnt from the excel.
Column name in excel seems to be trimed. why?

What could be the reason?

Note:My column name in excel is lenghty. ( " 'sdsd' Organisation Name if it is different from the dwiccc Organisation " this is excel column but its coming like

" sdsd' Organisation Name if it is different from the dwiccc Org" in my datatable)

below is the connction string am using
OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 8.0");

Please help me to solve this.

Any solution?

Posted 14-Nov-13 3:15am

Am Gayathri

Updated 14-Nov-13 4:48am

v2

Add a Solution

Comments

Am Gayathri 14-Nov-13 9:21am

help..........:( :( :(
Need to close this issue today itself..

Am Gayathri 14-Nov-13 10:48am

Any solution?

CHill60 14-Nov-13 14:48pm

I gave you the solution 2 hours before you posted this comment. If that solution doesn't work for you then post a comment against the solution explaining what the problem is now.

Am Gayathri 15-Nov-13 1:45am

As per your solution i have to trim the column names, Am looking for a solution which doesn't require to change column names.
If there is no solution like that i will do this one.
Thanks a lot...

CHill60 15-Nov-13 4:39am

If the maximum column length on your database is 64 characters then you will have to live with that or change RDMS.
See Maciej Los' solution regarding aliases if that helps.
I would also advise you to read up on SQL naming standards - they are suggested for a reason - I'll update my solution with a link

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)

CHill60 · Answer 1 · 2013-11-14T03:30:00

Solution 1

Your column name is too long for the database you are trying to store it on.

Stick to 64 characters or less.

Actually MUCH less, this is far too unwieldy as a column name and you should avoid spaces and special characters as well

[Edit]
An example of (good) SQL server naming standards[^]

Posted 14-Nov-13 3:30am

CHill60

Updated 14-Nov-13 22:40pm

v2

Comments

Am Gayathri 14-Nov-13 9:32am

Thanks for your reply

Maciej Los 14-Nov-13 16:30pm

+5!
Please, see my answer ;)

kiquenet.com 25-Jul-18 10:00am

not found

CHill60 25-Jul-18 10:14am

Thank you for letting me know that the link is broken. Here is an alternative - an article here on CodeProject
SQL Server Table and Column Naming Conventions[^]

CHill60 25-Jul-18 10:17am

Here is the fixed link SQL server naming conventions - they moved the document. Warning - this link is to a PDF

Maciej Los · Answer 2 · 2013-11-14T10:29:00

Solution 2

Solution 1 is very, good...

I would recommend you to use aliases ;)

Please, read it: Getting Data between Excel and SQL Server using ODBC [^]. There you'll find many useful information.

Posted 14-Nov-13 10:29am

Maciej Los