Getting data from differently formatted Excel sheets to a table Form/VBA/Import question (3 Viewers)

[bookrackonteur]

I wasn't sure where to post because I'm not sure I have the right concept for this.
We have a table with book data
ISBN, title, author, Pub, PubDate, StartDate(ie Fall25)

We import that data from a couple different places depending on how we ordered it, and of course every place sends their data in different formats, different columns different column headers etc. and if we're missing data other than ISBN or Title, it doesnt matter(its easy to manually enter, fix, or ignore)
Right now, I usually format the spreadsheet/csv that I get and then open my tblBookList, drag the columns in the table view to match the Excel and then Copy and do a Paste-Append which works, but is a nuisance and very easy to mess up (also easy to undo and redo, but...)

I can't figure out if the best option is to do an import with hardcoded import column headers and then make sure I get the data into a correctly or if there might be a way to do it with a form? My 'dream' is a form with text boxes where I can paste in 30 ISBNs, then a text box where I type in the 30 titles etc and VBA loops through and inserts.

Open to any thoughts - I may be missing something obvious, clever, or obscure.
-Mike
casual Access user with a modified Northwinds that has some great custom features, but also some stuff that real developers would cry over

 

[Gasman]

When I worked for a particular company, we would get their data in all sorts of formats.
We had to create an input program for each client to get the data into our system. That was COBOL.

You could create an import specification for each and then use a form to select which one.
I would probably have a table that held the company name and the specification and show it in a combo, then run the same code, just with a different spec name. After all that is what they are there for.

 

[The_Doc_Man]

I see that Gasman beat me to this.

The Import Specification (created and use manually on the External Data tab) is a feature you can design to link specific columns of a spreadsheet to specific fields of your table. There is no rule whatsoever that forbids having multiple Import Specifications, one for each sheet format. The trick to automating the import process is therefore to recognize which type of sheet you have, and use VBA to trigger the import with the right specification.

If there is a way to tell from the outside of the sheet, such as a particular naming convention or finding it in a particular folder, you can use that method. If you have to open the Excel file to "sample" it in order to determine the format, it isn't that hard to do. At most a little bit tedious.

When I was working with sheet imports, I looked at the column headers which were always in the first row and were reliable as to defining the type of sheet. It also gave me a warning when someone used the wrong kind of sheet.

 

[bookrackonteur]

Ok, this seems doable and sensible- there are 3 major sources, that have formats I am familiar with, and I can see what other formats I get.
thanks!
Also, if I recall, if I need to edit an import Spec, that's an unusual process because theyre stored in a hidden table? So if you do have any further tips on tweaking after I set it up, that's great. But either way, I can mess around with it.

 

[Gasman]

Have a look here

 

[Ken Sheridan]

If you intend to process the imported data in Access you might want to think about decomposing the imported data into correctly normalized related tables. This can be done with a set of 'append' queries executed in a specific order. In your case you'd probably need different sets of queries for each format in which you receive the data.

You might like to take a look at DecomposerDemo.zip in my Dropbox public databases folder at:

https://www.dropbox.com/scl/fo/0sci...cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

This little demo file illustrates a series of 'append' queries for importing spreadsheet data. Each stage in the operation is briefly explained as you step through the demo.

 

[Pat Hartman]

We had to create an input program for each client to get the data into our system. That was COBOL.

My version of COBOL had a redefines clause But I probably would have used separate programs also for neatness. Luckily, Access gives us the equivalent of "redefines". You use an import spec for each different format. Or if you like writing code, you can write code to automate Excel to reformat all the spreadsheets to one format but you still need to know the format of the file you are reading.