commas in excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

commas in excel

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-05-10 : 11:38:05

Hi

I have an excel file with 2 columns:
article art_group
kb art,tp,key
portal grup,port,li

I have a table in the database called tb_artGroups with the columns

article art_group

Now for each row in the excel i have to write the values after the comma in a new row such as:
article art_group
kb art
kb tp
kb key
portal grup
etc...

How can i do that with SSIS?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-11 : 02:13:59

May be you can try like this:-

select * 
into #Temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel8.0;Database=YourExcelFilepathHere;HDR=YES', 
'SELECT * FROM [Sheet1$]')

This will get your excel data to temporary table

Then use a query like this. Here RowToColumns will be a function which takes a comma seperated string of values and returns them as a table of values.

SELECT t.article,f.value
FROM #Temp t
CROSS APPLY dbo.RowToColumns(t.art_group,',') f

and function will be like :-

CREATE FUNCTION dbo.RowToColumns
(@String varchar(max),
@Delimiter char(1))
RETURNS @StringArray table (
		Value varchar(1000)
	)
AS
DECLARE @TempXML xml
SELECT @TempXML=CAST('<val>' + REPLACE(@String, @Delimiter, '</val><val>') + '</val>' AS XML)

INSERT INTO @StringArray
SELECT a.array.value('.', 'varchar(max)') 
FROM @TempXML.nodes('/val') a (array)
GO

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-05-11 : 06:39:09

hi

Thanks for the reply. I tried the following but i get an error:

select * 
into #Temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel8.0;Database=C:\Documents and Settings\user\My Documents\20080501_Customer_Full_Customers_list(1).xls;HDR=YES', 
'SELECT * FROM [20080501_Customer_Full_Customer$]')

Error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-12 : 05:44:05

quote:
Originally posted by collie

hi

Thanks for the reply. I tried the following but i get an error:

select *
into #Temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel8.0;Database=C:\Documents and Settings\user\My Documents\20080501_Customer_Full_Customers_list(1).xls;HDR=YES',
'SELECT * FROM [20080501_Customer_Full_Customer$]')

Error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-05-13 : 09:18:24

Hi,

I tried the following:

select t.Department ,t.ArticleGroups ,f.value
FROM #Temp1 t
CROSS APPLY dbo.RowToColumns(t.ArticleGroups ,',') f

However, i get the following error:
Incorrect syntax near '.'.

If i replace (t.ArticleGroups ,',') with for example
('texax,wahs,try',',') then it works.
Can't figure out why this happens.

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources