Solved

Export to CSV File

Posted on 2004-04-04
3
359 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…

919 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

12 Experts available now in Live!

Get 1:1 Help Now