Import DTS data issue 1

[JimFL]

Can anybody tell me how I can import data from an excel spreadsheet format to my database

The excell format is:

userID Answers set 1 Answers set 2
1 4,4,2 4,4,4,4,5,5,3,5,4,4,4,4,5
2 4,4,4 3,3,3,4,5,5,5,5,4,4,4,5,4

The problem is that the excel spreadsheet has multiple answers in two different cells.

I want to be able to add a single row for each answer in set 1 and set 2
ie


ID , userID, Questionset ANSWER
1 1 1 4
2 1 1 4
3 1 1 2
4 1 2 4
5 1 2 4 ... and so on


Can anybody help with this?

 

[gmmastros]

This can be done in various ways.

Here's one...

Declare @Temp Table(userId Integer, Answer1 VarChar(2000), Answer2 VarChar(2000))

Insert Into @Temp Values(1,'4,4,2','1,4,4,4,5,5,3,5,4,4,4,4,5')
Insert Into @Temp Values(2,'4,4,4','2,3,3,4,5,5,5,5,4,4,4,5,4')

Declare @Output
Table	(ID Integer identity(1,1),
		UserId Integer,
		QuestionSet Integer,
		Answer integer
		)

While exists(Select * From @Temp Where Answer2 Like '%,%')
	Begin

		Insert 
		Into	@Output(UserId, QuestionSet, Answer)
		Select	UserId,
				2,
				Left(Answer2, CharIndex(',', Answer2)-1)
		From	@Temp

		Update	@Temp
		Set		Answer2 = Right(Answer2, Len(Answer2) - CharIndex(',', Answer2))
	End

Insert Into @Output(UserId, QuestionSet, Answer)
Select UserId, 2, Answer2
From   @Temp

Select * From @Output

Notice that I use a table variable to represent your 'incoming data'. You will, of course, need to change this to look at your data instead. Also notic that I am ONLY accomodating the 'answer 2' field. Modifying this to work with Answer 1 should be very similar to what I show here.

So basically, my suggestion would be to import the data in to a staging table, run some variation of the code I show above to put the data in to the real table.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JimFL]

I had started to input the data into a dummy table - will use your help to import the data into the real table..

Thanks for your help

 

[JimFL]

Ive been able to use the code above to resolve my data issue but I really need another parameter to store the actual answerNo in the @output table.

So when it inserts in the first instance the answer would be 1 and then incremented as it goes along, However I need to know when to reset the answerNo back to 1 for each userID.

I think the code would be something like..

declare @answerNo = 1

While exists(Select * From @Temp Where Answer2 Like '%,%')
Begin

Insert
Into @Output(UserId, QuestionSet, AnswerNo, Answer)
Select UserId,
2,
@answerNo,
Left(Answer2, CharIndex(',', Answer2)-1)
From @Temp

Update @Temp
Set Answer2 = Right(Answer2, Len(Answer2) - CharIndex(',', Answer2))
@answerNo = @answerNo + 1
-- how do I reset?
End

Insert Into @Output(UserId, QuestionSet,answerNo, Answer)
Select UserId, 2, @answerNo,Answer2
From @Temp

I must be along the right lines but am confused as to how I do this properly. Can anybody help?

 

[gmmastros]

There are a couple problems with the code you posted. Let me show you...

[!]declare @answerNo Integer
Set @AnswerNo = 1[/!]

While exists(Select * From @Temp Where Answer2 Like '%,%')
    Begin

        Insert 
        Into    @Output(UserId, QuestionSet, AnswerNo, Answer)
        Select    UserId,
                2,
                @answerNo,
                Left(Answer2, CharIndex(',', Answer2)-1)
        From    @Temp

        Update    @Temp
        Set        Answer2 = Right(Answer2, Len(Answer2) - CharIndex(',', Answer2))

        [!] Set [/!]@answerNo = @answerNo + 1
-- how do I reset?
    End

Insert Into @Output(UserId, QuestionSet,answerNo, Answer)
Select UserId, 2, @answerNo,Answer2
From   @Temp

First, you need to declare a variable with it's data type on one line. Then, initialize that variable on another line.

Second, when setting a variable, you need to use the keyword [!]Set[/!].

You shouldn't need to reset the @AnswerNo variable because each time through the loop we 'peel off' the first answer for all userid's. Then, we increment the answer number and peel off the second answer. If you have a similar process for accomodating the answer1 field, and you re-use the same variable, then you will need to reset the @AnswerNo variable before you start the next loop.

userID      Answers set 1               Answers set 2
1             4,4,2             [blue][b]4[/b][/blue],[green][b]4[/b][/green],[red][b]4[/b][/red],4,5,5,3,5,4,4,4,4,5
2             4,4,4             [blue][b]3[/b][/blue],[green][b]3[/b][/green],[red][b]3[/b][/red],4,5,5,5,5,4,4,4,5,4

With the sample data shown, this algorithm will first 'peel off' and the [blue]blue[/blue] values. On the next iteration through the loop, the [green]green[/green] values will be taken, the third iteration will get the [red]red[/red] values, and so on.

I hope this makes sense.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JimFL]

Thanks for you help however still a little unsure of how that would work ?

If I look at the database entries for userID=1 they are

ID UserID Qset Answer - Need AnswerNo

1 1 2 4 - 1
1191 1 2 4 - 2
2381 1 2 2 - 3
3571 1 4 4 - 1
4761 1 4 4 - 2
5951 1 4 4 - 3
7141 1 4 4 - 4


This shows me how it is iterating throw through.

Are you sure that the @answerNo parameter would insert these values? Why wouldnt I need to reset the variable?

 

[JimFL]

It did work - thanks. Gave it a try and there was no need to reset the variable for the inner loop. I did it for both collumns and reset on the variable on the second loop to get back the second series of results.

Cheers again.