CSV to recordset

Tested in Excel 365 (16.8730.2046) 64-bit. All links open in new tab.

More about Schema.ini

 
 
  1. Public Sub CSVToRecordset()
  2. '===================================================================================
  3. ' Created by: Sanda Vladescu
  4. ' Updated on:
  5. ' Purpose: create a recordset from a csv file
  6. ' References: Microsoft ActiveX Data object
  7. ' Usage example:
  8. '
  9. '===================================================================================
  10.    
  11.     ' needs reference to Microsoft ActiveX Data object...
  12.    Dim folderPath As String, FileName As String, FilePath As String
  13.     Dim cn As ADODB.Connection
  14.     Dim rs As ADODB.Recordset
  15.     Dim strSQL, i
  16.    
  17.     FilePath = PickFileOrFolder(1)
  18.     folderPath = Left(FilePath, InStrRev(FilePath, "\") - 1)
  19.     FileName = Right(FilePath, Len(FilePath) - Len(folderPath) - 1)
  20.    
  21.     Set rs = ReadCSV(";", folderPath, FileName)
  22.     For i = 0 To rs.Fields.Count - 1
  23.         Debug.Print rs.Fields(i).Name & ":", rs.Fields(i).Value
  24.     Next i
  25.     ' do things with the recordset
  26.    Set rs = Nothing
  27. End Sub
  28. '
  29. Public Function ReadCSV(Delim As String, FilePath As String, _
  30.     FileName As String) As Recordset
  31.    
  32.     ' needs reference to Microsoft ActiveX Data object...
  33.    Dim cn As ADODB.Connection
  34.     Dim rs As Recordset
  35.     Dim strSQL As String
  36.    
  37.     ' create Schema.ini file
  38.    Open FilePath & "\Schema.ini" For Output As #1
  39.     Print #1, "[" & FileName & "]"
  40.     Print #1, "Format = " & ConvertDelimiter(Delim)
  41.     Print #1, "ColNameHeader = True"
  42.     Close #1
  43.    
  44.     Set cn = New ADODB.Connection
  45.     Set rs = New ADODB.Recordset
  46.     cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" & _
  47.                    "Data Source=" & FilePath & ";" & _
  48.                    "Extended Properties=""text;HDR=Yes;FMT=Delimited(;)"";")
  49.                    ' extended properties are overidden by schema.ini
  50.    
  51.     strSQL = "select * from " & FileName
  52.     rs.Open strSQL, cn
  53.     Set ReadCSV = rs
  54. End Function
  55. '
  56. Public Function ConvertDelimiter(Delim As String)
  57.     Dim myDelim
  58.     Select Case True
  59.         Case Len(Delim) = 1
  60.             myDelim = "Delimited(" & Delim & ")"
  61.         Case UCase(Delim) = "TAB"
  62.             myDelim = "TabDelimited"
  63.         Case Else
  64.             myDelim = "FixedLength"
  65.     End Select
  66.    
  67.     ConvertDelimiter = myDelim
  68. End Function

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.