Results 1 to 12 of 12

Import all excel files from a folder, each into their own new table in access

  1. #1
    Grummm is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7

    Import all excel files from a folder, each into their own new table in access

    Hi All,

    I have been trying to find a solution to this for a while now without success, it seems i am trying to do something no-one else has wanted to do. I have a reasonable level of experience using access to design databases but i am self taught on the whole.

    A full description of the task will take a long time but the crux of what i cannot find a solution to is this; I want to import all the excel files in a folder called "IMPORTS", each into their own new table in access, with the table name the same as the filename of the excel file from which they were imported. This is a temporary step in a process and once the tables have performed their purpose will be deleted.I will explain the process a bit more below for context.



    The idea is i will be receiving up to a few thousand of these excel files, each will contain one column of up to a maximum of 200 numbers (whole integer). These numbers are the primary key for a table of courses in the database which i will then link to a personID in the database, using an update query before the table is to be deleted. Ideally this will be a one button update records code that runs for all the files, updates the records and then deletes the tables imported (leaving the excel files untouched for now, but once i have tested it i may add code to move the files to another folder). The excel files will all (hopefully if users follow the outlined procedure) be uniquely named and that name will be used to find their record in the database, i may have to add code to check that the user exists before updating records and ignore the delete table step if it doesn't, to allow me to add a new user.

    At this stage i am trying to prove a concept rather than make the final code in one go but the one thing i have had little luck with is the main question above.

    Any guidance would be much appreciated,

    Graham

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,792
    would have thought that was fairly straightforward, it is a common requirement to import all files from a directory, use the dir function - air code would be something like this

    Code:
    FPath=dir("C:\IMPORT\*.xlsx")
    while FPath <>""
        FName=    replace(mid(FPath,instrRev(FPath,"\"),".xlsx","")
        docmd.transferspreadsheet acImport, acSpreadsheetTypeExcel12Xml,FName,FPath,True
        filecopy FPath, ("C:\IMPORT\Archive\" & FName
        FPath=dir
    wend
    Kill replace(FPath,Fname & ".xlsx") & "*.xlsx"
    but would have thought better to just store in one table, including the filename using an adaptation of the above

    Code:
    FPath=dir("C:\IMPORT\*.xlsx")
    while FPath <>""
        tabldefs("tmpImport").fields("FName").defaultValue=replace(mid(FPath,instrRev(FPath,"\"),".xlsx","")
        docmd.transferspreadsheet acImport, acSpreadsheetTypeExcel12Xml,"tmpImport",FPath,True
        filecopy FPath, ("C:\IMPORT\Archive\" & FName
        Kill FPath
        FPath=dir
    wend

  3. #3
    Grummm is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Many thanks for the reply, I will definitely explore putting it all into one table, will save a lot of extra messing about tidying up after it has done the import. I am getting an error with the code in this line, it doesn't like "mid" saying wrong number of arguments or invalid property assignment?

    This code is sufficiently new to me that i am not able to work it out, sorry.

    I should also have stated that i am running Access 2016 if that makes any difference?

    The second line is how it appears in the code in my database (i had to close a bracket to accept the code, have i done this correctly?

    Many thanks again,

    Graham

    tabldefs("tmpImport").fields("FName").defaultValue =replace(mid(FPath,instrRev(FPath,""),".xlsx","")



    tabldefs("tmpImport").Fields("FName").DefaultValue = Replace(Mid(FPath, InStrRev(FPath, ""), ".xlsx", ""))

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,792
    look like it - my mistake

  5. #5
    Grummm is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    I really don't like to admit defeat but i am still having trouble with the code suggested. The section highlighted red (if i have done this right) appears to be the issue.


    Code:
    FPath = Dir("C:\IMPORTS\*.xlsx")
    
    
    While FPath <> ""
        tabldefs("tmpImport").Fields("FName").DefaultValue = Replace(Mid(FPath, InStrRev(FPath, "C:\IMPORTS\"), ".xlsx", ""))
    
        
    
    
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tmpImport", FPath, True
        FileCopy FPath, ("C:\IMPORTS\Archive\" & FName)
        Kill FPath
        FPath = Dir
    Wend
    Mid is being highlighted as having too many arguments. I have looked up the code for each section of this (Mid, Replace, InStrRev) and i can see what it is trying to do but i cannot understand why it is not liking it, could it be too many functions at once? or possibly that there will be more than one backslash in the string?

    If i understand it correctly(?) it should return whatever * represents, so if the file path (FPath) is C:\IMPORTS\somename.xlsx it should return "somename"?

    I tried replacing the code in red with simply "Test" and instead get an issue with TableDefs so i am definitely out of my depth.

    As i said this is very new code to me so i apologise in advance if i am missing something that should be obvious to me.

    Many thanks in advance,

    Graham

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,792
    break it down into the various component steps using debug.print

    Code:
    debug.print InStrRev(FPath, "\")
    - what does this give you?

    Code:
    debug.print Mid(FPath, InStrRev(FPath, "\"))
    - and this?

    and finally this?

    Code:
    debug.print Replace(Mid(FPath, InStrRev(FPath, "\")),".xlsx","")
    my guess is the second step is wrong - I think it should be
    Code:
    debug.print Mid(FPath, InStrRev(FPath, "\")+1)

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,792
    if you find that transferspreadsheet doesn't work as expected try using sql instead - assuming you are using later versions of .xlsx then the sql would be something like

    Code:
    sqlStr="INSERT INTO tmpImport (FName, CoursePK) SELECT '" & FName & "', CoursePK FROM [Sheet1$A] IN 'C:\IMPORTS\" & FName & ".xlsx'[Excel 12.0;HDR=Yes;IMEX=2;ACCDB=YES;]"
    currentdb.execute sqlStr
    - no need to set the default

    Or alternatively link to the spreadsheet (using transfer spreadsheet) and use similar sql to above to append to your tmp table.

    Note that constantly appending and deleting data/tables will lead to bloat. If this is truly a temporary table, create it in a temporary db, and then delete it when done.

  8. #8
    Grummm is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Quote Originally Posted by Ajax View Post
    break it down into the various component steps using debug.print

    I did just that, but i was not using debug.Print correctly which was not very helpful.

    Code:
    debug.print InStrRev(FPath, "\")
    - what does this give you? This returns "0"

    Code:
    debug.print Mid(FPath, InStrRev(FPath, "\"))
    - and this? "*.xlsx"
    and finally this?

    Code:
    debug.print Replace(Mid(FPath, InStrRev(FPath, "\")),".xlsx","")
    This returns "\*" now i see what is happening.

    my guess is the second step is wrong - I think it should be
    Code:
    debug.print Mid(FPath, InStrRev(FPath, "\")+1)
    That's great, thank you, this part of the code is now functioning perfectly. I now need to read up on TableDefs as that is causing a compile error (sub or function not defined), have to call it a day for now but will read up on it tomorrow and see if i can figure out what i should do next.

    Thank you again,

    Graham

  9. #9
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,792
    did just that, but i was not using debug.Print correctly which was not very helpful.

    Code:
    debug.print InStrRev(FPath, "")
    - what does this give you? This returns "0"
    your fpath should be something like C:\IMPORT\SpreadsheetName.xlsx

    there are at least two backslashes - so debug.print FPath and see what you get

  10. #10
    Grummm is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Quote Originally Posted by Ajax View Post
    your fpath should be something like C:\IMPORT\SpreadsheetName.xlsx

    there are at least two backslashes - so debug.print FPath and see what you get

    Hi Ajax, I was getting inconsistent behaviour from the code in debug.print yesterday, sometimes i got "0" for FPath and other times I go the right file path, now no matter what i try i am no longer getting the correct output at all. I fear i have a bug with my computer as other strange things have been occurring and this may be why, so there is no point me persisting with this until i get the computer looked at. Its incredibly frustrating as i thought i was getting somewhere with this yesterday.

    Thank you so much for taking the time to respond with your help and suggestions, i am not going to give in but until i have got rid of the other issues i am experiencing with my computer i cannot be sure that is not upsetting what i am trying to do (for info the issue is with the File Explorer freezing, crashing or sometimes opening a folder in a miniature flickering window i cannot do anything to).

    I am hopeful that is the answer as the suggestions you gave yesterday did work if only for a time. If i get a chance i will try it on another computer to see if that produces the same result.

    Will let you know how i get on. I fear i am being far too ambitious with the level of knowledge i have of databases and so this may be a reach too far for me until i have learned a bit more about vba in general, but time will tell.

    Kind regards,

    Graham

  11. #11
    Grummm is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Hi again,

    After a lot of searching online i have changed my approach and am now using the code below to perform my task. I realised it more sensible to have the data in a better layout in the source excel files i will be generating rather than try to burden the task with code too much.

    Code:
    Option Compare DatabaseOption Explicit
    
    
    
    
    Public Function Importer3()
    
    
    Dim MyDb As DAO.Database
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number
    Dim Path As String 'FilestrPath
    Dim filename As String 'Filename
    Dim strSQL As String
    
    
     
        Path = Application.CurrentProject.Path & "\IMPORTS\"
        Set MyDb = CurrentDb
        
        
        
        DoCmd.SetWarnings False
     
      
        strFile = Dir(Path & "*.xlsx")
        While strFile <> ""
            
            intFile = intFile + 1
            ReDim Preserve strFileList(1 To intFile)
            strFileList(intFile) = strFile
            strFile = Dir()
        Wend
    
    
        If intFile = 0 Then
            MsgBox "No files found"
            Exit Function
        End If
     
       
        For intFile = 1 To UBound(strFileList)
           filename = Path & strFileList(intFile)
           
           
          
           DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Imports", filename, True
     
        Next intFile
      DoCmd.SetWarnings True
      
    Set MyDb = Nothing
    
    
      
    
    
    End Function
    This does the trick nicely (though i am concerned that filename is being specified in two strings but each used differently?)

    My problem is the code is seeing a formula in excel even though the output from the formula is nothing (not zero) and then i get paste errors (non conversion) on all the blank lines that the formula is outputting as nothing.

    Have i done something really silly in the setting up of the code or is this something more complex?

    EDIT, i hd to correct an error , i was trying to improve the code and put a set in which broke it, the code is now correct and performs as described above, sorry.

  12. #12
    Grummm is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    The code is fine, it's a problem with excel, the source data is linked to another excel file and that link is being broken. I still cant understand how access vba is seeing these errors when they are invisible, even copy paste special as values results in the errors but if i delete all the rows in the file with no data in (where the formulas were) the errors disappear.

    Thanks for all the guidance.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import 4 Different Excel Files from One Folder
    By Chaser in forum Import/Export Data
    Replies: 2
    Last Post: 10-20-2017, 09:17 AM
  2. Replies: 2
    Last Post: 12-04-2015, 07:30 AM
  3. Import multiple Excel Files into 1 access table
    By jurbin in forum Import/Export Data
    Replies: 1
    Last Post: 05-15-2015, 12:45 PM
  4. Replies: 1
    Last Post: 03-25-2014, 07:54 PM
  5. Replies: 1
    Last Post: 06-27-2012, 06:39 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums