SQL list to Excel in transposed format

[UnsolvedCoding]

I am working on a project that I took over. The gal who did this project would dump SQL to Notepad instead of putting it in Excel. The problem is that I have been given the task of not only putting a list of information from SQL into Excel but of sorting it in chronological sequence from left to right instead of top to bottom.

The claim number is the key and to remove enteries without notes a left outer join is used. The code runs in SQL. The notes have a date and to save hassle the date and the note are going to be put into the same cell.

First - How can I get the list in SQL to populate in Excel?

Second - How can I get the list to read left to right instead of top to bottom?

 

[Qik3Coder]

Sample data required.

What is the real need for left to right instead of top to bottem?
This is normally painful.

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[UnsolvedCoding]

The form is pre-made and houses a list of claims. Originally the form simply housed the claim number, a date and a new comment. The new comment was added to SQL one at a time by manually typing it into a specific cell.

The changes now show information pertaining to the specific client, comments to be added and the list is used as a whole instead of the user needing to type each claim number in a different location. The request is to add previous comments to excel instead of showing it in notepad. This code was used to populate notepad


Set wShell = CreateObject("WScript.Shell")
Set ofs = CreateObject("scripting.filesystemobject")
Set tempFile = ofs.opentextfile("c:\claim.summary.txt", 2, True) '2 forwriting

If Not rsERDB.EOF Then
'write header lines:
tempFile.writeline "==============================================================================================================" '110 =
tempFile.writeline "--- Chargeback Number: " & rsERDB("CBNumber")
tempFile.writeline "--- Chargeback Amount: " & FormatCurrency(rsERDB("AMOUNT"))
tempFile.writeline "--- Vendor Number/Name: " & rsERDB("VendorNumber") & " - " & rsERDB("VendorName")
tempFile.writeline "==============================================================================================================" '110 =




Do some SQL stuff in here




'write detail notes
While Not rsERDB.EOF
tempFile.writeline " " & rsERDB("NoteDate") & "(" & ProperCase(rsERDB("USR_NME")) & "): " & vbTab & rsERDB("Note_Text")
rsERDB.MoveNext
Wend

If sNonPClaim > "" Then
tempFile.writeline
tempFile.writeline
tempFile.writeline sNonPClaim
End If
Else
tempFile.writeline "No Pending Note Found"
End If
Else
tempFile.writeline "Invalid CB Number. If this is a pending claim, add 'P' at the end."
End If

However to make this fly, I need to take the note pad info and move it to Excel so that it runs on the same row as the claim number.

The SQL code will pull the info needed but getting it into Excel is proving more difficult.

 

[Qik3Coder]

You're still playing with excel, which may make this easier or harder.

Sample data. as in what do you have in the database, and what do you need shown.

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[Qik3Coder]

UnsolvedCoding (TechnicalUser) 21 Jul 11 12:36
What comes from SQL
Date Note
7/21/2011 Testing Notes
6/6/2011 This is a test

What is needed in Excel
7/21/2011 Testing Notes 6/6/2011 This is a test

Because you are using Excel it will be easy enough for you to massage this data in the vb code that you are writing in a similar fashion to how you did the one yesterday.

Your while loop that you had in yesteday's example will only ever have 1 row returned, where as the one for today will have multiple. You just need to offset, add the data, and repeat.
You will need an additional counter that IS reset prior to each row running.

If you are running SQL 2005 or newer you can do a slick trick with the Row_number function to generate a comment ID, which you can use as a multiplier to get the correct column number to put it in (I'll explain once you have some code).


Write a basic SQL statement to get the comments, write your vb script to drop it into excel, either in separate rows like yesterday, or take a swag and dropping them into rows.


BTW, you're starting to get a rather complex bit of logic going here, and may want to look at actually writing a program, with .net to handle this.

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[UnsolvedCoding]

It is rather complex. There are six major steps in this process. Yesterday was step 2, today is step 4. Using .Net would probably be a better solution but that would force changes to a lot of other applications.

The other problem is I am trying to do this with the permissions the user would have at the request of my boss. The theory is that if I can run it with limited permissions, the user would run it without problems.