Add a column with file name while importing .csv file in vba

chesterpark · Sep 28th 2015

Hello
Over the years I have always found what I needed eventually by searching, I have some code I have been using and it works spectaculary, and after hours i cant seem to get it right

But I would Like to take it one step farther, I need it to take the file name and add a column

IE it currently goes through each file in a folder and pulls 2 columns from it and pastes those into excel moving to right as it goes througg say 500 files
it currently puts the file name above the last column which has worked great until i want to manipulate the date easier.

[ATTACH=CONFIG]67053[/ATTACH]

So if possible I was wondering if anyone knew how to change my code so that it will have the "name of text file" then the 2 columns, then repeat like it does now

Actually another thing that would be great is, instead of being across like it is, add the name of the text file and just have 3 rows going downward instead of Left to right like now, so i can make a pivot table, not sure which way i would like best but either is great

Anyway i hope i have explained it enough

here is my code

Code

Sub Button3_Click()
    Sheet1.Cells.ClearContents


    Dim myDir As String, fn As String, ff As Integer, txt As String, flg As Boolean
    Dim delim As String, n As Long, b() As Variant, x As Variant, t As Integer
    
    myDir = "C:\Users\ASUS\Documents\Basket Stocks"
    delim = vbTab
    
    fn = Dir(myDir & "\*.csv")
    Do While fn <> ""
    
        ReDim b(1 To Rows.Count, 1 To 2)
        ff = FreeFile
        Open myDir & "\" & fn For Input As #ff
        
     '   Line Input #ff, txt  'Ignore header line
        
        Do While Not EOF(ff)
            Line Input #ff, txt
            x = Split(txt, delim)
            If Not flg Then
                n = n + 1: b(n, 1) = fn
                flg = True
            End If
            If UBound(x) > 0 Then
                n = n + 1
                b(n, 1) = x(2)
                b(n, 2) = x(1)
            End If
        Loop
        
        
      '  Columns(1).NumberFormat = "@"   'text
      '  Columns(2).NumberFormat = "General"  'general
      '  Columns(3).NumberFormat = "0"      'number
      
       '  Columns(1).NumberFormat = "0.00"
        ' Columns(3).NumberFormat = "0.00"


        
        
        
        Close #ff
        ThisWorkbook.Sheets(1).Cells(1, 1 + t * 2).Resize(n, 2).Value = b
        t = t + 1
        n = 0
        fn = Dir()
        flg = False
        
    Loop
    


End Sub

Display More

**pike** · Oct 9th 2015

Re: Add a column with file name while importing .csv file in vba

hello,
Maybe just write straight to the worksheet

Code

myDir = "C:\Users\ASUS\Documents\Basket Stocks"
    delim = vbTab
    fn = Dir(myDir & "\*.csv")
    
    Do While fn <> ""
        ff = FreeFile
        Open myDir & "\" & fn For Input As #ff
         
        Do While Not EOF(ff)
            Line Input #ff, txt
            x = Split(txt, delim)
            If Not flg Then
                n = n + 1
              Cells(n, 1) = fn
                flg = True
            End If
            If UBound(x) > 0 Then
                n = n + 1
               Cells(n, 2) = x(2)
                Cells(n, 3) = x(1)
            End If
        Loop
        Close #ff
        fn = Dir()
        flg = False
       
    Loop

Display More

jindon · Oct 9th 2015

Re: Add a column with file name while importing .csv file in vba

See if this is how you wanted (not tested)

Code

Sub Button3_Click()
    Sheet1.Cells.ClearContents
     
    Dim myDir As String, fn As String, ff As Integer, txt As String, flg As Boolean
    Dim delim As String, n As Long, b() As Variant, x As Variant, t As Integer, i As Long
     
    myDir = "C:\Users\ASUS\Documents\Basket Stocks"
    delim = vbTab
    
    i = 1
    
    fn = Dir(myDir & "\*.csv")
    Do While fn <> ""
         
        ReDim b(1 To Rows.Count, 1 To 2)
        ff = FreeFile
        Open myDir & "\" & fn For Input As #ff
         
         '   Line Input #ff, txt  'Ignore header line
         
        Do While Not EOF(ff)
            Line Input #ff, txt
            x = Split(txt, delim)
            If Not flg Then
                n = n + 1: b(n, 1) = fn
                flg = True
            End If
            If UBound(x) > 0 Then
                n = n + 1
                b(n, 1) = x(2)
                b(n, 2) = x(1)
            End If
        Loop
         
         
         '  Columns(1).NumberFormat = "@"   'text
         '  Columns(2).NumberFormat = "General"  'general
         '  Columns(3).NumberFormat = "0"      'number
         
         '  Columns(1).NumberFormat = "0.00"
         ' Columns(3).NumberFormat = "0.00"
         
         
         
         
        Close #ff
        ThisWorkbook.Sheets(1).Cells(i, 1).Resize(n, 2).Value = b
        i = i + n + 2
        n = 0
        fn = Dir()
        flg = False
         
    Loop
     
     
End Sub

Display More

Originally
http://www.ozgrid.com/forum/showthread.php?t=80643

giancarlo.g · Nov 29th 2021

Quote from pike

Re: Add a column with file name while importing .csv file in vba

hello,
Maybe just write straight to the worksheet

Code

myDir = "C:\Users\ASUS\Documents\Basket Stocks"
    delim = vbTab
    fn = Dir(myDir & "\*.csv")
    
    Do While fn <> ""
        ff = FreeFile
        Open myDir & "\" & fn For Input As #ff
         
        Do While Not EOF(ff)
            Line Input #ff, txt
            x = Split(txt, delim)
            If Not flg Then
                n = n + 1
              Cells(n, 1) = fn
                flg = True
            End If
            If UBound(x) > 0 Then
                n = n + 1
               Cells(n, 2) = x(2)
                Cells(n, 3) = x(1)
            End If
        Loop
        Close #ff
        fn = Dir()
        flg = False
       
    Loop

Display More

Hi, When running this code on my Scenario, I am getting "Sub or Function not defined" for the 'Cells' function (line 14). I'm assuming I need to specify the file. (I tried 'fn.Cells', 'x.Cells',) but still no joy. Quite new to VBA, can you help? Thank you!

**rory** · Nov 29th 2021

Please start your own thread, rather than hijacking someone else's.

Add a column with file name while importing .csv file in vba

Participate now!

Share