Click here to Skip to main content
15,878,231 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi!!! i want to get data from Excel and save into the database. The problem is, if the excel data cell in any of the column, contains two or more values in a single cell splitted by newline('\n'). Then i have to generate the primary key and separate the values respectively and save the values in the database.

Excel Data Like this
VB
Department     Employees
IT              A
                B
                C
                D
Accounts
HouseKeeping    E

Admin           F 


Need to be like this:

VB
Department  Employees
IT            A
IT            B
IT            C
IT            D
Accounts      E
HouseKeeping  E
Admin         F


Note: There is no primarykey and we have to generate primary key at runtime!

please help me...
Thanks in Advance!!!!!
Posted
Updated 25-Apr-13 23:27pm
v2
Comments
Maciej Los 26-Apr-13 2:57am    
What have you done so far?
J.D.Gopinath 26-Apr-13 5:54am    
I can able to read the excel file and save it in a datatable exactly like excel input data. But i don't know how to split the data in a cell containing '\n' (new line) generically.
Maciej Los 26-Apr-13 5:59am    
Improve your question and share the code. Write where exactly the problem is...
kk2014 26-Apr-13 7:05am    
you can split like..
string str=yourcell.split('\n');
some thing like that...

There is no simply way to achieve that, especially when both columns contain signs of new line: \n or CHR(10).

1. way is to use VBA
But you can't use VBA with ASP.NET.

2. way is to use stored procedure[^] on sql server side with CTE[^]
Have a look at example query:
SQL
DECLARE @names AS TABLE (aName NVARCHAR(50), belongsTo NVARCHAR(30))

INSERT INTO @names (aName, belongsTo)
SELECT * 
FROM (
	SELECT Department AS aName, 'Department' AS belongsTo
	FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=F:\NewLineInCell.xls', 'SELECT * FROM [Sheet1$]')
	WHERE NOT Department IS NULL 
	UNION ALL
	SELECT Employees AS aName, 'Employees'  AS belongsTo
	FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=F:\NewLineInCell.xls', 'SELECT * FROM [Sheet1$]')
	WHERE NOT Employees IS NULL) AS DT

;WITH CteNames AS
(
	SELECT LEFT(aName, CHARINDEX(CHAR(10),aName) -1) AS tmpName, RIGHT(aName, LEN(aName)-CHARINDEX(CHAR(10),aName)) AS Remainder, belongsTo
	FROM @names
	WHERE CHARINDEX(CHAR(10),aName)>0
	UNION ALL
	SELECT LEFT(Remainder, CHARINDEX(CHAR(10),Remainder) -1) AS tmpName, RIGHT(Remainder, LEN(Remainder)-CHARINDEX(CHAR(10),Remainder)) AS Remainder, belongsTo
	FROM CteNames
	WHERE CHARINDEX(CHAR(10),Remainder)>0
	UNION ALL
	SELECT Remainder As tmpName, NULL AS Reminder, belongsTo
	FROM CteNames
	WHERE CHARINDEX(CHAR(10),Remainder)=0 --AND Remainder IS NULL
)
SELECT DISTINCT tmpName, belongsTo
FROM CteNames
UNION ALL
SELECT DISTINCT aName AS tmpName, belongsTo
FROM @names
WHERE CHARINDEX(CHAR(10),aName)=0
ORDER BY belongsTo, tmpName


Above query returns:
tmpName         belongsTo
-------------------------
Accounts	Department
Admin		Department
HouseKeeping	Department
IT		Department
A		Employees
B		Employees
C		Employees
D		Employees
E		Employees
F		Employees


All what you need to do now, is to define a way to insert data into your tables.

BTW: i have added new line in cell, using combination of keys: left ALT + Enter.
 
Share this answer
 
v2
C#
string cellAsString;        // populate this by reading excel cell
cellAsString = "A\nB\nC\n\nD"; //This is just example data

//Split the string into an array of strings
//this also caters for double newlines with nothing in between
string[] cellSplit = cellAsString.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries);

foreach (string a in cellSplit)
{
    // write a database record for this Employee record
    Debug.Print(a);     // Demo only - You will need using System.Diagnostics; for this
}

Output
A
B
C
D
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900