CODEWITHSUNDEEP
sqlsql-serveretl
user3697498
user3697498

Reputation: 115

Export from Excel to SQL Server with Excel Column of Multiple Values

I am fairly new to database design although I have some SQL skill. I have an excel sheet that I wish to upload to SQL Server. The issue is I have a column in Excel that has multiple values separated by "/".

For example:

Fruit Banana/Pear/Orange Pear/Raspberry/... Banana

...

I want to split the cell based on "/" which I am fine doing. Then put the values into a table in SQL Server. However, there is no defined amount of "Fruits" that can be in the Excel cell so I need to allow for multiple table fields.

Does anyone have suggestions on how to do this? I wrote an ADODB connection to export from Excel to SQL Server but don't know how handle this cell.

Thank you

Upvotes: 0

Views: 141

Answers (3)

Freek Nortier
Freek Nortier

Reputation: 780

Use a Bridge Table. This way each row in Fruit table can have N types.

enter image description here

Description values in table Types will be Banana, Pear, Orange, Raspberry, etc.

Upvotes: 0

Keith O'Neill
Keith O'Neill

Reputation: 77

When you design the table it will have to have a defined number of columns.

The simplest approach would be to assign an ID for each group.

ID  Fruit
1   Banana
1   Pear
1   Orange
2   Pear
2   Raspberry
3   Banana

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81990

Another option is to split your data after it has been loaded. For example

Declare @YourTable table (ID int,Fruit varchar(150))
Insert into @YourTable values
(1,'Banana/Pear/Orange'),
(2,'Pear/Raspberry/Apple'),
(3,'Banana')


Select A.ID
      ,B.Key_Value
 From @YourTable A
 Cross Apply (Select * from [dbo].[udf-Str-Parse](A.Fruit,'/')) B

Returns

ID  Key_Value
1   Banana
1   Pear
1   Orange
2   Pear
2   Raspberry
2   Apple
3   Banana

The UDF

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End

Upvotes: 2

Related Questions