Getting data from differently formatted Excel sheets to a table Form/VBA/Import question

[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

 

[Gasman]

And sNZ() is your special NZ() function?

 

[CJ_London]

, I usually format the spreadsheet/csv

If you have multiple known formats in terms of layout and column headings you could use excel automation from access to get them in the right order

I have a similar issue with bank statements from different banks in csv and xls files. I have a template table that defines where to find each column for each of the destination tables and any conversions required. These are then used in an excel macro generated in access to update the excel sheet and save as a .csv. The import then becomes a simple insert query

The template table looks like this with example data -

BankFK	FileID	AcctNoKey	DateKey	DelRows	ColIDs	MoneyInCalc	MoneyOutCalc	Sort
1	Statements	fileName	to	1:3,5:5	ABDFGHI
2	Transaction Report	Account Number :	end date :	1:8	ABCDEGH	=IF(RC[2]>0, RC[2], "")	=IF(RC[1]<0, -RC[1], "")

tblProcess

[td width="14.4986%"]

Ascending -latest at bottom​

[/td]​

[td width="14.4986%"]

Descending - latest at top​

[/td]​

FileID - the first part of the file name to import
AccNoKey - where to find the account number - sometimes in the file name, sometimes in the data
DateKey - where to find the date of the report
DelRows - some files have additional rows of data (such as summary values, the banks name for the report, etc) that can be deleted
ColIDs - perhaps a bit obtuse, but these relate to which column the data can be found - the first char is for the first field in the destination table, the second, the second field etc
MoneyIn, MoneyOut calcs - the destination has these two columns, some bank statements have a single 'amt' columns with positives and negatives
Sort- some banks show latest transaction at the top, others at the bottom

you may have completely different requirements but it illustrates the principle - you probably don't need the last three columns for example

these values are then applied using VBA to modify the excel file

Form the users perspective, to set up a template for another bank, all they need to do is open the file in excel and complete the columns based on what they see.

 

[Pat Hartman]

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?

Amusing when it happens to someone else. When I was faced with importing bank files every month for reconciliation, I realized that importing the same file multiple times or missing a file could be catastrophic so I created a log table to track each file as it was imported. A logID was assigned and recorded in the append query so that each row was tagged with the ID of the file it came from so that a file could be backed out if necessary. Also having the log table prevented both duplicate entries and missing entries. Of course nothing is perfect and we had to rely on the bank's naming system for their file names which were always correct.

 

[CJ_London]

It is easy enough to avoid duplication of entries- left join the import table/query to the destination table and only import those records where the destination table is null

Typically join on date, transaction amount and detail and the balance

Similarly a simple query to compare opening and closing balances with the sum of amounts can verify the completeness of the import file and after import, the destination table

 

[Pat Hartman]

It is easy enough to avoid duplication of entries- left join the import table/query to the destination table and only import those records where the destination table is null

That assumes there is a unique ID. Bank statements don't have one. Only checks can be matched that way. Deposits and withdrawals don't have unique matching ID's in both systems.