Is there a way to download and import multiple .tsv files into excel or access?

Question

I am trying to create a database from SEC filings. The filings come in .tsv files and are structured in folders by year and quarter. Ever folder contains about 20 .tsv files that I would like to import into either Excel or Access (whichever is more feasible). Every file represents one table.

Instead of importing every single file by itself, is there a way to import them all at the same time? Or at least all the files of one folder at one time?

Is there an already implemented function or do I need to use Python, Excel Macro, etc.?

Of course - VBA can loop through files in a folder. Common topic. Do research, attempt code, and when you have specific issue post question. — June7, May 13 '22 at 18:07
Since data is tab delimited instead of comma separated, DoCmd.TransferText might not work. So options would be to set a link to each table and import with tab defined as separator or use text file manipulation code. Regardless of import method, get the file looping figured out first. There's a couple ways to do that. One uses FileSystemObject and another uses Dir() https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba — June7, May 13 '22 at 18:19
And to loop folder and subfolders, review http://allenbrowne.com/ser-59.html — June7, May 13 '22 at 18:48

score 0 · Accepted Answer · answered May 13 '22 at 18:24

Welcome to SO! In general, this forum is for answering specific questions based on what you have tried. It is recommended to study on topics and try first.

This is not the complete solution, your question is not specific enough.

But you should be able to build on this sample. Pretty much just supply the file loop method, and keep track of where each of the files go in your sheet.

NOTE: there are of course many other ways to do the same. This is showing way to do it automated from within Excel.

Datafile sample:  (assume TAB separated)
--------------------
H1  H2  H3
T1  T2  T3
F1  F2  F3

This code reads the file and imports so that C1R1 is current cell.

Option Explicit

'
' sub to do import.
' Make a loop here using list of files
'
Sub TestImport()
    Call ImportTextFile("c:\Temp\excelimport.txt", vbTab, ActiveCell)
End Sub

'
' function to import
'
Public Sub ImportTextFile(strFileName As String, strSeparator As String, rngTgt As Range)
    Dim strWholeLine As String
    Dim rw As Long, col As Long
    Dim i As Long, j As Long, ary() As String, a As Variant

    Dim wks As Worksheet
    Set wks = rngTgt.Parent
    Open strFileName For Input Access Read As #1
    rw = rngTgt.Row
    col = rngTgt.Column

  
  i = rw
  Do While Not EOF(1)
     Line Input #1, strWholeLine
     ary = Split(strWholeLine, strSeparator)
     
     j = col
     For Each a In ary
        Cells(i, j).Value = a
        j = j + 1
     Next a
     i = i + 1
  Loop
  Close 1
    Set wks = Nothing
End Sub

Thank you! I realize I have a lot to learn and figure out still to ask more specific questions. I appreciate your answer! — ManuelCukRia, May 13 '22 at 18:54

Is there a way to download and import multiple .tsv files into excel or access?

1 Answers1