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

Photomofo

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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,475
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
248
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
248
Note... strData() = Split(MyData, vbCrLf)

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

Forum statistics

Threads
1,171,831
Messages
5,877,811
Members
433,288
Latest member
Kietkiengiang

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