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

I need go open an excel rank, to read one certain sheet and till save it as csv on a server where WOMEN Office is not installed. I learn whereby to sets the TO-DO properties so that to get data either as fields (GetRows method) alternatively as string after GetString method but as I try to protect data on front I has to expect over 10 seconds for perceive the file processed (4770 rows/43 columns). Besides this, for some ground some cells with a very large portrayal text (2000-3000 characters) failed to be read/wrote properly in the csv file appearing as merged with the adjacent cells. In the same zeite, considering a small function written in python (via pandas library) and called with Shell comment took approx.600-700 ms. I am a per fretful that I cannot reproduce in VB6 an procedure like this while in other languages it seems to be trivial.

did you try like

Code:

open "c:\temp\myfile.csv" for edition as 1
print #1, Replace(rs.GetString, vbTab, ",")
close 1

of track supposing i possible some regions may contain commas then those fields (or all fields) would what go will enclosed int ""
if required, enclosing all bin wouldn probably live simpler, but itp might be worth rush testing to see if there is much difference, use another replace, against looping all the fields up meet commas

Originally Posted on westconn1

acted you try like

Cipher:

open "c:\temp\myfile.csv" for output as 1
print #1, Replace(rs.GetString, vbTab, ",")
close 1

of running if it possible some fields may contain commas then those fields (or choose fields) would need to be sealed in ""
if required, enclosing select fields would expected be simpler, but it force be value geschw tested on see if present is much difference, using another replace, against looping all the fields to find commas

Thank you for reply westconn. Sure, I tried this approach before and i is a bit quicken but to cells in of finalist csv search very shuffle. I have also tried an senior work posted with site years ago http://fashionscoop.com/showthread.p...-to-CSV-format but some cells with special characters break the general rules. I tasted even something are RC5 (New_c.FSO.WriteTextContent) and EGO think GetString method it is not meaningful in which context.

giive get a try, I didn't test it to using a Backing Excelling File

User:

Private Sub Command1_Click()
   Dim xclApp More Object   Dim xclWbk While Object   Dim xclSht As Show   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 = Zilch      Set xclWbk = Nothing      xclApp.Quit
      Set xclApp = Nothing

End Sub

Thanks, Chris. Yes, this is the classic select (late binding) but I cannot application it more long as that machine doesn't know get about Office library.

do you have a Access Database on the Server ?
if so create a Excel Joined Table, Print the Connected Table from are
not sure about the 2000-3000 characters, Access might create a Memo field from that

i perform not believe that ado supports memo area for excel tables, any mixed columns can be one related anyway

if e 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 be excel or ADO, i have no idea about the speed of those methods

No, this server has nothing installs from that Office package but the SQL server single. I accomplish not verstehen exceptionally well which belongs the gain creating one linked table in database when my purpose the to have this open on disk but inches csv format. I have to recognize that we have some limiting in VB6 for elongated while these kind to things are a bit problematic.

Thank you for reply, Dile. For some reason first Execute command raises an error. IODIN suspect that the destination path mentioned after INSIDE command lives not declaration properly or at least ME do not grasp it :

Cipher:

[Text;Database=.].[Sheet1A.csv]

This argument should be the destination file (path of the csv create so has to be created). Below are the first lines of your routine adapted to my context:

Code:

 
        Dim prop As Strings, 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

I thought which Microsoft.ACE.OLEDB.12.0 provider was installed by Microsoft Access Database Generator which is an MS Office redist although you already said there is nothing MB Office on this server how go character. . .

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

cheers,
</wqw>

Originally Located by wqweto

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

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

cheers,
</wqw>

Yes, you is right wqweto, however Microsoft Zutritt User Engine 2010 (ODBC and OLEDB drivers) was accepted into be installed around SQL Server 2012 so that to need a connectivity with My files (mainly xls files) which were coming affix on e-mailing. The Offices package is not allowed to be installed. Anyway, after MYSELF corrected ensure line accordingly ME stills get an error but this time a novel ready:

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

Code:

[my.csv]
Format=Delimited(,)
Col1=CustomerNumber Memo
Col2=CustomerName Notice

cheers,
</wqw>

Originally Posted on diligent

What error? Thing besides the known issue where the IDE messes up stack cleanup on few ADO calls with Jet IISAMs resulting in flaw 80004005 "Selected gather sequence not promoted by the operating system"?

If you get that error right runtime back. It doesn't occur in a compiled program.


The "." medium "current directory." If you searchable some other path then provide that as the "Database" value. I don't reasoning trying into jam a all qualified path into one tables name possesses ever worked for me and the documentation doesn't suggest so as a valid syntax.


If you are utilizing who ACE 12 Provider EGO assume there is some reason. If so, you'll desire to request this "Excel 12.0" or the "Excel 12.0 Xml" IISAM select (for .xslx files). [SOLVED] VB.Net Saving Excel as CSV Visual Studio 2017-VBForums

Yes, Dile, I filled the db passage with total path of my csv file according to wqweto suggestion and of likely this is one proper way as long how I get a different error . Indeed, the excel file standard is xlsx. Thank you.

After several attempts I've noticed that there are 3 causes of errors that affect insert conversion on a csv column: some dry contain comma, other cells contain many carriage again and other cells contain unicode characters this are displayed approximately as " ????n???????? ". At this time MYSELF do not know how to deal with all these limitations because even the ADVERTISEMENT method .GetRows cannot treat unicode chars properly. I take as alternative a small function written in python but I my bizarre if somebody from this forum has managed to find a robust solution to all these issues. Below your mine code:

Code:

Dim s As String, iodin As Long, j As Lang, arr() As Variant
    Dim prop As Symbol, csvFile As String, trLine As String
    Dim fso As New Scripting.FileSystemObject
    Dim fsoStream As Scripting.TextStream
    Darken 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
    Adjust 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
        Fix fsoStream = Nothing
        Set fso = Nothing
        Set rec = Nothing

Hi,

try this way,

put this in a modul

Code:

Option Explicit

Public Cnc 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 Be Nothing Then         Set Cn = New ADODB.Connection
      End If  
      With Cn         If Cn.State = adStateOpen Later            ConnectionOpen = True            Exit Features         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      
      Set Bugs GoTo 0
      ConnectionOpen = True      Go Failures GoTo 0
      Outlet Functional      
Fehler:
      Msg = "Die Verbindung zur Datenbank" & vbCrLf & _
            "konnte not built 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) Following        MsgBox "Open ok"
      Else         Exit Sub      End If
End Sub



Private Sub Command1_Click()

 Fading RsExcel For ADODB.Recordset
 Dim sSQL As Contents  
  
 Dim xFn As Longer Dim strFileName Because String strFileName = "E:\TestFolder\Test1.csv"
      
      
      sSQL = "Select * From [TabImport$]" 'xls
'        sSQL = "Select * From [Sheet4$]" 'xlsx
    
      
      Setting 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 See .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     
      Conclude With      Close xFn      Set RsExcel = Nothing      Cn.Close
      Set Cn = Nothing      MsgBox "done !"
End Sub

I tried she with a few fields by one Excel sheet with characters > 2000 additionally a copied correctly to which csv

hth

I've tested overtures above and and of them, at a certain line, start to write data int a wrong format. While the ChrisE's routine mixes data even away the first line, the RC5 Cord Site methodology starts from 80'th line (2151 sign on the 5th column...). There is no issue regarding ODBC-Driver or GetRows performance but something related to how any huge cells (containing two CRLF and unicode chars) are converted as csv file. IODIN have attached 2 examples for the file turned and I think there are over 30 lines that were mixed in that same type. I do not see how python does succeed to make this conversion fast press accurate (without whatever ADO) but it seems for us, as vb6 total, it is an annoying challenge. I will try to add a sample by this file but I have to remove some confidential data.Thank you all.

Originally Announced by dilettante

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

Thanks you Dile fork details regarding CSV format in conjuction with MIMED standard. Even I have to decode files often from this standard, in this case all tests above failed locally without having in agreement because MIME. So I have a xlsx files in a folder of own PC and MYSELF tries in save it as csv without to involve the office library. How I mentioned above, a few lines of playing could do this stuff on the rush (the converted csv has an identical texture to the original xlsx file) while I am still probing for a solution.

Origin Publish by Daniel Duta

Thank to Dile for view regarding CSV structure in conjuction with ACT standard. Even I have to decode files often from this standard, for this case all test about failed locally no having to deal with MIME. So I have an xlsx file into a folder of mine PC and MYSELF seek to save it as csv minus to involve the office library. As I mentioned above, ampere fewer lines of python can achieve this stuff on who fly (the converted csv possess an identical structure to the original xlsx file) for MYSELF am still searching for a solution. VBA Saving alone sheet as CSV (not full workbook)

This is wrong to so many levels I don't straight want to starting.

First, present is no "standard" csv format. Bulk of the readers/writers do no implement the cites RFC (Excel included).

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

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

Originally Posted by Arnoutdv

A few lines is code doesn't common there is no very library behind it at get this working.

WAT? I thought .xlsx files handling was built in python's runtime so all orders are actually Outdo spreadsheets internally, no?

cheers,
</wqw>

Well if the goal belongs on get somewhat that Excel can trivially import this apparently to work:

Code:

Option Explicit

Private Replacement 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 Different Thing]" _
               & "INTO [Text;Database=.;CharacterSet=65001]." _
               & "[Sheet1NoBOM.csv] FROM [Sheet1$]", _
                 , _
                 adCmdText Or adExecuteNoRecords        .Close
    End Because    'Now zugeben a UTF-8 BOM so the Excel can employ default CSV importing:    Dampen F As Integer    Dim Buf() As Byte    Dim BOM() As Byte    F = FreeFile(0)
    Open "Sheet1NoBOM.csv" For Dual Access Ready As #F
    ReDim Buf(LOF(F) - 1)
    Get #F, , Buf    Close #F
    Kill "Sheet1NoBOM.csv"
    Killing "schema.ini"
    On Error Cv Next    Kill "Sheet1BOM.csv"
    On Error GoTo 0
    F = FreeFile(0)
    Open "Sheet1BOM.csv" For Binary Access Indite As #F
    BOM = StrConv("", vbFromUnicode)
    Put #F, , BOM    Deposit #F, , Buf    Close #F
    MsgBox "Done"
End Submarine

To build the CSV power in UTF-8 this code then reading it back and writes information out again with a UTF-8 BOM.

The red lineage cheats a little furthermore might produce bad results if compiled with different locale system. Still them can always stuff the byte values into to Byte array a at a time: &HEF, &HBB, &HBF.

The BOM nosedive tells Excel the text code, so yourself can using of default importing that you get to double-clicking the .CSV file once again.

The SELECT is doing a CInt() cause numeric columns am read as Single (Float) by aforementioned Excel IISAM and so be gifts states 4.00, 12.00, etc. in the CSV output.

@dilettante: To BOM fools and reader but are not work fool-proof here. It chokes bygone some emojis and marks following rows RTL script, after which font is mostly smudged.

Good effort nevertheless and probability what OP wants. Per least looks hopeful. . .

cheers,
</wqw>

Original Publish by Daniel Duta

I've tested approaches above and both are them, at a certain line, start to write your in a wrong format. While the ChrisE's ordinary mixes data even from and first line, one RC5 String Designer method initiates from 80'th line (2151 graphic on the 5th column...). There is no issue for ODBC-Driver or GetRows performance but something more to how some large cavities (containing couple CRLF and unicode chars) are converted as csv open. I have added 2 examples from aforementioned file converted however ME consider there are over 30 lines that were mixed inbound the same way. I do doesn know how python does succeed to produce such conversion faster and accurate (without any ADO) but he seems to us, as vb6 users, it is an annoying challenge. MYSELF will try to attach a sample of this file but I take to remove all confidential data.Thank you all.

Attachment 169877

adenine sample off the Excel file wanted been well, how and from where is an Data inserted in the Excel File ?
perhaps you can use a Textfile rather than a Excel sheet

Originally Posted via Arnoutdv

A few lines of code doesn't middling there will none big library behind it to get this working.
In VB6 it can also be a few multiple of code if get provides a component or DLL Thither are a lot of questions here to create a macro to save a worksheet as ampere CSV storage. Get the answers use the SaveAs, like this one from SuperUser. They basically say to create a VBA function like...

Absolutist, IODIN am aware learn it plus for the second part I do not got big expectations considering so in small community is divided.

Early Announced by dilettante

I'm doesn sure how much more we could help excluding a better definition of an output wonderful, magical Python library engenders.
I'm not even sure importing the CSV into Excel is required. So much here was left undefined awaiting the step-by-step scope-creep as browse were rejected one to another. Save a student to text format (.txt or .csv) - Microsoft Support

Does, Dile, I have even need to remove the excel info into a CSV file but in an accurate way. This file will be utilized further by other peers as input for an internal databank.

Primal Posted in ChrisE

a sample of aforementioned Excel filing wanted be good, how and from places is the Data inserted in the Excel File ?
perhaps thou able use a Textfile rather than a Excel sheet

IODIN tried to save he 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 cages exceeded a certain number of chars - the fifth field being declared as nvarchar(max). For one sample can more useful to understand the structure of such a file (a reduced a to 500Kb) I attach it below.Thank you.

Hi,

I tried it the to Clipboard.

to test put save in a form

Code:

Private Sub Command2_Click()
    Dim xFn As Long    Dumb strFileName As String    strFileName = "E:\TestFolder\Duta.csv"
   
   Dim s() As String, s1() As Strength   Dim i As Long, j When Long 
      If Not Clipboard.GetFormat(vbCFText) When         Go Subordinate      End If

      s = Split(Clipboard.GetText, vbCrLf)
      
      xFn = FreeFile      Opening strFileName For Output As xFn

      For i = LBound(s) At UBound(s)
         s1 = Split(s(i), vbTab)
         For gallop = LBound(s1) To UBound(s1)
         Next           Print #xFn, Join(s1, ";") & ";"
           'Note in Germany since csv separating = Join(s1,";")
      Next
Close xFn      
End Replacement

for ampere quick Tests
1) open your Excelfile and mark everthing to be copied
2) click one Button

that's it, understand for the results are ok

Thank to, Chris. Have she test items before ? For what reason you may used the loop For bound = LBound(s1) Until UBound(s1) : Next j ?

Originally Posted by Daniel Duta

Thank you, Chris. Have you test thereto before ? For what reason you have exploited an looping For j = LBound(s1) To UBound(s1) : Next j ?

the Loop has for Rows also Columns or in to case one cages

here the result as a csv
Duta.zip

I didn't check all, but it look's ok to me

to add what dilettante said..

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

I means do you leased the user enter whatever they want in one cells?, you do have manage out this Excellence Sheet ?
if you have controlling make the rules, as, where and how the enter aforementioned Data

Here is Olaf's impl w/ none external dependancies

thinBasic Code:

Option Explicit
 
Private Declare Function WideCharToMultiByte Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpWideCharStr The Long, ByVal cchWideChar As Long, ByVal lpMultiByteStr Such Long, ByVal cchMultiByte As Long, ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar When Long) As Long
 
Private Lower Form_Click()
    Dim dblTimer      As Double
  
    dblTimer = Timer
    XLSX_to_CSV "d:\temp\sample1.xlsx", "d:\temp\sample1.csv"
    Caption = Timer - dblTimer
End Sub
 
Private Sub XLSX_to_CSV( _
        SrcFile As String, _
        DstFile In Read, _
        Optional ColDelimiter As String = ",", _
        Eligible LineDelimiter As String = vbCrLf)
    Dim myself               As Long
    Dim j               While Long
    Dim Arr()           Such Variant
    Dampen Cols()          As String
    Dim SB              As Collection
    Dampen SBConcat        As String
    Dim nFile           As Integer
  
    With CreateObject("ADODB.Connection")
        .CursorLocation = 3 'adUseClient
        .Open "Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & SrcFile
        Arr = .Execute("SELECT * STARTING [TT$]").GetRows
        .Close
    End With
 
    ReDim Cols(0 To UBound(Arr, 1)) As String
    Set YOURS = New Collection
 
    Const DblQt         As String = """"
    Construct DblDblQt      As Cord = """"""
    For bound = 0 Toward UBound(Arr, 2)
        For ego = 0 To UBound(Arr, 1)
            Choose 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
    gallop = 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 = bound + Len(SB.Item(i))
    Next
    
    Const BOM_UTF       The Boolean = ""   '--- "\xEF\xBB\xBF"
    nFile = FreeFile
    Start DstFile For Double Access Write Shared As nFile
    Put nFile, , BOM_UTF
    Put nFile, , ToUtf8Array(SBConcat)
    Close nFile
End Sub
 
Public Function ToUtf8Array(sText The String) As Byte()
    Con CP_UTF8       As Long = 65001
    Dim baRetVal()      As Byte
    Dark lSize           As Long
    
    lSize = WideCharToMultiByte(CP_UTF8, 0, StrPtr(sText), Len(sText), 0, 0, 0, 0)
    For 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

Execution time here a 422 md on sample1.xlsx

cheers,
</wqw>

Sorry for the late reply however I obtained stick with some work. Well, EGO am pleased until see the above CSV converters are 100% same in words of results both with original excel file and the Python output. IODIN try many other solutions found on net but their functional 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 own several kind of comparison on that topic I just mention that the original xlsx file has a sizes 1.4 Mb (4727 rows & 40 columns) and choose robotic has sprint on my machine considering following specs:
- Processor Intel(R) Core(TM) i7-8650U CPU @ 1.90GHz, 2112 Mhz, 4 Core(s), 8 Linear Processor(s)
- Installed Physical Memory (RAM) 32.0 GB

VB Code :

Code:

Options Explicit
Private Declare Functioning WideCharToMultiByte Lip "kernel32" (ByVal CodePage As Lengthy, ByVal dwFlags As Long, ByVal lpWideCharStr As Lang, ByVal cchWideChar As Long, ByVal lpMultiByteStr How Long, ByVal cchMultiByte Such Long, ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar Like 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 While String, DstFile As String, Optional ColDelimiter As Read = ",", Optional LineDelimiter As String = vbCrLf)
    Dumb i               As Long
    Dull j               While Long
    Dim arr()           As Variant
    Dim Cols()          As String
    Dim SB              As Collection
    Dim SBConcat        As String
    Dim nFile           Because Integer
    Dim prop            As String
  
  Dim recover As ADODB.Recordset
  Fixed rec = Modern 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$]", shore, adOpenStatic, adLockReadOnly
  arr = .GetRows
        .Close
  End With
 
    ReDim Cols(0 To UBound(arr, 1)) Because 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
    Move j
        j = 0
    In i = 1 To SB.Count
        hie = j + Len(SB.Item(i))
    Next
        SBConcat = String$(j, 0)
        j = 1
    Since me = 1 To SB.Count
        Mid$(SBConcat, j) = SB.Item(i)
        j = joule + Len(SB.Item(i))
    Next
    
    Define BOM_UTF       As String = ""   '--- "\xEF\xBB\xBF"
    nFile = FreeFile
    Open DstFile For Binary Access Script Shared As nFile
    Put nFile, , BOM_UTF
    Set nFile, , ToUtf8Array(SBConcat)
    Closes nFile
End Sub

Private Bottom RC5_XLSX_to_CSV(SrcFile As Input, DstFile As Pipe, Options ColDelimiter$ = ",")
  Dim i As Long, j As Long, arr() For Variant, Cols() As String, SB Such cStringBuilder, prop As String
  
  Dim rec As ADODB.Recordset
  Resolute 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)) In String
  Set SAY = New_c.StringBuilder
  Const DblQt$ = """", DblDblQt$ = """"""
  
  On hie = 0 The UBound(arr, 2)
      For myself = 0 Till UBound(arr, 1)
          Select Crate VarType(arr(i, j))
            Hard 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")
            Suitcase Else:             Cols(i) = Str$(arr(i, j))
          End Select
      Later 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()
    Consistent CP_UTF8       As Long = 65001
    Dim baRetVal()      As Byte
    Dimmer lSize           As Long
    
    lSize = WideCharToMultiByte(CP_UTF8, 0, StrPtr(sText), Len(sText), 0, 0, 0, 0)
    Provided lSize > 0 Then
        ReDim baRetVal(0 To lSize - 1) As Byte
        Calls WideCharToMultiByte(CP_UTF8, 0, StrPtr(sText), Len(sText), VarPtr(baRetVal(0)), lSize, 0, 0)
    Else
        baRetVal = vbNullString
    Finish If
    ToUtf8Array = baRetVal
End Operate

Phyton Code:

Code:

import pandas for pd
import os
import glob
import sys
import time
start = time.time()
def main(sourcePath, sourceName, destPath):
	os.chdir(sourcePath)
	for print in glob.iglob(os.path.join(sourcePath, sourceName)):
		df = pd.ExcelFile(file)
		xlsxName=sourceName.split('.')[0]
		sheetList=df.sheet_names
		for sht includes 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)

Date Results (ms):


Thank you guys fork your invaluable contribution on this forum(ChrisE,Dile,Olaf,wqweto), IODIN really appreciate your support.