Results 1 to 4 of 4
  1. #1
    Starchaser is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    1

    link multiple excel files to one DB

    Hi everybody,

    I have a few "newbie" questions about access.

    Situation :

    I have about 100 different excel files, which all have the same layout (all the headers are the same). They are all datasheets from different suppliers (productcodes, buying prices, description,....)
    All of those files are updated frequently.

    Every day I receive stocklists which I have to match with the datasheets and update the data. The stocklist is a list of articles from all different suppliers.
    Till now I always compared the stocklist with every excel file with the "vlookup" formula. You can imagine what time it takes!

    I can't merge all the excel files, because it's a lot of data and I've noticed that the vlookup formula isn't reliable if you have a file larger than 30000 rows.
    Secondly, as I mentioned the seperated datasheets are updated minimum every month, so that means I have to match the big merged file also everytime there's an update for one of those suppliers.

    So here are my questions :

    1. Is access the right tool to make my work easier? Can it handle better of with 100 different excel files?
    2. I've found out that you can link all the excel sheets in one access file.
    But how can you compare 1 stocklist with all the different sheets? To match with 1 list I think I understand, but when all the files are linked, will access look in all different files?
    I've read about the code "Union". But in the example I've found you should write a 'little' code for each excel sheet you want to link. This means I have to write about 100 times the same code?
    This is the example :
    - create a query
    Select*
    From (name of the first excel sheet)
    Union all




    and this for all 100 sheets....

    Or is there an other way?

    I hope the situation is a little bit clear.

    Anyway allready many thanks for looking at my thread.

    grtz

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Depends on what the desired output would be (and I can't recall what the limit is for Union query). Your best bet might be to link to the spreadsheets as tables (I would probably try to find code for this rather than manually link 100 separate sheets that might be in 100 separate workbooks). Then create one table from one linked sheet, then use code to run one append sql that loops through all the linked tables, appending to the master table you created. In future, you would want to append only new records to this table and in the same process, update that which was changed. However, given that your source data is in sheets, my guess is that it isn't structured correctly for a database anyway. It begs the question, if you want to manage the data in Access, why bother with Excel at all? Most of the time, they are incompatible.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    something weird happened to the response - reposted

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    But how can you compare 1 stocklist with all the different sheets?
    would need to know more, but if you have a 100 excel files, you will need to take an action 100 times

    Access is not excel and you need to apply database principles to get the benefits. Try to use it as you would excel and you will not succeed. In Any event a union query to linked excel files will be slow due to lack of indexing

    If all your files are the same, then I would merge them into one access table - comparison then becomes straight forward

    To do this you need to create 100 insert queries to insert the data into one table. How you do this is where I don't know your situation. The easiest way is probably to use vba to loop through a list of the filenames. No need to link, just use a query something like this

    Code:
    INSERT INTO myTable
    SELECT *
    FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\ImportFolder\fileName.XLSX'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes])  AS XL
    In vba, you can build the sql string and change the file name

    Code:
    dim sqlStr as string
    
    for each filename
        sqlStr="INSERT INTO myTable SELECT * FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\ImportFolder\" & fileName & ".XLSX'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes])  AS XL"
        currentdb.execute sqlstr
    next filename
    as I said, don't know enough - perhaps your files are in different folders, perhaps you don't get all of them every day, perhaps each day they have different names (i.e. to indicate the date)

    other things I don't know - does your file include the supplier name or ID on each row? do your suppliers supply the same products or are they all unique to each supplier? Do you need to record a history of changes? Is this a one time comparison and then your throw the table away? or do you get the same products each time and, once the table is populated, just update changes when they occur? All of these would change the above suggestion

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

Similar Threads

  1. Replies: 1
    Last Post: 04-10-2019, 11:39 AM
  2. Multiple Excel Files
    By DukeRollo in forum Import/Export Data
    Replies: 5
    Last Post: 11-30-2017, 06:41 PM
  3. Can I link 2 excel files into one Access table
    By Tibbs in forum Import/Export Data
    Replies: 3
    Last Post: 10-11-2017, 08:08 AM
  4. Linking multiple Excel files - PLEASE help!
    By studor63 in forum Import/Export Data
    Replies: 4
    Last Post: 09-11-2012, 01:24 PM
  5. Replies: 4
    Last Post: 06-14-2011, 07:19 PM

Posting Permissions

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