Imported XL data in different order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a procedure that scans a specific folder and imports *.xls files (over
60) into my database. The import works fine except that “sometimes†some of
the resulting tables are not in the same sequence as the original
spreadsheet. If I import them manually using the wizard, everything is ok but
as these sheets change frequently, I really do need the automated process to
work. I have tried adding them to tables that have autonumber keys defined
but this makes no difference. It’s not always the same spreadsheets – it
varies. Can anyone help please? I am using Access 2003 with SP2.

The import procedure is;-

With Application.FileSearch
.LookIn = strImportPath
.SearchSubFolders = False
.FileName = "*.xls"
If .Execute() > 0 Then
lngFiles = .FoundFiles.Count
For i = 1 To lngFiles
strFileName = ExtractFileName(.FoundFiles(i))
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
strFileName, (.FoundFiles(i)), False
Next i
End If
End With
 
The order of records in a table has no real meaning in a relational database.
It might seem like it or even act like it, but unless you have something to
sort on in a query, you can't depend on it. You can open a table and see
records in some order, but they might not be in the same order the next time
you open the table.

Now in your spreadsheets someone probably entered the data in a specific
order. When you reopen a spreadsheet you surely expect the data to be in the
same place. That's how spreadsheets work.

Now the problem becomes one of taking something that is in order
(spreadsheet) and dumping it into something that doesn't maintain order
(table). Is there any column or combination of columns in the spreadsheet
that maintains an order? For example if you sorted on that column(s) in the
spreadsheet, the data would stay where it should? If so is this data unique
enough to make a good candidate for a primary key or unique index in the
table? If so, life is good. Just sort on that unique index in queries and
your data is in order.

If not you might be able to link to the spreadsheets instead of importing
then use a query to append the records to a table in the sequence you wish.
In this case you would also want to add a field that contains the sort order.
An autonumber field might work; however, even autonumbers are not guaranteed
to be sequential.
 
Back
Top