What code could I use to automate the reformating of the xlsx file described below?

I have attached a small part of a large excel spreadsheet (MS SQL 2008 qry output)  "raw_data" that I need to reformat as shown in the "sample_output" sheet.

The subject and VisitID columns are identical in the raw_data  and "sample_output" sheets.
I need to automate the reformating of the  "Date drawn:" , "Time drawn" and the value in the cell above these two cells (drug level) as shown in the "sample output sheet" ie,  the Date drawn, Time drawn and drug level values for each SubjectID/VisitID in the "raw_data" sheet need to be moved into the corresponding rows for subjectID and visitID for all subjects and visit IDs in the "sample_output" sheet.

Many subject visits have no data - those cells should be left blank in the output file.
Thanks. Please advise.
EEPRN.xls
PDSWSSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FamousMortimerCommented:
By looking at it, it seems to me that the formatting should be done in the query. You can pivot on the data column to provide the output you require.  If you can provide the query I can help with that.

It can be done in Excel, but it would require a lot of programming of post processing, most of which can be done in the query.
0
PDSWSSAuthor Commented:
FamousMortimer:

SELECT     SubjectId, [Site Name], [Visit Name], [Form Name], StartTime, FreeTextAnswer, OptionText, QuestionText, QuestionId
FROM         vCNALLDATA
WHERE     (QuestionId = 1287)


I deleted form name, option text, question text and questionID from the excel output.

Thanks,
0
FamousMortimerCommented:
At first glance I thought that the data column was structured but now I see that there are many typos such as "Date Drawnn ".

Is there another column that specifies what the data column contains?

At any rate, I can provide with with something close shortly, but the typos will be left out
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

FamousMortimerCommented:
Here you go.  You can give it a try, but there are a few dates/times missing because of the inconsistent data.  Not sure how you want to handle that, but let me know if you'd like me to change it up a bit.

select SubjectID,
       VisitName,
       isnull(pvt.[drug_level], '') as drug_level,
       isnull(pvt.[dte], '')        as dte,
       isnull(pvt.[tme], '')        as tme
from   (select SubjectID,
               VisitName,
               case
                 when isdate(ltrim(substring(Data, charindex(':', data, 0) + 1, len(data)))) = 1 then ltrim(substring(Data, charindex(':', data, 0) + 1, len(data)))
                 when isnumeric(data) = 1 then data
                 else ''
               end as data,
               case
                 when isdate(ltrim(substring(Data, charindex(':', data, 0) + 1, len(data)))) = 1
                      and charindex('m', Data) = 0 then 'dte'
                 when isdate(ltrim(substring(Data, charindex(':', data, 0) + 1, len(data)))) = 1 then 'tme'
                 when isnumeric(data) = 1 then 'drug_level'
                 else ''
               end as datatype
        from   dbo.tCNAllData) p
       pivot( max(Data)
            for datatype in ([dte],
                             [tme],
                             [drug_level]) )as pvt
order  by SubjectID 

Open in new window

0
PDSWSSAuthor Commented:
Thanks. Will test soon.

Is there another column that specifies what the data column contains?

No. Another column contains answer IDs.

To clarify - please see attached part of form where drug level, dte and time are entered.
"Date drawn" and "time drawn" are stored in the backend MS SQL table with the
Date and time entered.  Thought this info could help you write your code.
Thanks
Screen-Shot-2013-10-01-at-10.40..png
0
PDSWSSAuthor Commented:
Correction - there is a column that I deleted that indicates  what the column contains.
0
FamousMortimerCommented:
OK shouldn't be a problem them.

Would it be possible to edit the view to discard the "Date/Time Drawn:" text, leaving only the time or date, then provide me an example with the column indicating the 'data type'?

If not possible to edit the view.  Do you have any idea why there are typos in the date/time drawn text?
0
PDSWSSAuthor Commented:
Drug Level, Date Drawn and Time drawn are all stored in one column - "FreeTextAnswer" in the backend table   dbo.answer

vCNAllData is a stored view  not  a  table. Thanks
0
PDSWSSAuthor Commented:
If not possible to edit the view.  Do you have any idea why there are typos in the date/time drawn text?

I introduced those typos when I manually replaced real data with fake data.
Sorry about that.  Please ignore those typos as they do not exist in the tables.
0
FamousMortimerCommented:
This should do it then assuming I referenced all of the fields correctly.

select SubjectID,
       [Visit Name],
       isnull(pvt.[drug_level], '') as drug_level,
       isnull(pvt.[dte], '')        as dte,
       isnull(pvt.[tme], '')        as tme
from   (select SubjectID,
               [Visit Name],
               case
				 when isdate(ltrim(replace(FreeTextAnswer,'Date Drawn:',0))) = 1 then ltrim(replace(FreeTextAnswer,'Date Drawn:',0))
				 when isdate(ltrim(replace(FreeTextAnswer,'Time Drawn:',0))) = 1 then ltrim(replace(FreeTextAnswer,'Time Drawn:',0))
                 when isnumeric(FreeTextAnswer) = 1 then data
                 else ''
               end as data,
               case
                 when isdate(ltrim(replace(FreeTextAnswer,'Date Drawn:',0))) = 1 then 'dte'
                 when isdate(ltrim(replace(FreeTextAnswer,'Time Drawn:',0))) = 1 then 'tme'
                 when isnumeric(FreeTextAnswer) = 1 then 'drug_level'
                 else ''
               end as datatype
        from   dbo.vCNALLDATA) p
       pivot( max(Data)
            for datatype in ([dte],
                             [tme],
                             [drug_level]) )as pvt
order  by SubjectID 

Open in new window

0
PDSWSSAuthor Commented:
Thanks. Tried to run and got the attached error message - "The PIVOT SQL construct or statement is not supported."
Please advise -

We also need to Select QuestionID

and

WHERE     (QuestionId = 1287)

to limit the report to the 3 answers of interest.
0
PDSWSSAuthor Commented:
Error message attached. Also see 2nd error message.
Screen-Shot-2013-10-01-at-12.29..png
Screen-Shot-2013-10-01-at-12.24..png
0
FamousMortimerCommented:
OK try this.  this will fix error message 2 (i forgot to change one of my column names back to FreeTextAnswer)

You should be able to ignore the second message.  You won't be able to view a pivot in graphical view.  Otherwise you may need to talk to your database admin.

select QuestionID,
       SubjectID,
       [Visit Name],
       isnull(pvt.[drug_level], '') as drug_level,
       isnull(pvt.[dte], '')        as dte,
       isnull(pvt.[tme], '')        as tme
from   (select Question ID,
               SubjectID,
               [Visit Name],
               case
                 when isdate(ltrim(replace(FreeTextAnswer, 'Date Drawn:', 0))) = 1 then ltrim(replace(FreeTextAnswer, 'Date Drawn:', 0))
                 when isdate(ltrim(replace(FreeTextAnswer, 'Time Drawn:', 0))) = 1 then ltrim(replace(FreeTextAnswer, 'Time Drawn:', 0))
                 when isnumeric(FreeTextAnswer) = 1 then FreeTextAnswer
                 else ''
               end      as data,
               case
                 when isdate(ltrim(replace(FreeTextAnswer, 'Date Drawn:', 0))) = 1 then 'dte'
                 when isdate(ltrim(replace(FreeTextAnswer, 'Time Drawn:', 0))) = 1 then 'tme'
                 when isnumeric(FreeTextAnswer) = 1 then 'drug_level'
                 else ''
               end      as datatype
        from   dbo.vCNALLDATA
        where  QuestionID = 1287) p
       pivot( max(Data)
            for datatype in ([dte],
                             [tme],
                             [drug_level]) )as pvt
order  by SubjectID 

Open in new window

0
PDSWSSAuthor Commented:
OK. Ran your code and it partially worked this time.

Appeared  capture the  drug level value but only sometimes captured the date and/or time. I have attached a part of the output.
 Any idea why sometimes outputs the date and/or time?  
Thanks
EEcoderesult.xls
0
FamousMortimerCommented:
I just realized I made a silly mistake in the replace function...

select QuestionID,
       SubjectID,
       [Visit Name],
       isnull(pvt.[drug_level], '') as drug_level,
       isnull(pvt.[dte], '')        as dte,
       isnull(pvt.[tme], '')        as tme
from   (select Question ID,
               SubjectID,
               [Visit Name],
               case
                 when isdate(ltrim(replace(FreeTextAnswer, 'Date Drawn:', ''))) = 1 then ltrim(replace(FreeTextAnswer, 'Date Drawn:', ''))
                 when isdate(ltrim(replace(FreeTextAnswer, 'Time Drawn:', ''))) = 1 then ltrim(replace(FreeTextAnswer, 'Time Drawn:', ''))
                 when isnumeric(FreeTextAnswer) = 1 then FreeTextAnswer
                 else ''
               end      as data,
               case
                 when isdate(ltrim(replace(FreeTextAnswer, 'Date Drawn:', ''))) = 1 then 'dte'
                 when isdate(ltrim(replace(FreeTextAnswer, 'Time Drawn:', ''))) = 1 then 'tme'
                 when isnumeric(FreeTextAnswer) = 1 then 'drug_level'
                 else ''
               end      as datatype
        from   dbo.vCNALLDATA
        where  QuestionID = 1287) p
       pivot( max(Data)
            for datatype in ([dte],
                             [tme],
                             [drug_level]) )as pvt
order  by SubjectID 

Open in new window

0
PDSWSSAuthor Commented:
Thanks for sticking with this.  This appears to do it.
One issue - if time is entered as 0700  or 0900 or in other odd formats does not output to report.
Would it be possible to output the time into the tme column no matter what format it is entered, eg text?

Thanks again
0
FamousMortimerCommented:
Here is a slightly different approach.  The only downside is that it does not return a date or time value, but rather a string... which seems to be what you need since it is typed text and may not be entered properly.

select QuestionID,
       SubjectID,
       [Visit Name],
       isnull(pvt.[drug_level], '') as drug_level,
       isnull(pvt.[dte], '')        as dte,
       isnull(pvt.[tme], '')        as tme
from   (select QuestionID,
               SubjectID,
               [Visit Name],
               ltrim(rtrim(replace(replace(FreeTextAnswer, 'Date Drawn:', ''), 'Time Drawn:', ''))) as data,
               case
                 when len(( replace(FreeTextAnswer, 'Date Drawn:', '') )) < len(FreeTextAnswer) then 'dte'
                 when len(( replace(FreeTextAnswer, 'Time Drawn:', '') )) < len(FreeTextAnswer) then 'tme'
                 when isnumeric(FreeTextAnswer) = 1 then 'drug_level'
                 else ''
               end                                                                                  as datatype
        from   dbo.vCNALLDATA
        where  QuestionID = 1287) p
       pivot( max(Data)
            for datatype in ([drug_level],
                             [dte],
                             [tme] ) )as pvt
order  by SubjectID 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PDSWSSAuthor Commented:
FamousMortimer-

Great job - A+
I greatly appreciate your sticking with it until you got it right.

Thanks again
0
PDSWSSAuthor Commented:
Great Solution and thanks again.
0
FamousMortimerCommented:
No problem, thanks for the grade!

It was a little difficult not having the database in front of me but eventually we got it right.
0
PDSWSSAuthor Commented:
FamousMortimer: I know the question is closed and I awarded you the points.
Your code has been working very well except that I need drug level to be txt and not numeric.
I have found that users will sometimes enter some text,  ie  N/A, none 0.45 leq or  0,5.
In these cases the cell is blank.  
Similar to what you changed for the  "time" field.
What needs to be done to your great code to change the drug_level field from numeric to txt?

Thanks again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.