Solved

How to export to CSV file from Crystal Report 2008

Posted on 2009-04-08
4
903 Views
Last Modified: 2013-11-15
I have a CR 2008 report which selects a record for 'parents contact details' from a SQL view. Details section Da will display father details and details section Db will display mother details from the one record in the view. Detail section a or b are conditionally suppressed if there are no details for either father or mother so no blank records are exported to the CSV file.
Two problems here, when I export to CSV file the Db section will be on the same line as the Da section instead of on the next line. Also for each section that has been suppressed I still get the comma seperators eg:  ,,,,   for each field on that suppressed record. I rushed out to buy CR 2008 because I was told this would do the export to CSV much better than CR10, this is becoming critical and I still have problems .
thanks for any assistance you can give.....
0
Comment
Question by:judeb1397
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24094779
Hi judeb1397,

CVS is a good format for different puposes but most of the time user need to do some formatting for it.
I have designed couple of software products where we need to customize same report in direct excel. You can find a very good VB 6 or .Net code to generate direct excel report from your software.
The code is available in a Tutorial at http://www.codeguru.com/vb/gen/vb_misc/tips/article.php/c8227

Hope it will help you out for this kind of problem.
Regards

Public Sub GenerateReport(prmTemp As ADODB.Recordset, _
                          prmPage As String, prmCol  As String, _
                          prmRow  As String, prmData As String, _
                          prmFile As String)
 
On Error GoTo Err_GenerateReport
   'VARIABLE DECLARATION
   Dim xlApp    As Excel.Application
   Dim xlBook   As Excel.Workbook
   Dim xlSheet  As Excel.Worksheet
   Dim xlSheet1 As Excel.Worksheet
   Dim rstemp   As ADODB.Recordset
   Dim intX     As Integer
   Dim intY     As Integer
   Set xlApp    = New Excel.Application
   Set xlBook   = xlApp.Workbooks.Add
   Set xlSheet  = xlBook.Worksheets.Add
   xlSheet.Name = "Pivot"
 
   Set rstemp = prmTemp
 
   'DUMP THE RECORDSET TO EXCEL
   For intY = 0 To rstemp.Fields.Count - 1
      xlSheet.Cells(intX + 1, intY + 1).Value = _
         rstemp.Fields(intY).Name
   Next intY
 
 
   intX = intX + 1
   While Not rstemp.EOF
      For intY = 0 To rstemp.Fields.Count - 1
         xlSheet.Cells(intX + 1, intY + 1).Value = _
            rstemp.Fields(intY).Value
      Next intY
      rstemp.MoveNext
      intX = intX + 1
   Wend
 
   'DATA DUMPED, SO WE HAVE THE DATA ON WHICH TO PIVOT
 
   'ADDING A NEW WORKSHEET FOR THE PIVOT TABLE
   Set xlSheet1  = xlBook.Worksheets.Add
   xlSheet1.Name = "Report"
 
   'CREATING THE PIVOT TABLE
   xlBook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
      "Pivot!R1C1:R" & rstemp.RecordCount + 1 _
      & "C" & rstemp.Fields.Count).CreatePivotTable _
      TableDestination:=xlSheet1.Range("A9"), _
      TableName:="PivotTable1"
 
   xlSheet1.PivotTables("PivotTable1").SmallGrid = False
 
   'SETTING THE PAGE LEVEL FIELDS FROM THE PARAMETERS PASSED
   If Len(prmPage) > 0 Then
      For intX = 1 To Len(prmPage)
         With xlSheet1.PivotTables("PivotTable1").PivotFields( _
            rstemp.Fields(CInt(Mid$(prmPage, intX, 1))).Name)
            .Orientation = xlPageField
            .Position = intX
         End With
      Next intX
   End If
 
   'SETTING THE COL LEVEL FIELDS FROM THE PARAMETERS PASSED
   If Len(prmCol) > 0 Then
      For intX = 1 To Len(prmCol)
         With xlSheet1.PivotTables("PivotTable1").PivotFields( _
            rstemp.Fields(CInt(Mid$(prmCol, intX, 1))).Name)
            .Orientation = xlColumnField
            .Position = intX
         End With
      Next intX
   End If
 
   'SETTING THE ROW LEVEL FIELDS FROM THE PARAMETERS PASSED
   If Len(prmRow) > 0 Then
      For intX = 1 To Len(prmRow)
         With xlSheet1.PivotTables("PivotTable1").PivotFields( _
            rstemp.Fields(CInt(Mid$(prmRow, intX, 1))).Name)
            .Orientation = xlRowField
            .Position = intX
         End With
      Next intX
   End If
 
   'SETTING THE DATA FIELDS FROM THE PARAMETERS PASSED
   If Len(prmData) > 0 Then
      For intX = 1 To Len(prmData)
         With xlSheet1.PivotTables("PivotTable1").PivotFields( _
            rstemp.Fields(CInt(Mid$(prmData, intX, 1))).Name)
            .Orientation = xlDataField
            .Position = 1
         End With
      Next intX
   End If
   'HIDING THE PIVOTTABLE COMMANDBAR
   xlApp.CommandBars("PivotTable").Visible = False
 
   xlSheet1.Cells.EntireColumn.AutoFit
   xlSheet1.Range("A1").Select
   xlApp.DisplayAlerts = False
   'DELETING THE SHEET WITH THE SOURCE DATA - SO THAT NO ONE
   'CAN MODIFY
   xlSheet.Delete
   xlApp.DisplayAlerts = True
   xlSheet1.Range("A1").Select
 
   'SAVING THE EXCEL SHEET
   xlBook.SaveAs prmFile
   xlApp.Visible = True
 
 
Exit_GenerateReport:
   'xlBook.Close
   Set xlApp    = Nothing
   Set xlBook   = Nothing
   Set xlSheet  = Nothing
   Set xlSheet1 = Nothing
   Set rstemp   = Nothing
   Exit Sub
 
Err_GenerateReport:
   xlBook.Close
   Set xlApp    = Nothing
   Set xlBook   = Nothing
   Set xlSheet  = Nothing
   Set xlSheet1 = Nothing
   Set rstemp   = Nothing
   Err.Raise vbObjectError + 1500, "modReport.GenerateReport", _
      Err.Description
 
End Sub

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 24094872
I don't see anything wrong with what CR is doing- indeed it seems to me that it is behaving exactly as it should be.
In creating the output file it is preserving the true record structure and ensuring that there is a placeholder for every field in the record.  For most situations these would be pretty fundamental requirements.

 I guess that means you will have to do some additional work to get the file you require. One option would be to export the data to Excel - which will preserve the layout- and then create your text file from Excel.

0
 

Author Comment

by:judeb1397
ID: 24102890
Thanks mfhorizon/peter57r, I was going to use VisualCUT to automate this export to CSV as I need it to be automated and I need it to be in CSV format with not blank records for an automated upload. You are correct when it is exported to Excel first then the data is fine. I am getting a little out of my depth with the VB 6 & .NET code option but I will explore this further..
0
 

Accepted Solution

by:
judeb1397 earned 0 total points
ID: 24210939
I am sorry for the delay getting back to this, I have been on leave. I have managed to find my own solution using Visual CUT to export to seperate files, one for the father details and one for the mother details and then using the Visual CUT command line argument (TXT_Merge) to append the export from the 2nd report to the export from the first report. This may be a little clunky but more within my capabilities. Thank you very much for your suggestions
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question