Importing Excel Files (1 Viewer)

tmyers

Member
Local time
Today, 18:49
Joined
Sep 8, 2020
Messages
349
In my application, I am getting ready to start working on the portion that deals with my vendors. Each vendor we use generally sends us their quote as a PDF along with the Excel file. Unfortunately, each vendors format for their given files are different than the others. Only one has a format that could be directly imported with no problem.

Now my question begins. Based on the fact that each vendors excel/pdf format is different, I assume the best course of action would be to make a table for each vendor and make it match their file. The next issues arises due to the fact the several of them have all of their company info as headers and such. Is there a way during the import process (that I eventually want to make happen via clicking a button and selecting the file), to start at certain lines within the excel document? Say vendor A has company info and headers from line 1 to 28, nothing until line 32, then the actual data I want to important starts at 38. In the 10 years I have done this, the vendors file has never changed format wise.

Is there a way to do this? Or are PDF's possibly easier to work with? Although I highly doubt that one. One of the main things my boss wanted (almost demanded) was the ability to import this data, as it is what takes up a large chunk of our time, as manually putting all it in can be very time consuming.

Also, there are essentially only 5 fields from each that I need to grab. Designation, quantity, manufacturer, product code, unit price. What order those are in on their file varies from vendor to vendor however.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
13,186
Hi. I think importing the data from PDF is possible but harder than using Excel. You can use Excel Automation to grab specific areas of a spreadsheet.

One approach is to create a separate temporary table for each vendor format to import the data into and then import the data from the temp table into your products table.

If you're saying people are manually entering each product into the database, you can dramatically reduce the time by simply modifying the spreadsheet first before importing it into Access.
 

tmyers

Member
Local time
Today, 18:49
Joined
Sep 8, 2020
Messages
349
Since each vendors sheet stays the same, I had thought about making a macro that goes through the file and reformats it to make importing easier.

As far as temporary tables go, are you referring to where a table is created, data put into it, then a query moves the data to other tables? If so, I have never done such a thing (yet). I have read about it, but thought it seemed pretty complicated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
13,186
Since each vendors sheet stays the same, I had thought about making a macro that goes through the file and reformats it to make importing easier.

As far as temporary tables go, are you referring to where a table is created, data put into it, then a query moves the data to other tables? If so, I have never done such a thing (yet). I have read about it, but thought it seemed pretty complicated.
Re: Temp Tables

Yes, the temp table structure matches the spreadsheet columns to make the import process easier. You then use a query to move the new data into your actual table with the correct structure.
 

tmyers

Member
Local time
Today, 18:49
Joined
Sep 8, 2020
Messages
349
Got it. I will play around with it and see if I can figure it out. If I come across any other questions, Ill get back to you!
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 17:49
Joined
Feb 28, 2001
Messages
18,329
I'm going to chime in here to support theDBguy's statements. There are many ways to "skin this cat" but you can do an Import Specification and then save it, where you put specific columns into a temporary table and then use queries to delete useless rows. You have to experiment a bit to get it right, but once you do, that time-saving advantage of automation starts in play.

The other way to do this, just so you will know it CAN be done, is to write VBA code to do the import as a subroutine that creates an Excel Application object to open the spreadsheet and pick it apart one row at a time, getting whatever you want and discarding the rest. You would open the sheet, open a recordset, and then scan through the sheet. When you find something you wanted to keep, you would do an .AddNew to the recordset, fill in the fields, and .Update the recordset. Loop through the spreadsheet until you reach its end. I'm not actually saying you should do this, but this is what you would have to do if you were planning to import the sheet directly.
 

tmyers

Member
Local time
Today, 18:49
Joined
Sep 8, 2020
Messages
349
That is what I am currently experimenting with. Playing with seeing how a "raw" vendor file gets imported, vs taking a quick second to doctor it up. So far, several are unable to be imported without tweaking, as they contain images that need to be removed (company logos and such).

I like the VBA option, but man does that coding sound complicated. I want to try the easier way first :)
 

tmyers

Member
Local time
Today, 18:49
Joined
Sep 8, 2020
Messages
349
I have run into a question with this. I made a temp table and saved my import steps. I then made an append query to move only the fields relevant, but I also need to update an ID field when they move to their new "home" so I can tie them to a specific job. Would that require another temp table? Import the messy data, move relevant data to another temp table, update with correct jobid, then append one last time to final table?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
13,186
I have run into a question with this. I made a temp table and saved my import steps. I then made an append query to move only the fields relevant, but I also need to update an ID field when they move to their new "home" so I can tie them to a specific job. Would that require another temp table? Import the messy data, move relevant data to another temp table, update with correct jobid, then append one last time to final table?
Hi. Not sure I completely understand that, but maybe you could add the ID field in the temp table. After you import the data, you can use an UPDATE query to assign those IDs in the temp table. After that, you should be able to APPEND the new data, with the ID, into your actual table.

Hope that helps...
 

tmyers

Member
Local time
Today, 18:49
Joined
Sep 8, 2020
Messages
349
That makes more sense. For some reason I thought any field I added to the temp table would be removed/overwritten when the saved import is run again.
 

tmyers

Member
Local time
Today, 18:49
Joined
Sep 8, 2020
Messages
349
Sorry to keep bugging, but how can I change the file path? I want to have my button do the saved import, but let the user select the file to run those steps for. Is that possible? Or do saved imports not work that way?

I am currently working with:

DoCmd.RunSavedImportExport "myImportnamehere"

That works, but it will obviously only run on that same file over and over. At least if it works the way I think it does.
 

tmyers

Member
Local time
Today, 18:49
Joined
Sep 8, 2020
Messages
349
After move diving around the web, I once again figured it out. Really need to look more before posting.

Solution was:
Code:
Private Sub Command56_Click()

 Dim f    As Object
 Set f = Application.FileDialog(3)
    f.AllowMultiSelect = True
    f.Show
MsgBox "file choosen = " & f.SelectedItems.Count

DoCmd.RunSavedImportExport "savedimportname"

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
13,186
After move diving around the web, I once again figured it out. Really need to look more before posting.

Solution was:
Code:
Private Sub Command56_Click()

Dim f    As Object
Set f = Application.FileDialog(3)
    f.AllowMultiSelect = True
    f.Show
MsgBox "file choosen = " & f.SelectedItems.Count

DoCmd.RunSavedImportExport "savedimportname"

End Sub
Actually, I'm surprised that worked, but glad to hear it does. Just to be safe, try deleting the original file from the original folder and try again with a different or same file in a different folder. Cheers!
 

tmyers

Member
Local time
Today, 18:49
Joined
Sep 8, 2020
Messages
349
Actually, after doing your suggestion it didn't. It imports the same file, regardless of the selection.
What did I do wrong?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
13,186
Actually, after doing your suggestion it didn't. It imports the same file, regardless of the selection.
What did I do wrong?
Okay, I thought so. You didn't do anything wrong. I believe it's the limitation of the SavedImport. The path for the file to import is fixed. The only way to change it is to use code and modify the saved import spec.

What I would suggest is to not use a saved import spec. Instead, we could try using the TransferSpreadsheet method to get the data from Excel into Access.
 

tmyers

Member
Local time
Today, 18:49
Joined
Sep 8, 2020
Messages
349

tmyers

Member
Local time
Today, 18:49
Joined
Sep 8, 2020
Messages
349
Okay, I thought so. You didn't do anything wrong. I believe it's the limitation of the SavedImport. The path for the file to import is fixed. The only way to change it is to use code and modify the saved import spec.

What I would suggest is to not use a saved import spec. Instead, we could try using the TransferSpreadsheet method to get the data from Excel into Access.
Could you possibly give an example of how to use the DoCmd.Transferspreadsheet? I am reading the docs on it now, but I am finding it a little confusing.
 

Isaac

Lifelong Learner
Local time
Today, 15:49
Joined
Mar 14, 2017
Messages
2,701
I actually thought about that, but I could very easily see my users forgetting to do that. The less I have to rely on them doing every step correctly, the better lol.
I think what Gasman might have meant is, YOU do that, in your code...then run your saved import spec.
 

Users who are viewing this thread

Top Bottom