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

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

    Sunday, November 19, 2017 7:48 PM

Answers

  • 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)

    Sunday, November 19, 2017 8:27 PM

All replies

  • 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)

    Sunday, November 19, 2017 8:27 PM
  • What this forum needs, Hans, is a "Good answer!" button.

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

    Sunday, November 19, 2017 8:33 PM
  • There is one: do you see the "Propose as answer" link? Maybe after you sign in?

    -Tom. Microsoft Access MVP

    Sunday, November 19, 2017 8:53 PM
  • 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

    Sunday, November 19, 2017 8:56 PM
  • Thank you, Dirk! :)

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

    Sunday, November 19, 2017 10:08 PM
  • 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

    Monday, November 20, 2017 8:13 AM
  • 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)

    Monday, November 20, 2017 9:41 AM
  • 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 MSDNFSF@microsoft.com.

    Tuesday, November 21, 2017 1:44 AM
    Moderator