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

Photomofo

Board Regular
Joined
Aug 20, 2012
Messages
240
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?


Code:
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,891
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

Board Regular
Joined
Aug 20, 2012
Messages
240
Thanks... I got it to work using this. Things are running a lot faster now.

Code:
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
 
Last edited:

Photomofo

Board Regular
Joined
Aug 20, 2012
Messages
240
Note... strData() = Split(MyData, vbCrLf)

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

Forum statistics

Threads
1,136,341
Messages
5,675,206
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top