April 22, 2015 at 1:55 pm
I don't even know if this is possible, but I thought I'd ask. 😉
I have a spreadsheet (a sample is attached). When I get it from the client, it looks kind of like what you see in the "Original after Initial Edits" sheet. I do have to do some edits to get it even looking like that (with the Session filled in all the way down, and the "session objectives" category filled in).
The goal is to get it the way you see it in the "Goal" spreadsheet.
Basically, it's all based on CategorySortOrder and QuestionSortOrder, neither of which are filled in initially. CategorySortOrder remains the same until you encounter a row where Question is blank, and then it goes up by one, until you get to the end of that session.
QuestionSortOrder is the sort order of that particular question within the category, so it goes up by one until you get to a blank Question (this is after getting rid of all rows where Question = 'Rate your achievement of the session objectives - At the end of this program, I was able to:'). Then it resets to 1 again.
How I've been doing it (and will continue to do it today because it needs to get done):
- In Excel, I manually type in all of the CategorySortOrder and QuestionSortOrder values, based on where I find blank rows.
I then sort by CategorySortOrder and QuestionSortOrder, and delete all of the rows at the end that have neither.
Then I do the final sort, by Session, CategorySortOrder, and QuestionSortOrder.
Then it's ready for uploading to SQL Server (at which point, there's processing done there as well, but that's already programmed).
I would very much like to skip this manual step, as it takes about 45 minutes to do (the real file is much longer than this), and just have TSQL handle it automatically. First it would need to clear all rows where Question = 'Rate your achievement of the session objectives - At the end of this program, I was able to:'. Then it would need to correctly fill in the CategorySortOrder and QuestionSortOrder values in the remaining rows, based on where it finds nulls in the Question field, then delete all of the rows where Question is Null.
If it's possible. :hehe: Please let me know even if it isn't.
Many thanks!
April 23, 2015 at 2:21 am
This shouldn't be too hard, can you post the DDL for the table and the data as an insert statement?
😎
April 23, 2015 at 6:35 pm
Well, this would be part of the problem. 😛 There is no table, and definitely no insert statements; there's just the spreadsheet (attached in the OP). I've been making all the fixes in the spreadsheet and then importing using the Import/Export wizard, but what I'd like to do is import the attached spreadsheet as-is (looking like it does in the "Original with edits" sheet) and then have TSQL do all the work somehow, to get the data looking like it does in the "Goal" sheet. That's the plan, or the hope, at least. 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply