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:
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
)
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.
Submit an article or tip