Unable to import 3700 .csv files into one local table (two part question)

Question

I feel like I am missing a few basic things so I hope the community can help me.

I want to import all .csv files into one access table for easy import into my local SQL database*.  I have about 240 files per folder and about 120 folders in total.  I found this section of code below on this forum that has given me some problems(modified for my needs but should still be searchable).

1)  This seems to work for me only when the .csv workbook is open.  If the workbook is closed I get run-time error '3274'

2)  Each table has the first row as text only, and rows 2 to N (N is not constant) as other data types.  When I tried to do an import for all data in the table I would get errors unless I create the table in advance as a text field.

My attempts at working around the problem have failed, and I don't trust myself to copy 3.7K .csv files by hand with 100% accuracy.  Any help is appreciated.

Cheers!

Wised

Software Versions:

Microsoft Excel 2013 64-bit

Microsoft Access 2013 64-bit

Microsoft Visual Basic for Applications 7.1

Function Impo_allExcel()
Dim myfile
Dim mypath

mypath = "C:\Users\wised\Desktop\Investment Data\EODData\AMEX Historical\AMEX_1997\"
ChDir (mypath)
myfile = Dir(mypath)
Do While myfile <> ""
  If myfile Like "*.csv" Then
     'this will import ALL the excel files
     '(one at a time, but automatically) in this folder.
     ' Make sure that's what you want.
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "two", mypath & myfile
  End If
  myfile = Dir()
Loop
End Function

*I am not opposed to using the SQL compiler to import all data from the .csv files into one table but I don't know where to begin.

Answer 1

A .csv file is a text file (even though it is opened in Excel by default), so you should use DoCmd.TransferText instead of DoCmd.TransferSpreadsheet. You will need an import specification for this. You have to create it only once:

• On the Data tab of the ribbon, in the Import group, click Text File.
• Select any of the .csv files, it doesn't matter which one (I assume they all have the same structure, otherwise this won't work at all).
• Click OK to start the Import Text Wizard.
• Go through all the steps, setting the field types etc., but do NOT click Finish.
• In the last step (where Next > is disabled), click Advanced...
• Review the settings.
• If they are OK, click Save As...
• Give the specification a name; in the sample code below I used MySpec, but you're free to use another name.
• Click OK, then click OK again.
• You can cancel the Import Text Wizard now, or click Finish to test that everything works OK.

Here is code that will loop through a folder and all its subfolders, and import all .csv files that it encounters:

Sub ImportCSVFilesFromFolderAndSubFolders()
    Dim flpath As String
    Dim macroname As String
    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        If .Show Then
            flpath = .SelectedItems(1)
        Else
            Beep
            Exit Sub
        End If
    End With
    Call ProcessFolder(flpath)
End Sub

Sub ProcessFolder(ByVal p As String)
    Dim fso As Object
    Dim fld As Object
    Dim fl As String
    Dim sfl As Object
    Set fso = CreateObject(Class:="Scripting.FileSystemObject")
    Set fld = fso.GetFolder(p)
    If Right(p, 1) <> "\" Then p = p & "\"
    ' Process files
    fl = Dir(p & "*.csv")
    Do While fl <> ""
        DoCmd.TransferText _
            TransferType:=acImportDelim, _
            SpecificationName:="MySpec", _
            TableName:="two", _
            FileName:=p & fl, _
            HasFieldNames:=False
        DoEvents
        fl = Dir
    Loop
    ' Process subfolders
    For Each sfl In fld.SubFolders
        Call ProcessFolder(sfl.Path)
    Next sfl
    Set sfl = Nothing
    Set fld = Nothing
    Set fso = Nothing
End Sub

If you think the code gets stuck, you can press Ctrl+Break.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

What this forum needs, Hans, is a "Good answer!" button.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 3

There is one: do you see the "Propose as answer" link? Maybe after you sign in?

---

-Tom. Microsoft Access MVP

Answer 4

There is one: do you see the "Propose as answer" link? Maybe after you sign in?

Sure, but it doesn't convey the same appreciation. :-)

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

Thank you, Dirk! :)

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 6

This works perfectly; had I known you would have responded so quickly I would have checked earlier!

As I work with VBA, can I expect that over 95% (or, 100%) of all capabilities in the Microsoft Office Product currently used can be programmed with VBA, and all I have to do is look?

Again appreciate your help!

Cheers;

Wised

Answer 7

It varies from application to application, but in general: yes, almost anything that is possible in Office applications can be done in VBA.

If you don't know how to do something, you can search the internet, or ask questions in a forum such as this one.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 8

Hello Wised,

I suggest you mark helpful post as answer to close this thread as your issue has been resolved. If you have any new issues, please feel free to post on this forum. 

You could visit links below to get more information about Access programming.

Introduction to Access programming

Access VBA reference

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].