How to read a csv or excel file and write the data to DB table using vb6

Question

I want to read data from excel file and write the data to a table. I cant use excel.application because microsoft office is not installed.

Public Function ImportExcel(CommonDialog1 As Object)
    Dim sFilePath As String
    Dim fso As New FileSystemObject
    Dim txtStream As TextStream
    Dim sRowRange As Range
    Dim nRows As Integer
    Dim str As String
    'Set the settings for the Common dialogue box and Show
        Set CommonDialog1 = CreateObject("MSComDlg.CommonDialog.1")
        CommonDialog1.Filter = "Microsoft Excel Workbook(*.xls)"
        CommonDialog1.ShowOpen
        If CommonDialog1.FileName <> "" Then
            sFilePath = CommonDialog1.FileName
            If UCase(Right(Trim(sFilePath), 3)) = "XLW" Or UCase(Right(Trim(sFilePath), 3)) = "XLS" Or UCase(Right(Trim(sFilePath), 4)) = "XLSX" Or UCase(Right(Trim(sFilePath), 3)) = "CSV" Then
                Set fso = New FileSystemObject
                Set txtStream = fso.OpenTextFile(sFilePath, ForReading, False)
                
                Do While Not txtStream.AtEndOfStream
                    str = txtStream.ReadLine
                Loop
                txtStream.Close
            Else
                MsgBox "Make sure your selected file has file extension xlw or xls", vbOKOnly + vbInformation
                Exit Function
            End If
        End If
        
    End Function

Have you ever opened an Excel file in a text editor? It's a vastly complicated binary file format. While it is certainly possible to write a bunch of code that parses the file so that you can write the data elsewhere, that is unlikely to be a good way to spend the next several years of your life. There are multiple libraries that handle parsing of Excel files. It would make far more sense to find one that works for you and use it. — Justin Cave
– Justin Cave, Commented Mar 16, 2021 at 17:44
You can use ADO to read the Excel file. For example connection strings, see connectionstrings.com/ace-oledb-12-0 — MarkL
– MarkL, Commented Mar 16, 2021 at 19:41
Its not clear what this question is actually asking. Can you please clarify the specific issue you are facing? Otherwise people probably won't be able to give you much help. — StayOnTarget
– StayOnTarget, Commented Mar 16, 2021 at 20:44
ADO can't read these formats, but you can use ADO or DAO to ask Jet or ACE engines to use their Installable ISAM libraries to read these formats. — Bob77
– Bob77, Commented Mar 16, 2021 at 21:21

Brian M Stafford · Accepted Answer · 2021-03-17 16:15:25Z

2

As has been suggested in the comments, you can use a combination of ADO and the ACE OLEDB Provider to read Excel files. Here is a simple example:

Private Sub ImportExcel()
   Dim cn As ADODB.Connection
   Set cn = New ADODB.Connection
   cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Projects\" & _
           "Read Excel File With ADO\sample.xlsx;" & _
           "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
   
   Dim rs As ADODB.Recordset
   Set rs = cn.Execute("SELECT * FROM [Sheet1$]")
   
   Do While Not (rs.BOF Or rs.EOF)
      'do whatever you need with the data
      MsgBox rs.Fields(0).Value & ", " & rs.Fields(1).Value
      rs.MoveNext
   Loop
   
   rs.Close
   cn.Close
End Sub

I ran this example against Excel 365 but I suspect it will also work on other versions. The RecordSet treats every row in the Excel file as a record and every column as a field.

You will need to add a Reference to Microsoft ActiveX Data Objects X.X Library and have the ACE OLEDB Provider on your computer, too.

edited Mar 17, 2021 at 16:15

answered Mar 16, 2021 at 22:12

Brian M Stafford

8,9432 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Smith Over a year ago

If there are mixed data types in Excel columns an IMEX param shold be added to extended properties. stackoverflow.com/questions/46170343/… microsoft-ssis.blogspot.com/2011/06/…

Collectives™ on Stack Overflow

How to read a csv or excel file and write the data to DB table using vb6

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related