Page 1 of 2 12 LastLast
Results 1 to 40 of 47

Thread: [RESOLVED] How to save a xls file as csv via ADO ?

  1. #1

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Resolved [RESOLVED] How to save a xls file as csv via ADO ?

    I need to open an excel file, to read a certain sheet and to save it as csv on a server where MS Office is not installed. I know how to set the ADO properties so that to get data either as array (GetRows method) or as string using GetString method but when I try to save data on disk I have to wait over 10 seconds to see the file processed (4770 rows/43 columns). Besides this, for some reason some cells with a very large description text (2000-3000 characters) failed to be read/wrote properly in the csv file appearing as merged with the adjacent cells. In the same time, considering a small function written in python (via pandas library) and called with Shell command took approx.600-700 ms. I am a bit frustrated that I cannot reproduce in VB6 a procedure like this while in other languages it seems to be trivial.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to save a xls file as csv via ADO ?

    did you try like

    Code:
    open "c:\temp\myfile.csv" for output as 1
    print #1, Replace(rs.GetString, vbTab, ",")
    close 1
    of course if it possible some fields may contain commas then those fields (or all fields) would need to be enclosed in ""
    if required, enclosing all fields would probably be simpler, but it might be worth speed testing to see if there is much difference, using another replace, against looping all the fields to find commas
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by westconn1 View Post
    did you try like

    Code:
    open "c:\temp\myfile.csv" for output as 1
    print #1, Replace(rs.GetString, vbTab, ",")
    close 1
    of course if it possible some fields may contain commas then those fields (or all fields) would need to be enclosed in ""
    if required, enclosing all fields would probably be simpler, but it might be worth speed testing to see if there is much difference, using another replace, against looping all the fields to find commas
    Thank you for reply westconn. Yes, I tried this approach before and it is a bit faster but the cells in the final csv looks very mixed. I have also tried an older function posted on site years ago http://www.vbforums.com/showthread.p...-to-CSV-format but some cells with special characters break the general rule. I tried even something in RC5 (New_c.FSO.WriteTextContent) but I think GetString method it is not useful in this context.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,044

    Re: How to save a xls file as csv via ADO ?

    giive this a try, I didn't test it so use a Backup Excel File

    Code:
    Private Sub Command1_Click()
       Dim xclApp As Object
       Dim xclWbk As Object
       Dim xclSht As Object
       Dim Excelfile As String
       
       Excelfile = "E:\ExcelExport2.xls" 'your ExcelFile
              
        Set xclApp = CreateObject("Excel.Application")
        Set xclWbk = xclApp.Workbooks.Open(Excelfile)
        Set xclSht = xclWbk.Worksheets(1) 'Sheet 1
    
    
        
        xclSht.SaveAs "E:\TestFolder\TestExport.csv" 'save as csv
        xclApp.ActiveWindow.Close SaveChanges:=False
        
        
          Set xclSht = Nothing
          Set xclWbk = Nothing
          xclApp.Quit
          Set xclApp = Nothing
    
    End Sub
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    Thanks, Chris. Yes, this is the classic method (late binding) but I cannot use it as long as that machine doesn't know anything about Office library.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,044

    Re: How to save a xls file as csv via ADO ?

    do you have a Access Database on the Server ?
    if so create a Excel Linked Table, Print the Linked Table from there
    not sure about the 2000-3000 characters, Access might create a Memo field from that
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to save a xls file as csv via ADO ?

    i do not believe that ado supports memo fields for excel tables, any mixed columns can be a problem anyway

    if it is a compressed xml type excel file (excel 2007 or later) there are some examples posted by wqweto, with links to github, to read those files without either excel or ADO, i have no idea about the speed of those methods
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    No, that server has nothing installed from the Office package but the SQL server only. I do not understand very well which is the gain creating a linked table in database when my purpose is to have that file on disk but in csv format. I have to recognize that we have some limitations in VB6 as long as these kind of things are a bit problematic.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  9. #9
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to save a xls file as csv via ADO ?

    ADO doesn't support text or Excel data, or any other kind. It relies on Providers to understand, read, and write in various data formats.

    However Jet does support both, and you can use either ADO or DAO as your data connector library.

    Code:
    Option Explicit
    
    Private Sub Main()
        ChDrive App.Path
        ChDir App.Path
        With New ADODB.Connection
            .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='SampleBook.xls';" _
                & "Extended Properties='Excel 8.0;HDR=Yes'"
            .Execute "SELECT * INTO [Text;Database=.].[Sheet1A.csv] FROM [Sheet1$]", _
                     , _
                     adCmdText Or adExecuteNoRecords
            .Execute "SELECT CInt([This]) AS [This],[That],[The Other Thing]" _
                   & "INTO [Text;Database=.].[Sheet1B.csv] FROM [Sheet1$]", _
                     , _
                     adCmdText Or adExecuteNoRecords
            .Close
        End With
        MsgBox "Done"
    End Sub
    The "dice rolling" that the Excel IISAMs do to determine column data types typically picks "Float" (Single) by default for a numeric column. That's where the second export above comes in: converting from Float to Short (Integer).

    This is obviously a trivial test case.

  10. #10
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to save a xls file as csv via ADO ?

    Here's another export:

    Code:
            .Execute "SELECT CInt([This]) AS [This],[That],[The Other Thing]" _
                   & "INTO [Text;Database=.;CharacterSet=65001]." _
                   & "[Sheet1C.csv] FROM [Sheet1$]", _
                     , _
                     adCmdText Or adExecuteNoRecords
    That one exports UTF-8 (with no BOM) instead of ANSI.

  11. #11

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    Thank you for reply, Dile. For some reason first Execute command raises an error. I suspect that the destination path mentioned after INTO command is not declared properly or at least I do not understand it :
    Code:
    [Text;Database=.].[Sheet1A.csv]
    This argument should be the destination file (path of the csv file that has to be created). Below are the first lines of your routine adapted to my context:
    Code:
     
            Dim prop As String, csvFile As String
            
            prop = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & xlsInput & "';Extended Properties='Excel 12.0 Xml;HDR=Yes'"
            csvFile = App.Path & "\mdTTsITSM.csv"
            
            Set con = New ADODB.Connection
        
        With con
            .Open prop
            .Execute "SELECT * INTO [Text;Database=.].[" & csvFile & "] FROM [TT$]", adCmdText Or adExecuteNoRecords
            .Close
        End With
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  12. #12
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: How to save a xls file as csv via ADO ?

    I thought that Microsoft.ACE.OLEDB.12.0 provider was installed by Microsoft Access Database Engine which is an MS Office redist but you already said there is nothing MS Office on this server so go figure. . .

    Also never knew that [Database=.].[c:\path\to\my.csv] is valid Text driver data source, instead of the documented [Database=c:\path\to].[my.csv] so that schema.ini is accessed under c:\path\to etc. details.

    cheers,
    </wqw>

  13. #13
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by Daniel Duta View Post
    For some reason first Execute command raises an error.
    What error? Something besides the known issue where the IDE messes up stack cleanup on some ADO calls involving Jet IISAMs resulting in error 80004005 "Selected collating sequence not supported by the operating system"?

    If you get that error just run again. It doesn't occur in a compiled program.


    The "." means "current directory." If you wanted some other path then provide that as the "Database" value. I don't think trying to jam a fully qualified path into the table name has ever worked for me and the documentation doesn't suggest that as a valid syntax.


    If you are using the ACE 12 Provider I assume there is some reason. If so, you'll want to request the "Excel 12.0" or the "Excel 12.0 Xml" IISAM type (for .xslx files).

  14. #14

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by wqweto View Post
    I thought that Microsoft.ACE.OLEDB.12.0 provider was installed by Microsoft Access Database Engine which is an MS Office redist but you already said there is nothing MS Office on this server so go figure. . .

    Also never knew that [Database=.].[c:\path\to\my.csv] is valid Text driver data source, instead of the documented [Database=c:\path\to].[my.csv] so that schema.ini is accessed under c:\path\to etc. details.

    cheers,
    </wqw>
    Yes, you are right wqweto, however Microsoft Access Database Engine 2010 (ODBC and OLEDB drivers) was accepted to be installed around SQL Server 2012 so that to have a connectivity with Office files (mainly xls files) that are coming attached on email. The Office package is not allowed to be installed. Anyway, after I corrected that line accordingly I still get an error but this time a new one:Name:  Capture.PNG
Views: 1608
Size:  2.5 KB
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  15. #15
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: How to save a xls file as csv via ADO ?

    There is this Schema.ini File (Text File Driver) topic in MSDN. You can try Memo data-type like this

    Code:
    [my.csv]
    Format=Delimited(,)
    Col1=CustomerNumber Memo
    Col2=CustomerName Memo
    cheers,
    </wqw>

  16. #16

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by dilettante View Post
    What error? Something besides the known issue where the IDE messes up stack cleanup on some ADO calls involving Jet IISAMs resulting in error 80004005 "Selected collating sequence not supported by the operating system"?

    If you get that error just run again. It doesn't occur in a compiled program.


    The "." means "current directory." If you wanted some other path then provide that as the "Database" value. I don't think trying to jam a fully qualified path into the table name has ever worked for me and the documentation doesn't suggest that as a valid syntax.


    If you are using the ACE 12 Provider I assume there is some reason. If so, you'll want to request the "Excel 12.0" or the "Excel 12.0 Xml" IISAM type (for .xslx files).
    Yes, Dile, I filled the db path with full path of my csv file according to wqweto suggestion and most likely this is the proper way as long as I get a different error . Indeed, the excel file format is xlsx. Thank you.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  17. #17

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    After many attempts I've noticed that there are 3 sources of errors that affect my conversion to a csv file: some cells contain comma, other cells contain many carriage return and other cells contain unicode characters that are displayed approximately as " ????n???????? ". At the moment I do not know how to deal with all these limitations because even the ADO method .GetRows cannot treat unicode chars properly. I have as alternative a small function written in python but I am curious if somebody from this forum has managed to find a robust solution to all these issues. Below is my code:
    Code:
    Dim s As String, i As Long, j As Long, arr() As Variant
        Dim prop As String, csvFile As String, trLine As String
        Dim fso As New Scripting.FileSystemObject
        Dim fsoStream As Scripting.TextStream
        Dim rec As ADODB.Recordset
        
        prop = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & xlsInput & "';Extended Properties='Excel 12.0 Xml;HDR=Yes'"
        csvFile = App.Path & "\test.csv"
    
        Set rec = New ADODB.Recordset
        Set fsoStream = fso.CreateTextFile(csvFile, True)
            
        With rec
              .CursorLocation = adUseClient
              .Open "SELECT * FROM [TT$]", prop, adOpenStatic, adLockReadOnly
        arr = .GetRows
        End With
    
        For i = 0 To UBound(arr, 2)
        For j = 0 To UBound(arr, 1)
            If IsNull(arr(j, i)) Then
               s = vbNullString
            Else
               s = Replace(arr(j, i), ",", ";")
            End If
               strLine = strLine & s & ","
        Next j
            strLine = Left(strLine, Len(strLine) - 1)
            fsoStream.WriteLine strLine
            strLine = vbNullString
        Next i
    
            fsoStream.Close
            Set fsoStream = Nothing
            Set fso = Nothing
            Set rec = Nothing
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  18. #18
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,044

    Re: How to save a xls file as csv via ADO ?

    Hi,

    try this way,

    put this in a modul
    Code:
    Option Explicit
    
    Public Cn As ADODB.Connection
    Public DBName As String
    
    Public Function ConnectionOpen(Cn As ADODB.Connection) As Boolean
    'Connection zur DB
       Dim Msg As String
      
          'Object generieren
          If Cn Is Nothing Then
             Set Cn = New ADODB.Connection
          End If
      
          With Cn
             If Cn.State = adStateOpen Then
                ConnectionOpen = True
                Exit Function
             End If
             
             'Beschreibung Connection
             On Error GoTo Fehler
            
             
                .Provider = "Microsoft.Jet.OLEDB.4.0"
                .Properties("Data Source") = DBName
                .CursorLocation = adUseClient
                .Mode = adModeShareDenyNone
                .Properties("Extended Properties") = "Excel 8.0;"
                .Open
          
          'for xlsx:
    '            .Provider = "Microsoft.ACE.OLEDB.12.0"
    '            .Properties("Data Source") = DBName
    '            .CursorLocation = adUseClient
    '            .Mode = adModeShareDenyNone
    '            .Properties("Extended Properties") = "Excel 12.0;"
    '            .Open
    
          
          
          End With
          
          On Error GoTo 0
          ConnectionOpen = True
          On Error GoTo 0
          Exit Function
          
    Fehler:
          Msg = "Die Verbindung zur Datenbank" & vbCrLf & _
                "konnte nicht hergestellt werden  " & vbCrLf & vbCrLf & _
                "Fehler " & Err.Number & vbCrLf & vbCrLf & _
                Err.Description
          MsgBox Msg, vbCritical, "Connection-Error"
          Err.Clear
    End Function
    and this in a form
    Code:
    Option Explicit
    
    
    Private Sub Form_Load()
    DBName = "E:\ExcelExport2.xls"
    'DBName = "E:\Book1.xlsx"
    
          If ConnectionOpen(Cn) Then
            MsgBox "Open ok"
          Else
             Exit Sub
          End If
    End Sub
    
    
    
    Private Sub Command1_Click()
    
     Dim RsExcel As ADODB.Recordset
     Dim sSQL As String
      
      
     Dim xFn As Long
     Dim strFileName As String
     strFileName = "E:\TestFolder\Test1.csv"
          
          
          sSQL = "Select * From [TabImport$]" 'xls
    '        sSQL = "Select * From [Sheet4$]" 'xlsx
        
          
          Set RsExcel = New ADODB.Recordset
          With RsExcel
             .CursorLocation = adUseClient
             .CursorType = adOpenKeyset
             .LockType = adLockOptimistic
             .ActiveConnection = Cn
             .Open sSQL
    
    xFn = FreeFile
    Open strFileName For Output As xFn
    
    
    
            Do Until .EOF
             Dim s() As String
            ReDim s(8)
            s(0) = .Fields(0)
            s(1) = .Fields(1) & vbNullString
            s(2) = .Fields(2) & vbNullString
            s(3) = .Fields(3) & vbNullString
            s(4) = .Fields(4) & vbNullString
            s(5) = .Fields(5) & vbNullString
            s(6) = .Fields(6) & vbNullString
            s(7) = .Fields(7) & vbNullString
            s(8) = .Fields(8) & vbNullString
                Print #xFn, Join(s, ";") & ";"
          .MoveNext
       Loop
         
          End With
          Close xFn
          Set RsExcel = Nothing
          Cn.Close
          Set Cn = Nothing
          MsgBox "done !"
    End Sub
    I tried it with a few fields in the Excel sheet with characters > 2000 and it copied correctly to the csv

    hth
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  19. #19
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by Daniel Duta View Post
    After many attempts I've noticed that there are 3 sources of errors that affect my conversion to a csv file: some cells contain comma, other cells contain many carriage return and other cells contain unicode characters that are displayed approximately as " ????n???????? ".
    Commas are no problem at all. The TextDelimiter (") around values of the columns takes care of that.

    "Unicode" is no problem. Just specify CharacterSet=65001 for UTF-8 with no BOM. If you are trying to import these back into Excel, well it chokes and assumes ANSI if you just double-click a .CSV file so you will have to go through the Import wizard, specify UTF-8 and comma-delimited, etc.

    Inline CR or CRLF aren't an issue, they get wrapped in quotes as well.

    But the CSV probably won't import back into Excel properly. Excel isn't very consistent about that. When opening ANSI text files by double-click it seems to work fine, however the Import dialog seems to screw up on "multiline" values for a UTF-8 text import.


    Quote Originally Posted by Daniel Duta View Post
    At the moment I do not know how to deal with all these limitations because even the ADO method .GetRows cannot treat unicode chars properly.
    Sure it does.

    Your problem is that after you have the String YOU fail to deal with it properly! You are almost certainly trying to Debug.Print it (always ANSI), dump it into an ANSI control like TextBox, or write it to disk using VB native Text I/O which is always ANSI.

    You need to write it out as Unicode (UTF-16LE) which Excel doesn't seem to be able to import, or else encode it into UTF-8 and write that out.


    You may as well use that Python somebody else wrote wrapping a library that yet again somebody else wrote. Odds are though if your goal is re-importing into Excel that isn't going to help you either.

  20. #20
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by Daniel Duta View Post
    After many attempts I've noticed that there are 3 sources of errors that affect my conversion to a csv file:
    some cells contain comma, other cells contain many carriage return and other cells contain unicode characters that are displayed approximately as " ????n???????? ".
    Below is a Code-Snippet which might help...
    It makes up a Select Case with the vartype of the current cell,
    and wraps e.g. Cells with Text-Contents (Variants of type vbString) in DoubleQuotes,
    it also detects Date-Cells and formats them to ISO-DateStrings.

    The RC5-StringBuilder is used (in conjunction with an RC5-Stream), to do "chunk-writes", maintaining UTF8-format in the exported CSV.

    Code:
    Option Explicit
    
    Private Sub Form_Click()
      New_c.Timing True
         XLSX_to_CSV "c:\temp\testexport.xlsx", "c:\temp\testexport.csv"
      Caption = New_c.Timing
    End Sub
    
    Private Sub XLSX_to_CSV(SrcFile As String, DstFile As String)
      Dim i As Long, j As Long, Arr() As Variant, Cols() As String
      Dim SB As cStringBuilder, FS As cStream
      
      With CreateObject("ADODB.Connection")
        .CursorLocation = 3 'adUseClient
        .Open "Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & SrcFile
         Arr = .Execute("SELECT * FROM [TT$]").GetRows
        .Close
      End With
     
      ReDim Cols(0 To UBound(Arr, 1)) As String
      Set SB = New_c.StringBuilder
      Set FS = New_c.fso.CreateFileStream(DstFile, STRM_SHARE_DENY_NONE Or STRM_WRITE)
      
      For j = 0 To UBound(Arr, 2)
          For i = 0 To UBound(Arr, 1)
              Select Case VarType(Arr(i, j))
                Case vbNull, vbEmpty:  Cols(i) = vbNullString
                Case vbString:         Cols(i) = """" & Arr(i, j) & """"
                Case vbDate:           Cols(i) = Format$(Arr(i, j), "yyyy\-mm\-dd hh:nn:ss")
                Case Else:             Cols(i) = Str$(Arr(i, j))
              End Select
          Next i
          SB.AppendNL Join(Cols, ",")
          If SB.length > 65536 Then FS.WriteFromByteArr SB.ToUTF8: SB.Clear 'write-out and clear the StringBuilder
      Next j
      
      If SB.length Then FS.WriteFromByteArr SB.ToUTF8 'write the rest of the SB-Contents, if there are any
    End Sub
    The above is using the Excel-ODBC-Driver, since it is able to switch dynamically to the older *.xls-Files, is such a source was put in...
    If that does not work on your machine, then switch back to the Ace-12-Driver you currently use...

    As for performance - cannot do much, until the Array was retrieved via GetRows...
    After that it should basically fly though...

    Let us know about your new timings...

    HTH

    Olaf

  21. #21

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    I've tested approaches above and both of them, at a certain line, start to write data in a wrong format. While the ChrisE's routine mixes data even from the first line, the RC5 String Builder method starts from 80'th line (2151 characters on the 5th column...). There is no issue regarding ODBC-Driver or GetRows performance but something related to how some huge cells (containing both CRLF and unicode chars) are converted as csv file. I have attached 2 examples from the file converted but I think there are over 30 lines that were mixed in the same way. I do not know how python does succeed to make this conversion fast and accurate (without any ADO) but it seems for us, as vb6 users, it is an annoying challenge. I will try to attach a sample of this file but I have to remove some confidential data.Thank you all.
    Last edited by Daniel Duta; Jul 20th, 2019 at 12:15 PM. Reason: Sensitive data
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  22. #22
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to save a xls file as csv via ADO ?

    Well maybe you can tell us what "correctly" handling CRLF within a column value should look like?

    As far as I know, common CSV doesn't address the matter and it is just illegal.

    By default the ACE/Jet Text Installable ISAM and the old ODBC Desktop Driver for Text will both address it by outputting the CRLFs as they come. As long as there is a TextDelimiter defined, even by default (the quotation mark character ") , then those folded fields still get the delimiters:

    Code:
    "This","That","The Other Thing"
    12,"AA","ΛΘΔ Lorem ipsum ""dolor"" sit amet, consectetur adipiscing elit. Ut in urna gravida, hendrerit mi non, facilisis ante. Aenean ac enim tempus, lacinia purus quis, volutpat velit. Suspendisse quis commodo nibh. Nam et libero eu lectus sagittis rhoncus. Mauris augue felis, egestas et consequat ac, fermentum id massa. Nunc pretium consectetur faucibus. Nullam lacinia lacus non sem sagittis, id rhoncus est ultrices."
    11,"AB","Proin semper augue vitae ligula lacinia, a facilisis nulla blandit. Pellentesque varius, velit eget bibendum ullamcorper, leo erat feugiat nunc, sed tempus nunc turpis ac diam. Duis at luctus neque, sed hendrerit orci. Vivamus luctus convallis augue et congue. Quisque tincidunt risus ante, vitae convallis ipsum volutpat nec. Integer maximus porta rhoncus. Mauris maximus quis mauris quis eleifend. Quisque venenatis turpis a ipsum volutpat viverra. Curabitur nisl tortor, fringilla id enim at, scelerisque elementum metus. Curabitur sed tellus ac ipsum rhoncus rutrum. Nam eu nibh dolor. Nulla sed eros nunc. Nulla fermentum a tellus in dapibus. Morbi vehicula sed sem sit amet sagittis. Nulla bibendum tincidunt risus, a tincidunt velit varius a."
    134,"AC","Proin vitae ante lectus. Ut tempus, ipsum lacinia varius auctor, neque ligula pretium ipsum, ac pretium dui augue eget elit. Duis accumsan eleifend viverra. Suspendisse eget magna dolor. Sed aliquet sed ante nec maximus. Etiam ullamcorper urna non ultrices dignissim. Maecenas in mi eu leo ornare vulputate vitae ornare turpis. Integer eu velit eu sapien condimentum tincidunt vitae ut orci. Maecenas id ex vel odio pulvinar suscipit. Cras a magna non nunc faucibus viverra volutpat malesuada lorem. Curabitur enim dolor, aliquet et scelerisque non, porta vitae sem."
    14,"AD",
    17,"BA","Pellentesque viverra leo a quam mattis dignissim at dictum tortor. Donec a convallis eros. Nullam fringilla nulla sed condimentum mollis. Sed nisl urna, dignissim et aliquam fermentum, congue mollis sem. Mauris eget justo et ante rhoncus maximus. Sed fermentum risus est, in blandit felis condimentum efficitur. Ut at cursus est, nec ultrices quam."
    999,"Z","Φνοπρςτ, works?"
    77,"ZZ","Abc
    123
    xyZ"
    The red stuff here is such a value.


    On import Excel seems to be able to accept and process these properly as long as you can do a default import. That only works for ANSI text files though so your Unicode requirement blows that out of the water. This is an Excel problem.

  23. #23
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to save a xls file as csv via ADO ?

    This is also what is described in Common Format and MIME Type for Comma-Separated Values (CSV) Files, RFC 4180.

  24. #24

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by dilettante View Post
    This is also what is described in Common Format and MIME Type for Comma-Separated Values (CSV) Files, RFC 4180.
    Thank you Dile for details regarding CSV format in conjuction with MIME standard. Even I have to decode files often from this standard, in this case all tests above failed locally without having to deal with MIME. So I have a xlsx file in a folder of my PC and I try to save it as csv without to involve the office library. As I mentioned above, a few lines of python can do this stuff on the fly (the converted csv has an identical structure to the original xlsx file) while I am still searching for a solution.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  25. #25
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: How to save a xls file as csv via ADO ?

    .. As I mentioned above, a few lines of python can do this stuff on the fly ..
    A few lines of code doesn't mean there is no huge library behind it to get this working.
    In VB6 it can also be a few lines of code if someone provides a component or DLL

  26. #26
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by Daniel Duta View Post
    Thank you Dile for details regarding CSV format in conjuction with MIME standard. Even I have to decode files often from this standard, in this case all tests above failed locally without having to deal with MIME. So I have a xlsx file in a folder of my PC and I try to save it as csv without to involve the office library. As I mentioned above, a few lines of python can do this stuff on the fly (the converted csv has an identical structure to the original xlsx file) while I am still searching for a solution.
    This is wrong on so many levels I don't even want to start.

    First, there is no "standard" csv format. Most of the readers/writers do no implement the cited RFC (Excel included).

    Second, Excel's csv reader/writer does *not* support unicode/utf-8 so you cannot have "a few lines of python" that convert xlsx->csv with no loss when viewed in Excel.

    Try it: in Excel paste unicode text (emojis?) and save in csv format. You'll get "Some features in your workbook might be lost if you save it in CSV (comma delimited)" message from Excel. And you'll have a lot of question marks instead of emojis in the saved file.

    Quote Originally Posted by Arnoutdv View Post
    A few lines of code doesn't mean there is no huge library behind it to get this working.
    WAT? I thought .xlsx files handling was built in python's runtime so all arrays are actually Excel worksheets internally, no?

    cheers,
    </wqw>

  27. #27
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to save a xls file as csv via ADO ?

    Well if the goal is to get something that Excel can trivially import this seems to work:

    Code:
    Option Explicit
    
    Private Sub Main()
        ChDrive App.Path
        ChDir App.Path
        With New ADODB.Connection
            .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='SampleBook.xls';" _
                & "Extended Properties='Excel 8.0;Hdr=Yes'"
            'UTF-8 out instead of ANSI:
            .Execute "SELECT CInt([This]) AS [This],[That],[The Other Thing]" _
                   & "INTO [Text;Database=.;CharacterSet=65001]." _
                   & "[Sheet1NoBOM.csv] FROM [Sheet1$]", _
                     , _
                     adCmdText Or adExecuteNoRecords
            .Close
        End With
        'Now add a UTF-8 BOM so that Excel can use default CSV importing:
        Dim F As Integer
        Dim Buf() As Byte
        Dim BOM() As Byte
        F = FreeFile(0)
        Open "Sheet1NoBOM.csv" For Binary Access Read As #F
        ReDim Buf(LOF(F) - 1)
        Get #F, , Buf
        Close #F
        Kill "Sheet1NoBOM.csv"
        Kill "schema.ini"
        On Error Resume Next
        Kill "Sheet1BOM.csv"
        On Error GoTo 0
        F = FreeFile(0)
        Open "Sheet1BOM.csv" For Binary Access Write As #F
        BOM = StrConv("", vbFromUnicode)
        Put #F, , BOM
        Put #F, , Buf
        Close #F
        MsgBox "Done"
    End Sub
    After creating the CSV output in UTF-8 this code then reads it back and writes it out again with a UTF-8 BOM.

    The red line cheats a little and might produce bad results if compiled with different locale settings. But you can always stuff the byte values into the Byte array one at a time: &HEF, &HBB, &HBF.

    The BOM header tells Excel the text encoding, so you can use the default importing that you get by double-clicking the .CSV file once again.

    The SELECT is doing a CInt() because numeric columns are read as Single (Float) by the Excel IISAM and that would give us 4.00, 12.00, etc. in the CSV output.
    Last edited by dilettante; Jul 16th, 2019 at 09:35 AM.

  28. #28
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: How to save a xls file as csv via ADO ?

    @dilettante: The BOM fools the reader but does not work fool-proof here. It chokes past some emojis and marks following rows RTL script, after which text is mostly garbled.

    Good effort nevertheless and probably what OP wants. At least looks promising. . .

    cheers,
    </wqw>

  29. #29
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to save a xls file as csv via ADO ?

    I'm not sure how much more we could help without a better definition of the output wonderful, magical Python library creates.

    I'm not even sure importing the CSV into Excel is required. So much here was left undefined awaiting the gradual scope-creep as solutions were rejected one after another.

  30. #30
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,044

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by Daniel Duta View Post
    I've tested approaches above and both of them, at a certain line, start to write data in a wrong format. While the ChrisE's routine mixes data even from the first line, the RC5 String Builder method starts from 80'th line (2151 characters on the 5th column...). There is no issue regarding ODBC-Driver or GetRows performance but something related to how some huge cells (containing both CRLF and unicode chars) are converted as csv file. I have attached 2 examples from the file converted but I think there are over 30 lines that were mixed in the same way. I do not know how python does succeed to make this conversion fast and accurate (without any ADO) but it seems for us, as vb6 users, it is an annoying challenge. I will try to attach a sample of this file but I have to remove some confidential data.Thank you all.

    Attachment 169877
    a sample of the Excel file would be good, how and from where is the Data inserted in the Excel File ?
    perhaps you can use a Textfile rather than a Excel sheet
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  31. #31

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by Arnoutdv View Post
    A few lines of code doesn't mean there is no huge library behind it to get this working.
    In VB6 it can also be a few lines of code if someone provides a component or DLL
    Absolutely, I am aware about it and for the second part I do not have big expectations considering that our small community is divided.

    Quote Originally Posted by dilettante View Post
    I'm not sure how much more we could help without a better definition of the output wonderful, magical Python library creates.
    I'm not even sure importing the CSV into Excel is required. So much here was left undefined awaiting the gradual scope-creep as solutions were rejected one after another.
    No, Dile, I have just need to extract the excel info into a CSV file but in an accurate way. This file will be used further by other colleagues as input for an internal database.

    Quote Originally Posted by ChrisE View Post
    a sample of the Excel file would be good, how and from where is the Data inserted in the Excel File ?
    perhaps you can use a Textfile rather than a Excel sheet
    I tried to save it as text but is more useless than the file csv converted. The most structured conversion until now I've got it with Olaf's procedure but even it failed when cells exceeded a certain number of chars - the fifth field being declared as nvarchar(max). Because a sample is more useful to understand the structure of such a file (a reduced one to 500Kb) I attach it below.Thank you.
    Last edited by Daniel Duta; Jul 20th, 2019 at 12:19 PM.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  32. #32
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,044

    Re: How to save a xls file as csv via ADO ?

    Hi,

    I tried it with the Clipboard.

    to test put this in a form
    Code:
    Private Sub Command2_Click()
        Dim xFn As Long
        Dim strFileName As String
        strFileName = "E:\TestFolder\Duta.csv"
       
       Dim s() As String, s1() As String
       Dim i As Long, j As Long
     
          If Not Clipboard.GetFormat(vbCFText) Then
             Exit Sub
          End If
    
          s = Split(Clipboard.GetText, vbCrLf)
          
          xFn = FreeFile
          Open strFileName For Output As xFn
    
          For i = LBound(s) To UBound(s)
             s1 = Split(s(i), vbTab)
             For j = LBound(s1) To UBound(s1)
             Next
               Print #xFn, Join(s1, ";") & ";"
               'Note in Germany for csv semicolon = Join(s1,";")
          Next
    Close xFn
          
    End Sub
    for a quick Test
    1) open your Excelfile and mark everthing to be copied
    2) click the Button

    that's it, see if the results are ok
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  33. #33

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    Thank you, Chris. Have you test it before ? For what reason you have used the loop For j = LBound(s1) To UBound(s1) : Next j ?
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  34. #34
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,044

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by Daniel Duta View Post
    Thank you, Chris. Have you test it before ? For what reason you have used the loop For j = LBound(s1) To UBound(s1) : Next j ?
    the Loop is for Rows and Columns or in this case the cells

    here the result as a csv
    Duta.zip

    I didn't check all, but it look's ok to me
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  35. #35
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by Daniel Duta View Post
    No, Dile, I have just need to extract the excel info into a CSV file but in an accurate way.
    It feels like you have said this or something similar many times. However you haven't told us what "accurate way" means.

    We know you want to mix multi-locale alphabets, so ANSI isn't good enough. We know you need comma characters (sometimes "," and sometimes ";" for some locales) so you need the quotation mark text qualfier/delimiters. We know you have multiline values, some of them quite large. How large? Excel is limited to 32K and can only display 1K of that, though later versions of Excel might show more.

    We need to know exactly the format you want, and it would be helpful to know how the solutions you have been offered fail to meet your requirements.

  36. #36
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,044

    Re: How to save a xls file as csv via ADO ?

    to add what dilettante said..

    like I asked in Post#30....how and from where is the Data inserted in the Excel File ?

    I mean do you let the user enter whatever they want in the cells?, you do have control of this Excel Sheet ?
    if you have control make the rules, what, where and how the enter the Data
    Last edited by ChrisE; Jul 17th, 2019 at 12:14 PM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  37. #37
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to save a xls file as csv via ADO ?

    Yep, I'm out.

    I took the sample posted above and exported worksheet "TT" as UTF-8 with no BOM so Jet/ACE can read it back in.

    Then I read the worksheet and the CSV back in parallel, comparing just the text values from each. I didn't compare columns with Double, Date, or other types.

    The only places they differed was where the worksheet had cells where the value had trailing spaces. So if there is any problem it must be that the desired "CSV" format is something different from that exported by Jet and ACE Text IISAMs. What that format might be we still have not been told.

  38. #38
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to save a xls file as csv via ADO ?

    Quote Originally Posted by Daniel Duta View Post
    The most structured conversion until now I've got it with Olaf's procedure -
    but even it failed when cells exceeded a certain number of chars - the fifth field being declared as nvarchar(max).
    The only thing I've forgot in my initial Routine was in the Type-Case 'vbString' -
    since enclosing a given Text-Content (in DblQuotes) is not enough ...
    when double-quoting gets done, one also has to escape potentially already existing double-quotes with "double-double-quotes".

    Here again my (now updated) code, which converted your sample.xlsx in a way, that Excle was able to read it again...

    Code:
    Option Explicit
    
    Private Sub Form_Click()
      New_c.Timing True
         XLSX_to_CSV "c:\temp\sample1.xlsx", "c:\temp\sample1.csv"
      Caption = New_c.Timing
    End Sub
    
    Private Sub XLSX_to_CSV(SrcFile As String, DstFile As String, Optional ColDelimiter$ = ",")
      Dim i As Long, j As Long, Arr() As Variant, Cols() As String, SB As cStringBuilder
      
      With CreateObject("ADODB.Connection")
        .CursorLocation = 3 'adUseClient
        .Open "Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & SrcFile
         Arr = .Execute("SELECT * FROM [TT$]").GetRows
        .Close
      End With
     
      ReDim Cols(0 To UBound(Arr, 1)) As String
      Set SB = New_c.StringBuilder
     
      Const DblQt$ = """", DblDblQt$ = """"""
      For j = 0 To UBound(Arr, 2)
          For i = 0 To UBound(Arr, 1)
              Select Case VarType(Arr(i, j))
                Case vbNull, vbEmpty:  Cols(i) = vbNullString
                Case vbString:         Cols(i) = DblQt & Replace(Arr(i, j), DblQt, DblDblQt) & DblQt
                Case vbDate:           Cols(i) = Format$(Arr(i, j), "yyyy\-mm\-dd hh:nn:ss")
                Case Else:             Cols(i) = Str$(Arr(i, j))
              End Select
          Next i
          SB.AppendNL Join(Cols, ColDelimiter)
      Next j
      
      New_c.FSO.WriteTextContent DstFile, SB.ToString, True, True '<- True, True == UTF8 + BOM
    End Sub
    I've marked the new parts in the routine magenta (also took up Diles suggetion, to write this stuff out with an UTF8-BOM).

    Still interested in the timings (compared to the python-companion-lib).

    HTH

    Olaf

  39. #39
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: How to save a xls file as csv via ADO ?

    Here is Olaf's impl w/ no external dependancies

    thinBasic Code:
    1. Option Explicit
    2.  
    3. Private Declare Function WideCharToMultiByte Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long, ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar As Long) As Long
    4.  
    5. Private Sub Form_Click()
    6.     Dim dblTimer      As Double
    7.  
    8.     dblTimer = Timer
    9.     XLSX_to_CSV "d:\temp\sample1.xlsx", "d:\temp\sample1.csv"
    10.     Caption = Timer - dblTimer
    11. End Sub
    12.  
    13. Private Sub XLSX_to_CSV( _
    14.         SrcFile As String, _
    15.         DstFile As String, _
    16.         Optional ColDelimiter As String = ",", _
    17.         Optional LineDelimiter As String = vbCrLf)
    18.     Dim i               As Long
    19.     Dim j               As Long
    20.     Dim Arr()           As Variant
    21.     Dim Cols()          As String
    22.     Dim SB              As Collection
    23.     Dim SBConcat        As String
    24.     Dim nFile           As Integer
    25.  
    26.     With CreateObject("ADODB.Connection")
    27.         .CursorLocation = 3 'adUseClient
    28.         .Open "Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & SrcFile
    29.         Arr = .Execute("SELECT * FROM [TT$]").GetRows
    30.         .Close
    31.     End With
    32.  
    33.     ReDim Cols(0 To UBound(Arr, 1)) As String
    34.     Set SB = New Collection
    35.  
    36.     Const DblQt         As String = """"
    37.     Const DblDblQt      As String = """"""
    38.     For j = 0 To UBound(Arr, 2)
    39.         For i = 0 To UBound(Arr, 1)
    40.             Select Case VarType(Arr(i, j))
    41.             Case vbNull, vbEmpty
    42.                 Cols(i) = vbNullString
    43.             Case vbString
    44.                 Cols(i) = DblQt & Replace(Arr(i, j), DblQt, DblDblQt) & DblQt
    45.             Case vbDate:
    46.                 Cols(i) = Format$(Arr(i, j), "yyyy\-mm\-dd hh:nn:ss")
    47.             Case Else
    48.                 Cols(i) = Str$(Arr(i, j))
    49.             End Select
    50.         Next i
    51.         SB.Add Join(Cols, ColDelimiter) & LineDelimiter
    52.     Next j
    53.     j = 0
    54.     For i = 1 To SB.Count
    55.         j = j + Len(SB.Item(i))
    56.     Next
    57.     SBConcat = String$(j, 0)
    58.     j = 1
    59.     For i = 1 To SB.Count
    60.         Mid$(SBConcat, j) = SB.Item(i)
    61.         j = j + Len(SB.Item(i))
    62.     Next
    63.    
    64.     Const BOM_UTF       As String = ""   '--- "\xEF\xBB\xBF"
    65.     nFile = FreeFile
    66.     Open DstFile For Binary Access Write Shared As nFile
    67.     Put nFile, , BOM_UTF
    68.     Put nFile, , ToUtf8Array(SBConcat)
    69.     Close nFile
    70. End Sub
    71.  
    72. Public Function ToUtf8Array(sText As String) As Byte()
    73.     Const CP_UTF8       As Long = 65001
    74.     Dim baRetVal()      As Byte
    75.     Dim lSize           As Long
    76.    
    77.     lSize = WideCharToMultiByte(CP_UTF8, 0, StrPtr(sText), Len(sText), 0, 0, 0, 0)
    78.     If lSize > 0 Then
    79.         ReDim baRetVal(0 To lSize - 1) As Byte
    80.         Call WideCharToMultiByte(CP_UTF8, 0, StrPtr(sText), Len(sText), VarPtr(baRetVal(0)), lSize, 0, 0)
    81.     Else
    82.         baRetVal = vbNullString
    83.     End If
    84.     ToUtf8Array = baRetVal
    85. End Function
    Execution time here is 422 ms on sample1.xlsx

    cheers,
    </wqw>

  40. #40

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to save a xls file as csv via ADO ?

    Sorry for the late reply but I got stuck with some work. Well, I am glad to see the above CSV converters are 100% similar in terms of results both with original excel file and the Python output. I tried many other approaches found on net but their functionality failed when faced these tough conditions. Perhaps stuff like this should be kept/integrated somewhere for a future vb library. Because you are interested in having some kind of comparison on this topic I just mention that the original xlsx file has a size 1.4 Mb (4727 rows & 40 columns) and all routines were run on my machine considering following specs:
    - Processor Intel(R) Core(TM) i7-8650U CPU @ 1.90GHz, 2112 Mhz, 4 Core(s), 8 Logical Processor(s)
    - Installed Physical Memory (RAM) 32.0 GB

    VB Code :
    Code:
    Option Explicit
    Private Declare Function WideCharToMultiByte Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long, ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar As Long) As Long
    
    Private Sub Command1_Click()
      New_c.Timing True
         RC5_XLSX_to_CSV App.Path & "\sample.xlsx", App.Path & "\sample.csv"
         Text1.Text = New_c.Timing
    End Sub
    
    Private Sub Command2_Click()
      New_c.Timing True
         API_XLSX_to_CSV App.Path & "\sample.xlsx", App.Path & "\sample.csv"
         Text2.Text = New_c.Timing
    End Sub
    
    Private Sub API_XLSX_to_CSV(SrcFile As String, DstFile As String, Optional ColDelimiter As String = ",", Optional LineDelimiter As String = vbCrLf)
        Dim i               As Long
        Dim j               As Long
        Dim arr()           As Variant
        Dim Cols()          As String
        Dim SB              As Collection
        Dim SBConcat        As String
        Dim nFile           As Integer
        Dim prop            As String
      
      Dim rec As ADODB.Recordset
      Set rec = New ADODB.Recordset
      
      prop = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & SrcFile & "';Extended Properties='Excel 12.0 Xml;HDR=Yes'"
    
      With rec
            .CursorLocation = adUseClient
            .Open "SELECT * FROM [TT$]", prop, adOpenStatic, adLockReadOnly
      arr = .GetRows
            .Close
      End With
     
        ReDim Cols(0 To UBound(arr, 1)) As String
        Set SB = New Collection
     
        Const DblQt         As String = """"
        Const DblDblQt      As String = """"""
        For j = 0 To UBound(arr, 2)
            For i = 0 To UBound(arr, 1)
                Select Case VarType(arr(i, j))
                Case vbNull, vbEmpty
                    Cols(i) = vbNullString
                Case vbString
                    Cols(i) = DblQt & Replace(arr(i, j), DblQt, DblDblQt) & DblQt
                Case vbDate:
                    Cols(i) = Format$(arr(i, j), "yyyy\-mm\-dd hh:nn:ss")
                Case Else
                    Cols(i) = Str$(arr(i, j))
                End Select
            Next i
            SB.Add Join(Cols, ColDelimiter) & LineDelimiter
        Next j
            j = 0
        For i = 1 To SB.Count
            j = j + Len(SB.Item(i))
        Next
            SBConcat = String$(j, 0)
            j = 1
        For i = 1 To SB.Count
            Mid$(SBConcat, j) = SB.Item(i)
            j = j + Len(SB.Item(i))
        Next
        
        Const BOM_UTF       As String = ""   '--- "\xEF\xBB\xBF"
        nFile = FreeFile
        Open DstFile For Binary Access Write Shared As nFile
        Put nFile, , BOM_UTF
        Put nFile, , ToUtf8Array(SBConcat)
        Close nFile
    End Sub
    
    Private Sub RC5_XLSX_to_CSV(SrcFile As String, DstFile As String, Optional ColDelimiter$ = ",")
      Dim i As Long, j As Long, arr() As Variant, Cols() As String, SB As cStringBuilder, prop As String
      
      Dim rec As ADODB.Recordset
      Set rec = New ADODB.Recordset
      
      New_c.FSO.CreateFileStream DstFile, STRM_WRITE
      
      prop = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & SrcFile & "';Extended Properties='Excel 12.0 Xml;HDR=Yes'"
    
      With rec
            .CursorLocation = adUseClient
            .Open "SELECT * FROM [TT$]", prop, adOpenStatic, adLockReadOnly
      arr = .GetRows
            .Close
      End With
     
      ReDim Cols(0 To UBound(arr, 1)) As String
      Set SB = New_c.StringBuilder
      Const DblQt$ = """", DblDblQt$ = """"""
      
      For j = 0 To UBound(arr, 2)
          For i = 0 To UBound(arr, 1)
              Select Case VarType(arr(i, j))
                Case vbNull, vbEmpty:  Cols(i) = vbNullString
                Case vbString:         Cols(i) = DblQt & Replace(arr(i, j), DblQt, DblDblQt) & DblQt
                Case vbDate:           Cols(i) = Format$(arr(i, j), "yyyy\-mm\-dd hh:nn:ss")
                Case Else:             Cols(i) = Str$(arr(i, j))
              End Select
          Next i
              SB.AppendNL Join(Cols, ColDelimiter)
      Next j
      
      New_c.FSO.WriteTextContent DstFile, SB.ToString, True, True '<- True, True == UTF8 + BOM
    End Sub
    
    Private Function ToUtf8Array(sText As String) As Byte()
        Const CP_UTF8       As Long = 65001
        Dim baRetVal()      As Byte
        Dim lSize           As Long
        
        lSize = WideCharToMultiByte(CP_UTF8, 0, StrPtr(sText), Len(sText), 0, 0, 0, 0)
        If lSize > 0 Then
            ReDim baRetVal(0 To lSize - 1) As Byte
            Call WideCharToMultiByte(CP_UTF8, 0, StrPtr(sText), Len(sText), VarPtr(baRetVal(0)), lSize, 0, 0)
        Else
            baRetVal = vbNullString
        End If
        ToUtf8Array = baRetVal
    End Function
    Python Code:
    Code:
    import pandas as pd
    import os
    import glob
    import sys
    import time
    start = time.time()
    def main(sourcePath, sourceName, destPath):
    	os.chdir(sourcePath)
    	for file in glob.iglob(os.path.join(sourcePath, sourceName)):
    		df = pd.ExcelFile(file)
    		xlsxName=sourceName.split('.')[0]
    		sheetList=df.sheet_names
    		for sht in sheetList:
    			df.parse(sht).to_csv(destPath+xlsxName+'_'+sht+'.csv', index=False)
    main("C:\\Users\\Daniel\\Desktop\\Convert XlsTo Csv\\","sample.xlsx","C:\\Users\\Daniel\\Desktop\\Convert XlsTo Csv\\")
    end = time.time()
    duration = (end - start) * 1000
    print (duration)
    Timing Results (ms):
    Name:  Benchmark.PNG
Views: 1556
Size:  9.5 KB

    Thank you guys for your valuable contribution on this forum(ChrisE,Dile,Olaf,wqweto), I really appreciate your support.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width