Import files sequentially (1 Viewer)

jmsm

New member
Local time
Today, 11:12
Joined
Oct 1, 2020
Messages
12
Hello.

I have a bunch of files that I need to import sequentially. The files are generated by an order management system, and the format is like this: store_YYYYMMDDHH. I can't use this date because the number of characters are not static:

store_20209111- this can be the file of 01/09/2020 at 11h or 11/09/2020 at 1h.

So, my idea is to change manually the files with a _number like:

store_date_1
store_date_2

So, I want to import these files sequentially: first the store_date_1 and then the store_date_2 and so on.

My code is like these:

For Each ofile In ofolder.Files


DoCmd.SetWarnings False

If VBA.InStr(1, ofile.Name, "~") = 0 Then
If ofile.Type Like "*" & oRecon_otype & " *" Then
'
Debug.Print ofile.Path
ofilepath = ofile.Path
oExFileName = ofile.Name


MsgBox "File to open : " & ofile.Name
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, oTableName, ofilepath, True




i = i + 1

End If
End If
Next ofile

Exit Function


ExitDoor:
MsgBox Err.Number & " - " & Err.Description
End Function


For each file in the specific folder, import all. But I want a piece of code to force access to import the store_1 first, next the store_2 and so on.

Any ideas?

Thank you.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:12
Joined
Jan 23, 2006
Messages
15,361
How can you be sure the records are/would be in time sequence if you don't know exactly how to interpret the data?
Are there specifications for these records in the Order Management system?
Can you show more sample data-- there may be a pattern?
 

jmsm

New member
Local time
Today, 11:12
Joined
Oct 1, 2020
Messages
12
The date in file's name correspond to the extraction date from OMS.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:12
Joined
Jan 23, 2006
Messages
15,361
But, if you don't know how to interpret that date, you can only guess. Seems there are not 2 digits for each part of the Date/time format.
What are the ramifications of making the wrong guess? That is, how critical is a misinterpretation to your business?
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:12
Joined
Aug 11, 2003
Messages
11,696
why want to force access to import the one store first? Simply add the filename/storename to your target table running an update query after importing the file.
 

jmsm

New member
Local time
Today, 11:12
Joined
Oct 1, 2020
Messages
12
Because orders change status where are picked, billed and/or shipped. The same order can be in two different excel files because it was a status update. I created a query to delete the old records (based on order number) and add the ones present in the new excel file.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:12
Joined
Jan 23, 2006
Messages
15,361
Excel?
The same order can be in two different excel files because it was a status update.
I think it's time for a database solution rather than spreadsheets.
 

jmsm

New member
Local time
Today, 11:12
Joined
Oct 1, 2020
Messages
12
Excel? I think it's time for a database solution rather than spreadsheets.

I can't control that. I'm importing the excel spreadsheets into a access database in order to work the data.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:12
Joined
Jan 23, 2006
Messages
15,361
I misunderstood then --I thought you were working entirely with Excel. Perhaps you could overview in plain English:
-the overall process
-each of the excel file (content and purpose)
-your Access database table design(s) (and relationships)
and provide some sample(s) of the data.

If you need the date and time, and it's important to your business, then you should not have to guess what the data represents.
As has been suggested, you can add a field to an import to indicate date/time or whatever you need, but that may not apply in your case since the data is output by the OMS. You can still add your own field and populate it helps.
Good luck.
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:12
Joined
Aug 11, 2003
Messages
11,696
Because orders change status where are picked, billed and/or shipped. The same order can be in two different excel files because it was a status update. I created a query to delete the old records (based on order number) and add the ones present in the new excel file.
You typicaly dont delete records you mark them obsolete, depending on the update date and time.... the order you import your different files should not matter.
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:12
Joined
Aug 11, 2003
Messages
11,696
You can investigate the DIR function and the use of that over your current method.

Another alternative is use a table to store your filenames first, then open a query which can force the filename order and fetch the filenames from the query to import the files.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:12
Joined
Jan 23, 2006
Messages
15,361
The Picked, Billed and/or Shipped status seems to be transaction related. How are these identified/separated in the Excel files? I think we need to see some data that you are working with to give focused responses.

How do you interpret and process the data now when you can't be sure of the date involved?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:12
Joined
Jul 9, 2003
Messages
16,244
I have a bunch of files that I need to import sequentially.

I answered a similar question in 2008, see here:-


Might offer some inspiration...
 

Isaac

Lifelong Learner
Local time
Today, 04:12
Joined
Mar 14, 2017
Messages
8,738
@jmsm
Very recently I faced this exact same need. I need to load a screen with a list of files in a folder, and I wanted there to be a sequence to it. So that they loaded in order on the screen's listbox, which happened to be in Excel, but doesn't matter.
Of course, Scripting Filesystemobject (which it looks like you're using) doesn't guarantee you ANY particular 'order' to their 'for each file in folder.files' collection/loop. Neither does DIR I doubt.

Here is how I solved it:

- Declare an array
- Populate your files list - and I agree with whoever suggested DIR() to do so. It's 10,000 times faster. In my case I'd gone from about 45 seconds to load my screen (using for each scripting.filesystemobject.folder.file/files method), to about 1 second using DIR.
- Put the files list in the array
- SORT THE ARRAY

...Loop through the array in your vba code.

Populate an array with DIR() files in a folder:
Code:
Public Function GetFilesDir(ByVal sPath As String, _
    Optional ByVal sFilter As String) As String()

'dynamic array for names
Dim aFileNames() As String
ReDim aFileNames(0)

Dim sFile As String
Dim nCounter As Long

If Right(sPath, 1) <> "\" Then
    sPath = sPath & "\"
End If

If sFilter = "" Then
    sFilter = "*.*"
End If

'call with path "initializes" the dir function and returns the first file
sFile = Dir(sPath & sFilter)

'call it until there is no filename returned
Do While sFile <> ""

    'store the file name in the array
    aFileNames(nCounter) = sFile

    'subsequent calls without param return next file
    sFile = Dir

    'make sure your array is large enough for another
    nCounter = nCounter + 1
    If nCounter > UBound(aFileNames) Then
        'preserve the values and grow by reasonable amount for performance
        ReDim Preserve aFileNames(UBound(aFileNames) + 255)
    End If

Loop

'truncate the array to correct size
If nCounter < UBound(aFileNames) Then
    ReDim Preserve aFileNames(0 To nCounter - 1)
End If

'return the array of file names
GetFilesDir = aFileNames()

End Function

Sort an array:
Code:
Function SortArrayZtoA(myArray As Variant)
'9/4/2020 isaac
'i got this from https://exceloffthegrid.com/sorting-an-array-alphabetically-with-vba/  Brilliant !!
Dim i As Long
Dim j As Long
Dim Temp

'Sort the Array Z-A
For i = LBound(myArray) To UBound(myArray) - 1
    For j = i + 1 To UBound(myArray)
        If UCase(myArray(i)) < UCase(myArray(j)) Then
            Temp = myArray(j)
            myArray(j) = myArray(i)
            myArray(i) = Temp
        End If
    Next j
Next i

SortArrayZtoA = myArray

End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:12
Joined
Sep 21, 2011
Messages
14,038
I'd try and approach it from another angle.
Go to who ever exports that data for you and ask for some naming convention that defines the order.?

I was thinking you could use the date and time of the file creation, but what's to say two files will not get created in the same second?
 

jmsm

New member
Local time
Today, 11:12
Joined
Oct 1, 2020
Messages
12
I'd try and approach it from another angle.
Go to who ever exports that data for you and ask for some naming convention that defines the order.?

I was thinking you could use the date and time of the file creation, but what's to say two files will not get created in the same second?
That would be the best approach but unfortunately that's impossible. There are excel files generated by a client which I can't control.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:12
Joined
Sep 21, 2011
Messages
14,038
That would be the best approach but unfortunately that's impossible. There are excel files generated by a client which I can't control.
Ask them to generate them with a few seconds between each then?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:12
Joined
Feb 19, 2013
Messages
16,553
it may be the excel files are generated from multiple systems, each of which has a different date definition in the file name. So if you know the contents of the file you can adjust accordingly. Or perhaps the data contains the date/time information so the order of import is irrelevant - might matter for Excel, but completely irrelevant to a database.
 

Users who are viewing this thread

Top Bottom