Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

Export to CSV File

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
siva2k
Asked:
siva2k
1 Solution
 
DhaestCommented:
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
 
MitzsCommented:
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
 
leonstrykerCommented:
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

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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now