Can you extract data from a CSV file without opening it?

[Photomofo]

I seem to recall being able to do this but it's been 10 years since I last used the trick.

Here's what I have so far. I adapted some code I found searching for how to answer this question. Here's the particular line I think I need to change:

' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
Set src = Workbooks.Open(Folder_CSV & ARG & ".csv", True, True)

Any suggestions?

Option Explicit


Sub ReadDataFromCloseFile()
    
Dim i As Long
Dim j As Long
Dim Month_x As Integer
Dim Year_x As Long
Dim ARG As String
Dim Folder_CSV As String
Dim Data As Variant
Dim States As Variant
Dim XYZ_Array As Variant
Dim src As Workbook
    
ThisWorkbook.Save
    
States = Sheets("States").Range("A1:B14")
XYZ_Array = Sheets("States").Range("Z1:Z1000000")
    
MsgBox ("Select folder with CSV files.")


Folder_CSV = Get_Folder
    
For Year_x = 1997 To 2018
For Month_x = 1 To 12
For j = 2 To 14 
    
    'Get File Name
    
    If Month_x < 10 Then
    
        ARG = Year_x & States(j, 2) & "0" & Month_x
        
    Else
    
        ARG = Year_x & States(j, 2) & Month_x
    
    End If
    
    If Dir(Folder_CSV & ARG & ".csv") <> "" Then
    
    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
    Set src = Workbooks.Open(Folder_CSV & ARG & ".csv", True, True)
    
    Else
    
        Stop
    
    End If

    'Grab some data
    
    Data = src.Worksheets(ARG).Range("C3:C1048576")
    
    ' CLOSE THE SOURCE FILE.
    src.Close False             'FALSE - DON'T SAVE THE SOURCE FILE.
    Set src = Nothing
    
    For i = 2 To 1000000
    
        If Data(i, 1) = "" Then
        
            Exit For
            
        End If
        
        ORIS_Array(Data(i, 1), 1) = Data(i, 1)
    
    Next i
    
Next j
Next Month_x
Next Year_x


Sheets("States").Range("Z1:Z1000000") = ORIS_Array


    
End Sub


Function Get_Folder()


Dim Folder_x As String


Folder_x = Application.GetOpenFilename()


Do While Right(Folder_x, 1) <> "\"


    Folder_x = Left(Folder_x, Len(Folder_x) - 1)


Loop


Get_Folder = Folder_x


End Function

 

[John_w]

Try Data tab -> From Text and run the Text Import Wizard. That imports a CSV file without opening it. Record a macro and edit the code if you need to customise the process.

 

[Photomofo]

Thanks... I got it to work using this. Things are running a lot faster now.

Option Explicit

Const Delim As String = ""","""

Sub ReadDataFromCloseFile()
    
Dim i As Long
Dim ARG As String
Dim Folder As String
Dim TmpAr() As String
Dim MyData As String
Dim strData() As String
    
    If Dir(Folder & "xyz.csv") <> "" Then
    
        Open Folder & "xyz.csv" For Binary As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
        MyData = Space$(LOF(1))
        Get [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , , MyData
        Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
        strData() = Split(MyData, vbCrLf)
        
        For i = 1 To UBound(strData)
        
            If Len(Trim(strData(i))) <> 0 Then
            
                TmpAr = Split(strData(i), Delim)
               
                'Data in Column A
                ARG = TmpAr(0)
                
                'Data in Column B
                ARG = TmpAr(1)
            
            End If
            
        Next i

 

[Photomofo]

Note... strData() = Split(MyData, vbCrLf)

should be: strData() = Split(MyData, vbLf)