[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.

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

Quote:

---

Originally Posted by westconn1

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.

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

---

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.

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

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

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.

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.

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.

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

---

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>

Quote:

---

Originally Posted by Daniel Duta

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).

Quote:

---

Originally Posted by wqweto

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:Attachment 169751

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>

Quote:

---

Originally Posted by dilettante

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.

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

---

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

Quote:

---

Originally Posted by Daniel Duta

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

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.

Quote:

---

Originally Posted by Daniel Duta

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

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.

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.

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

Quote:

---

Originally Posted by dilettante

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.

Quote:

---

.. 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

Quote:

---

Originally Posted by Daniel Duta

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

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>

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.

@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>

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.

Quote:

---

Originally Posted by Daniel Duta

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

Quote:

---

Originally Posted by Arnoutdv

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

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

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.

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

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 ?

Quote:

---

Originally Posted by Daniel Duta

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
Attachment 169925

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

Quote:

---

Originally Posted by Daniel Duta

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.

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

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.

Quote:

---

Originally Posted by Daniel Duta

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

Here is Olaf's impl w/ no external dependancies

thinBasic 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 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 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
  
    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 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
 
Public 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

Execution time here is 422 ms on sample1.xlsx

cheers,
</wqw>

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):
Attachment 169957

Thank you guys for your valuable contribution on this forum(ChrisE,Dile,Olaf,wqweto), I really appreciate your support. :wave:

Quote:

---

Originally Posted by dilettante

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.

---

Yes and no: CrLf should simply be treated as a new row; technically you could wrap it in quotes, but I doubt you would find something like that in the wild.

However, you can find embedded linefeeds e.g. in an official UN database available here - starting with footnote 456, Source: Dominican Republic Encuesta Nacional de Hogares..., approximately row 41174, there are 34 of them. I write "approximately" because Excel 2003 doesn't load them correctly.

Which brings me to a few questions - if they are stupid, apologies:

If there is no Office installation on a machine, is it still possible to load an Excel file in format *.xls or *.xlsx via ADO or JET?
If yes, are these applications part of a standard Windows installation?
If so, why does Microsoft allow everybody to open their proprietary formats?

You can't "load" it in any meaningful sense. There is no user interface and no macros can be run.

Jet 4.0 and its Excel 8.0 Installable ISAM are part of Windows so every machine should have them except perhaps for certain Windows Server SKUs where you can omit the WOW64 mechanism. I'm not even sure any recent version allows that any more since it caused no end of problems with nothing gained.

That can be used to read/write data from/to XLS workbooks but not XLSX format workbooks even if the converter library for Office 2003 is installed. So to process XLSX this way you'd need to either convert to XLS format or else install the ACE 12.0 OLEDB Provider and its own IISAMs available as a separate download for free.

But yes, you do not need MS Excel installed in order to access XLS workbook data. Even on a fresh install of Windows. It has been this way for a very long time, at least the late 1990s.

Thanks, very interesting ;-)

Quote:

---

Yes and no: CrLf should simply be treated as a new row; technically you could wrap it in quotes, but I doubt you would find something like that in the wild.

---

By accident I encountered such a file in the wild yesterday.
I did a dump of the contacts of my outlook.com account, which is a CSV file.
The text stored in the "notes" field can wrap multiple lines, which are stored as CRLF in the text file, thus giving new lines for a field.

That's a gross one! Fortunately, I don't have to use Outlook ;-)

Quote:

---

Originally Posted by jj2007

That's a gross one! Fortunately, I don't have to use Outlook ;-)

---

Such files, which for some are exceptions, are common files at my workplace.
These files come automatically from a BI tool and it only exports data from another tool where users have the freedom to write anything in certain text boxes from GUI. Thus, in the Description field, we can find all sorts of exotic characters, email messages or explanatory texts copied from other tools. Therefore, the difficulty of parsing as CSV. So, the Excel file itself is not modified by Outlook in any way - in fact, there is no Outlook here, but a local mail server that receives MIME-formatted emails. Fortunately, there are some experienced guys in the forum who have managed to offer a robust solution that I have not met it elsewhere. Note that conversion time is very close to Python, but Python uses a class that reads directly from the excel file archive. We can not say exactly, but perhaps in our case a bottleneck could be the ADO itself if we consider that in my tests the ADO reading time is on average about 70% of the total processing time ...

Why don't you use tab-delimited instead? It's much easier to handle than csv. Of course, in theory a user could insert a tab character in a field, but that could be substituted with a space before storing it. Which would be a good thing anyway, replacing tabs with \t and crlf's with \n before storing the stuff in a cell.