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
Solved

Export to CSV File

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
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…

840 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