Reading data from .csv files

[JackDanIce]

Hi,

I'm trying to learn how to read data from CSV files (that have been saved from Excel) without opening them.

Searching online, most suggestions ask for a delimiting character but as I saved the file from Excel (without being asked for such a character), I don't know what to specify this as.

I've also read ADO connections can be used with SQL like queries to read in the data. My SQL knowledge is basic, though suspect it will work for simple reading in of entire files (or very basic filters using WHERE keyword).

Can anyone suggest some code to learn from or point me to any suitable websites? There seems to be several ways of reading data from CSV and text files, are there any preferred methods or ones that are faster than others?

Thank you in advance,
Jack

 

[Kenneth Hobson]

Of course the Comma Separated Value (CSV) files by default are delimited by well, commas.

ADO would be best. There are many examples. Here are a few:

Sub pull_data()
  Dim s1 As Worksheet, ML_Dir As String
  ML_Dir = ThisWorkbook.Path
  'Open_Sort_CSV ML_Dir, "ImportCSV.csv", ActiveSheet.Name, True, "Data Used"
  Open_Sort_CSV ML_Dir, "ImportCSV.csv", ActiveSheet.Name, False, "F1"
End Sub

'Add Tools > References... > Microsoft ActiveX Data Objects 2.8 Library
Sub Open_Sort_CSV(CSV_Dir, CSV_name, Data_Sheet, Optional Header As Boolean = True, _
  Optional SortField As String = "", Optional SortASC As Boolean = True)

  Dim connectionString As String
  'Late binding:
  'Dim objConnection As Object, objRecordset As Object
  ' Early Binding:
  Dim objConnection As Connection, objrecordset As Recordset
  Dim A As Integer
  Dim Location As Range, Rw As Long, col As Integer, c As Integer, MyField As Variant

  'set record set variables
  Const adOpenStatic = 3
  Const adLockOptimistic = 3
  Const adCmdText = 1 '&H1
  
  'set connection and recordset
  Set objConnection = CreateObject("ADODB.Connection")
  Set objrecordset = CreateObject("ADODB.Recordset")
  
  'oopen connection (headers,Delimited style,mixed data taken as text(not sure imex works))
  connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & CSV_Dir & ";" & _
    "Extended Properties=""text;HDR=" & Header & ";FMT=Delimited;IMEX=3"""
  objConnection.Open connectionString
  
  'get data from csv
  Select Case True
    Case SortField = ""
      objrecordset.Open "SELECT * FROM " & CSV_name, _
      objConnection, adOpenStatic, adLockOptimistic, adCmdText
    Case SortField <> "" And SortASC
      objrecordset.Open "SELECT * FROM " & CSV_name & " Order By `" & SortField & "` ASC", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
    Case SortField <> "" And SortASC = False
      objrecordset.Open "SELECT * FROM " & CSV_name & " Order By `" & SortField & "` DESC", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
  End Select
  
  'Loop across the fields
  If Header Then
      With objrecordset
           For A = 0 To .Fields.Count - 1
           ' Add field names to data sheet
            If Right(.Fields(A).Name, 7) <> ".NoName" Then _
              ThisWorkbook.Worksheets(Data_Sheet).Cells(1, 1).Offset(0, A).Value = .Fields(A).Name
           Next A
      End With
      'copy data into worksheet under headers
      ThisWorkbook.Worksheets(Data_Sheet).Cells(2, 1).CopyFromRecordset objrecordset
   'Write RecordSet to results area
    Set Location = ThisWorkbook.Worksheets(Data_Sheet).Range("A2")
    Rw = Location.Row
    col = Location.Column
    c = col
    With objrecordset
      Do Until .EOF
          For Each MyField In .Fields
            Cells(Rw, c) = MyField
            c = c + 1
          Next MyField
          .MoveNext
          Rw = Rw + 1
          c = col
      Loop
    End With

  
    Else
      'copy data into worksheet
      ThisWorkbook.Worksheets(Data_Sheet).Cells(1, 1).CopyFromRecordset objrecordset
  End If

    'end connection and recordset
    Set objConnection = Nothing
    Set objrecordset = Nothing
    
End Sub





'Pulls Data from CSV to Data sheet
Sub Open_Sort_CSV_o(CSV_Dir, CSV_name, Data_Sheet, Optional Header As String = "No")

  Dim connectionString As String, objConnection As Object, objrecordset As Object
  Dim A As Integer
  Dim Location As Range, Rw As Long, col As Integer, c As Integer, MyField As Variant

  'set record set variables
  Const adOpenStatic = 3
  Const adLockOptimistic = 3
  Const adCmdText = &H1
  
  'ser connection and recordset
  Set objConnection = CreateObject("ADODB.Connection")
  Set objrecordset = CreateObject("ADODB.Recordset")
  
  'oopen connection (headers,Delimited style,mixed data taken as text(not sure imex works))
  'connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & CSV_Dir & ";" & _
    "Extended Properties=""text;HDR=" & Header & ";FMT=Delimited;IMEX=1"""
  connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & CSV_Dir & ";" & _
    "Extended Properties=""text;HDR=" & Header & ";FMT=Delimited(,);"""
  objConnection.Open connectionString
  
  'get data from csv
  objrecordset.Open "SELECT * FROM " & CSV_name, _
            objConnection, adOpenStatic, adLockOptimistic, adCmdText
  
  'Loop across the fields
  If Header = "Yes" Then
      With objrecordset
           For A = 0 To .Fields.Count - 1
  
           ' Add field names to data sheet
             ThisWorkbook.Worksheets(Data_Sheet).Cells(1, 1).Offset(0, A).Value = .Fields(A).Name
           Next A
      End With
  
      'this errors for no good reason so stop any errors
      'On Error Resume Next
      
      'copy data into worksheet under headers
      ThisWorkbook.Worksheets(Data_Sheet).Cells(2, 1).CopyFromRecordset objrecordset
           'Write RecordSet to results area

  
    Else
      'copy data into worksheet
      ThisWorkbook.Worksheets(Data_Sheet).Cells(1, 1).CopyFromRecordset objrecordset
  End If

    'end connection and recordset
    Set objConnection = Nothing
    Set objrecordset = Nothing
    
End Sub

 

[JackDanIce]

I think the term foot in mouth applies or see what is in front of you! Yes, should have realised comma would be the default separator!

Thank you very much for the suggested code Kenneth, including SQL queries, much appreciated.

Jack

FYI, for any other readers, this is in lieu of opening a file, copying and pasting the data and closing the file.