How to split cell of excel sheet and save it into different column of database

Question

0.00/5 (No votes)

See more:

Hello SIr
please help me. I want to split the cell of excel and save it into different columne.
I want to split column: 111000/2008 .
I want to save registration number=111000
passing year:2008.

Please help. I need to save it in database

Posted 15-Apr-15 3:38am

Aone starteam

Updated 15-Apr-15 4:31am

v2

Add a Solution

Comments

Maciej Los 15-Apr-15 10:13am

Passing year 2007? I see: 111000/2008...

Aone starteam 15-Apr-15 10:31am

its 2008

Maciej Los 15-Apr-15 10:38am

Please, see my answer ;)

1 solution

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-04-15T04:35:00

Solution 1

Steps to do:
1. Create OleDbConnection[^] and use ConnectionString[^] as follow:

C#

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

2. Create OleDbCommand:

SQL

SELECT LEFT([FieldNameOrColumnNo], LEN([FieldNameOrColumnNo]-5) AS RegNo,
    RIGHT([FieldNameOrColumnNo], 4) AS PassYear
FROM [SheetName$]

3. Create and Execute OleDbReader[^]
4. Create DataTable[^] and load data into it from OleDbReader[^].
5. That's all!

Posted 15-Apr-15 4:35am

Maciej Los

Updated 15-Apr-15 4:37am

v2

Comments

Aone starteam 15-Apr-15 11:00am

i have 8 column in that excelsheet

Maciej Los 15-Apr-15 11:06am

So what? Just add another columns into select statement...

Aone starteam 15-Apr-15 11:11am

Thanks........ I will try it

Aone starteam 16-Apr-15 3:56am

Thanks its works

Maciej Los 16-Apr-15 4:56am

You're very welcome ;)