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

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.?

CodePudding user response：

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