Export to CSV File

Posted on 2004-04-04
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.
Question by:siva2k
LVL 53

Accepted Solution

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))
                For Counter = 0 To rs.Fields.count
                    ExcelWorkBook.sheets(1).cells(TransferRS.AbsolutePosition + 1, Counter + 1) = Trim(.Fields(Counter).Name)
            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")
                            ExcelWorkBook.sheets(1).cells(TransferRS.AbsolutePosition + 2, Counter + 1) = Trim(.Fields(arrFields(Counter)))
                        End If
                    For Counter = 0 To rs.Fields.count
                        ExcelWorkBook.sheets(1).cells(TransferRS.AbsolutePosition + 1, Counter + 1) = Trim(.Fields(arrFields(Counter)))
                End If
        End With
        ' Saving excell, closing excell
        ExcelWorkBook.saveas IIf(strDir <> "", strDir, App.Path) & IIf(strFileName <> "", strFileName, "\output" & Format(Now(), "yyyymmddhhnnss")) & ".xls"
        Set ExcelApp = Nothing
       exit sub
       msgbox err.number & " " & err.description
end sub

Expert Comment

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

    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

'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

LVL 29

Expert Comment

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.

EE Cleanup Volunteer

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

685 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