How to import 1200+ excel files with multiple tabs where each tab goes into multiple tables? RRS feed

  • Question

  • I would like to import 1200+ xlsm files into Access for further processing. There is data in these files that need to be copied to the appropriate tables, currently a manual process. I suggested that we import them into staging tables and work from there. This should be a one time task as the process has since changed, but these (old) files still need to be imported.

    Each xlsm files has one or more tabs with data on them, plus a final tab that can be ignored. Each tab has three separate sections. I will explain in detail to give the idea:

    The first section has the main record spread over a few rows but in distinct columns. Specifically, the date is in row 8, merged columns H,I,J. Name is in row 9, merge of B,C. Home / contact is in row 9, F. Doctor is in 11, merged columns B,C. This entire section creates one record to which the other two parts are associated.

    The second part is a table "on its side," as the rows are what would normally be database columns. 5 columns that might be filled in. For any column that is filled in, all its rows must have data. The data is in rows 15-22, with merged columns A,B acting as the row name (what would be a database column name). Of the five columns that can be filled in, the first always is, the others are optional, but in order, that is, 3 cannot be filled in unless 2 is, and so on. The first column (the one that must be filled in) is a merge of C,D; 2 is E; 3 is F; 4 is G; and 5 is a merge of H,I,J,K,L.

    The third part is associated with the second part, as a many-to-many relationship. It has 6 rows, not all of which are filled in. As before, the first row must be filled in, but the third will only be filled in if the second is, and so on. I have been told that "a few" (no idea how many) have a 7th row added, in which case all 7 are filled in.

    The section has 5 columns that describe the item, and then a second 5 that associate it with the second part (explained above). That is, the first of the second 5 columns has the quantity of this item for the first column in the second part, the second has the quantity of this item for the second column in the second part, and so on. The first five columns are the data: Name (this is data, not a column or row name) merge of columns A,B,C; Quantity in D, Size in E, Item number in F, Code in G. The five columns that associate it with the second part are H,I,J,K,L.

    I'm certain this is all confusing, i just want to give an idea of what i am dealing with. My idea is to import it as three staging tables into Access, specifically made for this purpose, and then deal with the data there. Unless importing it as one generic table is better and only then trifurcating it?

    In any case, this must be done for over 1200 files, some with multiple tabs. Can someone help me with an idea of how to approach this? Wizard, code, whatever. It does not matter as long as it works; it's a one-time task.

    I am also trying/playing with the idea of creating the staging tables via the import wizard: External Data->Excel, but when i point it to the directory with the xlsm files, it will not list them. It seems to only want other file types. How do i import an xlsm file?

    Thursday, July 11, 2019 5:17 PM

All replies

  •  Although Excel seems fixated on .xlsx, if you use the file filter *.xlsm, Access will list the files.

    As you’ve outlined your procedure, you have a good head start.

    I’d tackle this with VBA, perhaps use an array or two.

    Strapped for time at the moment.

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, July 12, 2019 9:38 PM
  • Thanx, i wish i knew that before. In the end (which is actually just the beginning) i used vb (not vba) to import the files. After installing vsto, including it in the project, and importing it, i was able to use interop.excel to use excel objects easily. Specifically, for each on all the files in the directory, for each _workbook in workbooks, for each sheet in _workbook, then did the logic to get what i wanted. It seems to work now, big test on Monday, and then i can move on to step 2.

    Friday, July 12, 2019 10:11 PM
  • Cool!

    It may have been easier (now I tell you) in the VBE: click on Tools | References and check the “Microsoft Office Excel nn.0 Object Library“ box. There’s a  ton of stuff in there.

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, July 12, 2019 10:44 PM
  • It's not in there by default. You have to go to the Visual Studio Installer and add VSTO. Took me forever to figure that one out.
    Friday, July 12, 2019 10:47 PM
  • How is this going?

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, July 17, 2019 7:50 PM
  • All the files have been imported to the three staging tables. It only took a few hours.
    Wednesday, July 17, 2019 7:53 PM