convert datareport to .csv file

[WBH1138]

Hi

I've been asked to convert one of our datareports to a .csv file so users can open it in Excel.

Is there a better way than just reading through the data environment and building the .csv from that?
That would mean having to add the formulae/functions for horizontal and vertical totalling that the datareport does already.

I was hoping to be able to do something generic that would work for all datareports.

thanks

 

[ChewDoggie]

I don't know about your formulae/functions but you can try to use the recordset (rsDataReport) that's bound to your datareport with this method. However, if there is a large amound of data, this method can be slow:

        DBData = Trim(rsDataReport.GetString(adClipString, , ",", vbCrLf, vbNullString))
        Open FilePath For Output As #1
        Print #1, Headers ' column names .. optional
        Print #1, DBData
        DoEvents
        Close #1

HTH

Chew

10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[WBH1138]

Using the recordset bound to the report is OK but I have SUM functions that are added to the report designer and so not in the recordset.
To get those I will have to add fields in the .csv file which will be Excel functions.

When I used Crystal you had access to each line of data as the report was created but that doesn't appear to be the case in DataReports.

 

[SkipVought]

What a mess, adding functions to a .csv text file!!!

Might be simpler to save the hard data only, open in Excel and do the forumals there. Then convert all the formulas to text, before saveing as a .csv.

But WHY formulas to begin with. NEVER got a csv with a formula before.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WBH1138]

I'm having to add Excel functions to total up at the end i.e. =SUM(G4:G205), because the totalling is done by the datareport and I can't retrieve it to use to create the relevant fields in the .csv.

Using the Excel object in VB6 might help but they don't like using too many references here.

 

[JoeAtWork]

I doubt that you could stick a formula into a CSV file (it's just a plain text file) and expect Excel to convert it.

Why don't you just open another recordset that retrieves the SUM value and stick it in at the end?

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[HughLerwill]

Or consider creating the text file without frills then import it into an Excel Template which has the formats and formulae required.

 

[WBH1138]

I've added the Excel formula into the text file (saved as a ".csv") and it gets converted when Excel opens it.

The code I used follows, in case it helps anyone....

I wanted to make this routine generic but I can't guarantee, as in this case, that the recordset fields are in the right order.

Public Sub CreateAgedDebtCSV(ByVal rsReportRecordset As ADODB.Recordset)

Dim objFSO As FileSystemObject
Dim objTextstream As TextStream
Dim intFinalRow As Integer
Dim strName As String
Dim strAddress As String


'Instantiate file and textstream objects to create/overwrite .csv file
Set objFSO = New FileSystemObject
Set objTextstream = objFSO.OpenTextFile(gsDatabaselocation & AGED_DEBT_CSV_FILE, ForWriting, True)

'Set initial line value for SUMming function
intFinalRow = 3

'Set up title and headings for .csv file
objTextstream.WriteLine ",,,,Financial Aged Debt Report,,,,,,"
objTextstream.WriteBlankLines 1
objTextstream.WriteLine "Client Name,Client ID,Address,Phone,Last Payment,Current,30+ Days," & _
"60+ Days,90+ Days,120+ Days,Total"

'Loop through report recordset
With rsReportRecordset

.MoveFirst

Do Until .EOF

'Ensure names and addresses do not have commas within them, as this will throw the layout of the .csv
strName = Replace(.Fields(1).Value, ",", " ", 1)
strAddress = Replace(.Fields(11).Value, ",", " ", 1)

'Write line to .csv file for each record in recordset
objTextstream.WriteLine strName & "," & .Fields(0).Value & "," & strAddress & "," & _
.Fields(12).Value & "," & .Fields(10).Value & "," & .Fields(4).Value & "," & _
.Fields(5).Value & "," & .Fields(6).Value & "," & .Fields(7).Value & "," & _
.Fields(8).Value & "," & .Fields(9).Value

'Increment row count for SUMming function
intFinalRow = intFinalRow + 1

.MoveNext
Loop
End With

'Create totals line
objTextstream.WriteBlankLines 1
objTextstream.WriteLine ",,,,Totals :,=SUM(F4:F" & CStr(intFinalRow) & "),=SUM(G4:G" & CStr(intFinalRow) & ")," & _
"=SUM(H4:H" & CStr(intFinalRow) & "),=SUM(I4:I" & CStr(intFinalRow) & ")," & _
"=SUM(J4:J" & CStr(intFinalRow) & "),=SUM(K4:K" & CStr(intFinalRow) & ")"


objTextstream.Close
Set objTextstream = Nothing
Set objFSO = Nothing

End Sub

 

[SkipVought]

Dim intFinalRow As Integer

You may run into problems if your tables exceed 32767 rows. I recommend always declaring row indexes as LONG, version less than 2007.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WBH1138]

A good point.

I did consider it but it's unlikely that any of our customers have that many clients. And as it's for an Aged Debt report, that many clients owing them money and I doubt they'd still be in business. )