Forum Discussion

CRogers650's avatar
CRogers650
Copper Contributor
Aug 25, 2023

Excel Visual Basics - Reading Closed .csv files & Grabbing Certain Rows

Hello everyone,

I am pretty new to using VBA and was hoping someone could help me with retrieving certain information from file(s).

At this moment I am trying to get Excel to read and grab information out of certain .csv files (while being closed) in a specific folder. At the moment this part is working, however, instead of retrieving all of the rows in every file, I would like it to only retrieve the first and last row of every file.  I have attached the macro that is being used at the moment. Any help would be appreciated.

 

Sub GetDataFromCSVFiles()
        
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim Addr As String
    Dim SQLString As String
    Dim i As Integer
    
    Addr = Dir(ThisWorkbook.Path & "\postal\*addr.csv")
    
    Do Until Addr = ""
        SQLString = SQLString & " UNION SELECT * FROM [" & Addr & "]"
        Addr = Dir
    Loop
    
    SQLString = Mid(SQLString, 8)
    Debug.Print SQLString
    
    Sheet2.Cells.Clear
    
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = _
    "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "Dbq=" & ThisWorkbook.Path & "\postal\;" & _
    "Extensions=asc,csv,tab,txt;"
    
    cn.Open
    
    Set rs = New ADODB.Recordset
    
    rs.ActiveConnection = cn
    rs.Source = SQLString
    rs.CursorType = adOpenStatic

    
    rs.Open
    
    If rs.RecordCount = 0 Then
        MsgBox "There are no records"
    End If
    
    
    For i = 0 To rs.Fields.Count - 1
        Sheet2.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
   
    Sheet2.Range("A2").CopyFromRecordset rs
    Sheet2.Range("A2").CurrentRegion.EntireColumn.AutoFit
 
    rs.Close
    cn.Close
      
End Sub

 

 

excel
Macros and VBA

4 Replies

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Aug 26, 2023

    CRogers650 

    I think you can use first and last to specify the first and last records like below:

     

    select first(item_description),first(qty) from PiecesMinifigsSets union all select last(item_description),last(qty) from PiecesMinifigsSets;

     

    sample:

    drop table PiecesMinifigsSets;
    create table PiecesMinifigsSets (item_description text,qty double);
    insert into PiecesMinifigsSets values('black title',0.1);
    insert into PiecesMinifigsSets values('black title',0.2);
    insert into PiecesMinifigsSets values('black title',0.3);
    insert into PiecesMinifigsSets values('black title',null);
    insert into PiecesMinifigsSets values('blue title',0.11);
    select * from PiecesMinifigsSets;

    item_description qty

    black title.1
    black title.2
    black title.3
    black title 
    blue title.11


    select first(item_description),first(qty) from PiecesMinifigsSets union all select last(item_description),last(qty) from PiecesMinifigsSets;

    Expr1000 Expr1001

    black title.1
    blue title.11
  • nimesht's avatar
    nimesht
    Iron Contributor
    Aug 25, 2023

    Hi CRogers650,

     

    Is there a unique ID column in the CSV files, which could work as primary key (only once in whole file)?

    If yes, is the CSV file sorted using this unique ID column?

     

    If yes to both, then you can use SQL Top 1 condition along with ASC/DESC order clauses to get 1st and last records.

     

    OR, after importing the data using ADO, keep only the first and last record as below.

    You can create the VBA using record macro as below.

    • select the cell from the 1st record which you need to keep
    • Navigate to the cell in the 2nd row using down key
    • select all the cells till last record using Ctrl + Shift + down key
    • deselect the last row cell using Shift + Up key
    • Click Shift + Spacebar to select complete rows
    • Press Del key to delete

    I hope that should do it for you.

     

    Use Like if this post helped to solve your issue and Mark as Best Response if the request can be closed.

Resources