Solved

Export to CSV File

Posted on 2004-04-04
3
356 Views
Last Modified: 2013-12-25
I am using VB6 with SQL Server as backend. I need to export data to CSV file. I got a code from the internet to export the data using double quotes as delimiter. I have a problem in this. Some data which is varchar have double quotes and it gives problem for example.
CustID  CustName          Amount    Tax     BillAmt
1          L "M" N Private    125.00    2.00   127.00
When I import this data to CSV in that file I see it like this.
CustID  CustName          Amount    Tax     BillAmt
1         L M" N Private"    125.00     2.00   127.00
So when I import the data this record is imported wrongly. How to solve this problem. Some records have comma(,) also in the data I cant use that also for delimiters.
0
Comment
Question by:siva2k
3 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 75 total points
ID: 10755347
Just use another delimeter, like ";" or "~"


I use this function, to place a recordset with title in a spreadsheet

private sub transferdata (TransferRs as recordset)
on error goto transferdataError
        Dim ExcelApp As Object
        Dim ExcelWorkBook As Object
        Set ExcelApp = CreateObject("excel.application")
        ExcelApp.Visible = False
        Set ExcelWorkBook = ExcelApp.Workbooks.Add
        With TransferRS ' recordset
            ' Printing the titels into excell !
            If strFields <> "*" Then
                For Counter = 0 To UBound(arrFields)
                    ExcelWorkBook.sheets(1).cells(TransferRS.AbsolutePosition + 1, Counter + 1) = Trim(arrFields(Counter))
                Next
            Else
                For Counter = 0 To rs.Fields.count
                    ExcelWorkBook.sheets(1).cells(TransferRS.AbsolutePosition + 1, Counter + 1) = Trim(.Fields(Counter).Name)
                Next
            End If
           
            ' Printing the fields into excell !
            Do Until .EOF
                If strFields <> "*" Then
                    For Counter = 0 To UBound(arrFields)
                        If Trim(arrFields(Counter)) = "taal" Then
                            ExcelWorkBook.sheets(1).cells(TransferRS.AbsolutePosition + 2, Counter + 1) = IIf(Trim(.Fields(arrFields(Counter))) = 1, "FR", "NL")
                        Else
                            ExcelWorkBook.sheets(1).cells(TransferRS.AbsolutePosition + 2, Counter + 1) = Trim(.Fields(arrFields(Counter)))
                        End If
                    Next
                Else
                    For Counter = 0 To rs.Fields.count
                        ExcelWorkBook.sheets(1).cells(TransferRS.AbsolutePosition + 1, Counter + 1) = Trim(.Fields(arrFields(Counter)))
                    Next
                End If
                .MoveNext
            Loop
            .Close
        End With
        ' Saving excell, closing excell
        ExcelWorkBook.saveas IIf(strDir <> "", strDir, App.Path) & IIf(strFileName <> "", strFileName, "\output" & Format(Now(), "yyyymmddhhnnss")) & ".xls"
        ExcelWorkBook.Close
        ExcelApp.quit
        Set ExcelApp = Nothing
       exit sub
transferdataError:
       msgbox err.number & " " & err.description
end sub
0
 
LVL 4

Expert Comment

by:Mitzs
ID: 10756342
hi siva2k

here is the code to create a .csv file, i havent cleaned the code as i just picked it up from my routine, so you might get some error of declaring variable.

Just look at the structure and way to create a csv file.

Dim rsP As ADODB.Recordset
Dim sLine As String

Set rsP = New ADODB.Recordset

Dim FileNum As Long
Dim i As Integer

FileNum = FreeFile

Open "c:\Feed.csv" For Output As FileNum
   

sLine = ""
strSQL = " Select * from Product Where intactive='Y'"
rsP.Open strSQL, tConn, adOpenKeyset, adLockReadOnly
Dim sCat As String
Dim sManu As String
Do While Not rsP.EOF
'loop thru your recordset and just create and the line of records with delimiter ",(Comma)" and end it with vbcrlf

DoEvents
    sLine = sLine & Trim(rsP.Fields("Stock Code")) & ","
    sLine = sLine & Replace(Trim(rsP.Fields("chrProductName")), ",", "") & ","
    sLine = sLine & Trim(rsP.Fields("Part No")) & ","
    sLine = sLine & Trim(sCat) & ","
    sLine = sLine & Trim(sManu) & ","
    sLine = sLine & IIf(cVal(Trim(rsP.Fields("Free stock"))) > 0, "Y", "N") & ","
    sLine = sLine & Trim(rsP.Fields("Price")) & ","
    sLine = sLine & vbCrLf
   
'    Print #FileNum, sLine
   
    i = i + 1
    lblLines.Caption = "Lines Tranferred :-" & i
DoEvents
rsP.MoveNext

Loop
'output to a file the titles and the records string(sline)
If rsP.EOF Then
    Print #FileNum, "Stock Code,Description,MPN,Category,Manufacturer,Stock,Price,Product URL" & vbCrLf & sLine
End If


This file will opne in excel and the delimiter is comma ","

Close FileNum
MsgBox "done"


hope this helps

Mitzs
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11315634
No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:

Split Dhaest, Mitzs

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

leonstryker
EE Cleanup Volunteer
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now