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

[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.

 

[bookrackonteur]

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.

hi, yes, I saw you comment on a similar post and downloaded that to take a look at.
Publisher and Author arent normalized right now because the data we get in is so varied, we have a separate report we run to tidy it up, but this may help fix that problem if we put the garbage into a related table and can clean from there. So this might inspire me to fix that set of problems.

 

[Gasman]

Look at using a side end db for that.
Importing data and deleting bloats Access like it is going out of fashion.
At least Foxpro had a Zap command.

 

[bookrackonteur]

Look at using a side end db for that.
Importing data and deleting bloats Access like it is going out of fashion.
At least Foxpro had a Zap command.

are you saying to maybe import all this stuff to a different DB, clean it up and then import the nice clean table? that is intriguing.

 

[Gasman]

are you saying to maybe import all this stuff to a different DB, clean it up and then import the nice clean table? that is intriguing.

Yes. That keeps your main DB at a reasonable size and limits the need for compact.
You start off with a fresh empty side end each time.
Wish I had know about it when I worked for one of the large UK banks, as I was importing data every day and having to clear out the previous days, as everything had to come from the a particular bank department.

This would be the same bank department that imported the same data twice on one occasion and were unable to work out which version to remove? The number of times users reported duplicate payments got beyond a joke.

 

[RonPaii]

With spreadsheets I only use import spec on sheets I control. Those coming outside, I allow the user to select the columns to import.

                For ColumnCount = 1 To 20
                    ' Pull each column header to var
                    ColumnHeader _
                        = sNZ(Trim$(oXLSheet.Cells(LineCount + 1, _
                                                   ColumnCount).Value), _
                             vbNullString)
                    If ColumnHeader <> vbNullString Then
                        NextImportLine = Replace$( _
                                            Replace$( _
                                            Replace$( _
                                            Replace$( _
                                            Replace$(ColumnHeader, _
                                                    DOUBLEQUOTE, vbNullString), _
                                                    SINGLEQUOTE, vbNullString), _
                                                    COMMA, vbNullString), _
                                                    SEMICOLON, vbNullString), _
                                                    vbCrLf, vbNullString)
                        If NextImportLine <> vbNullString Then
                            SelectList = SelectList & _
                                         ColumnCount & COMMA & _
                                         NextImportLine & COMMA
                        End If
                    Else
                        ' Break out of loop on 1st empty column heading
                        If SelectList = vbNullString Then
                            pai1_Msg_Box "No column headings found in file: " & _
                                         PathToImportFile, , _
                                         "Cannot import"
                        End If
                        Exit For
                    End If
                Next

Get the column(s) using a list box, in this case a part number and price column.
Then import into a temp table for post processing.

Reuse query by supplying new parameter values.

            Set qryAppendPartPrice _
                =oDatabase.CreateQueryDef(vbNullString, _
                                    "PARAMETERS Part Text ( 255 ), vendorPrice IEEEDouble; " & _
                                    "INSERT INTO feImportVendPrice ( PAIPN, Price ) " & _
                                    "VALUES ([Part], [vendorPrice]);")

               If PartNumberColumn <> -1 Then
                    CurrentRow = 1
                    ' Change from 1000 to number of rows in sheet 
                    Do Until CurrentRow _
                           = oXLSheet.Cells(oXLSheet.Cells.Rows.Count, "A").End(xlUp).Row + 1
                        ImportPart = Left$(Trim$(Replace$( _
                                                 Replace$( _
                                                 Replace$( _
                                                 Replace$( _
                                                 Replace$(NZ(Trim$(oXLSheet.Cells(CurrentRow, _
                                                                                   PartNumberColumn).Value), _
                                                                                   vbNullString), _
                                                          DOUBLEQUOTE, vbNullString), _
                                                          SINGLEQUOTE, vbNullString), _
                                                          COMMA, vbNullString), _
                                                          SEMICOLON, vbNullString), _
                                                          vbCrLf, vbNullString)), 255)
                        If ImportPart <> vbNullString Then
                            qryAppendPartPrice.Parameters(0) = ImportPart
                            qryAppendPartPrice.Parameters(1) = NZ(Trim$(oXLSheet.Cells(CurrentRow, PriceColumn)), 0)
                            qryAppendPartPrice.Execute
                            LineCount = LineCount + 1
                        End If
                        CurrentRow = CurrentRow + 1
                    Loop
                End If
            End If