Masking date time within file name

if the data structure of the CSV file doesn't change:

1. import one
2. modify data structure to add field AT THE END for the file path and name (ie: fPathName, text, 255)
3. Import data to existing table
4. run update query to populate fPathName for all records that don't have it filled

once you do this process manually, you can automate it with VBA.

to improve: the file names you import will be in their own table that VBA would populate with AutoNumber FileID and fPathName (or just fName if path is always the same) -- and then store a numeric FileID in the related table instead of the storing path\filename onto each record.

what kind of data is this?

Hi Strive4Peace (love the name!!)

Since I have little experience with Access and SQL (just enough to be dangerous) I am not clear on the suggestion.     The data being imported is production hours worked by employee, employee id, start time, end time, activity code, etc.     So for each day worked there is a file which is generated based upon time clocks we have within the Distribution Center.      Here is what an actual file name looks like: 20170320061616_Time Detail031917b.csv.     The file data never changes and the naming convention also remains consistent with the only change being the date time at the start of the file name and the date after "Time Detail".        We would like to have an import button that a user would press AFTER entering the date of the file to be imported such as 031917.       Can we mask the preceding date and time which is prior to "Time Detail" and then somehow program in SQL to look for i.e.; 00000000000000_Time Detail&MMDDYY&b.csv?

Rosemary

Hi Rosemary,

thank you

by "structure of the CSV file doesn't change" I mean -- are the columns you import always the same? you said, "file data never changes" so I will assume, for now, that it is.

Before the process can be automated, it is best to have a clear understanding of how things happen manually.

> "actual file name "

file NAME is only important to identify where the data is

* QUESTION: Are the files to import always in the same place or does the location (path) change?

If path is always the same and Access keeps track of what what it imports (ie, using a table), the path can be checked and if there is anything new, Access can bring it in.

* QUESTION: Is the new data just a new version of ALL the data, or is it only new records?

> "program in SQL"

not by itself -- VBA will be needed to make this happen more seamlessly.

> "little experience with Access and SQL"

where is your comfort zone?

> "not clear on the suggestion"

to learn about the basics of Access, read this short free book:

http://www.AccessMVP.com/strive4peace

Hi Strive4Peace

The files are always stored in the exact same place and the data is always new for each day, however there may be several iterations of the file within a specific day but the only important one is the file that has the "b" at the end.

I am pretty comfortable with access in general however writing and compiling SQL or VBA statements is very new to me.     Thank you for the book and I will read through it to update my knowledge.

Thank you
Rosemary

you're welcome, Rosemary

the book will help, and open your eyes. What you want can be automated with VBA ... once there is a firm grasp on the manual process -- so that is the first step: defining exactly what needs to happen. To do that, you need better knowledge of what Access can do and how it works.

take your time ... get through the book (luckily it is short and has lots of pictures) and then respond again.

> "The files are always stored in the exact same place and the data is always new for each day, however there may be several iterations of the file within a specific day but the only important one is the file that has the "b" at the end"

great! Access could, using VBA, store the path for importing and do it automatically once exactly what needs to happen is known.  The process could also be put on a timer so that someone would not have to manually launch it to run (although a button to do that is the step before the timer).

Rosemary, your code needs to
a) accept a date input (MMDDYY) by the user
b) look in a specific folder for a filename that contains the date input and "b"
c) return the full name of that file so that it can be imported.
If that right, this code will meet those requirements.  (If not, please post the corrected requirements and we'll take another swing.  :)  )

Dim strDate as string, strFolderNa as string, fso As Object,  objFiles As Object

strFolderNa = “YourPathToFolder”
strDate  = InputBox("Enter date of data to be imported, using MMDDYY format:", "TitleOfYourDatabase")

'loop through files in folder until one matches
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFiles = fso.GetFolder(strFolderNa).Files

For Each obj In objFiles
   If InStr(obj.Name, “*” & strDate & “b.csv”) > 0 Then
      strFileNa = obj.Name
      Set objFiles = Nothing
      Set fso = Nothing
      Set obj = Nothing
      GoTo LinkCSVFile 
   End If
Next obj

'If execution does not GoTo LinkSpreadsheet, no file was found
strMsg = "No file matching the name pattern '" & strDate  & "' was found in " & strFolderNa & ". " & _
"Confirm the date of the data to be imported and restart this process."
MsgBox strMsg, vbOKOnly, "TitleOfYourDatabase"
Exit Sub

LinkCSVFile :
‘your code to import the data goes here


ExitSub:
      Set objFiles = Nothing
      Set fso = Nothing
      Set obj = Nothing
      Exit Sub

Select allOpen in new window

You will need to replace “YourPathToFolder” and  "TitleOfYourDatabase" with text appropriate to your process.

The input box prompts the user to enter the MMDDYY-format date, and the code then loops through all the files in the specified folder, looking for a file with a name that contains the MMDDYY string and ends with “b.csv”.
If no filename matches those parameters, the user is notified, and the process is halted.  If a filename does match, execution of the code jumps to the LinkCSVFile section.  Put your code to link to the file and import the data after that section name.

If you have a reasonable expectation that the user will be importing data for the current date, you can use this line to display a default value the user would just have to confirm:

strDate  = InputBox("Enter date of data to be imported, using MMDDYY format:", "TitleOfYourDatabase", Format(Date(), "MMDDYY")

Select allOpen in new window

If the data imported belongs to the previous work day, you can tweak the Date() to return a previous date.

Hi Paul;

Thank you for the response.    This looks promising and looks familiar to me based upon my reading some existing code in the database.

I will try your suggestion and let you know how this turns out.

Thank you again;

Rosemary

Note that you may have to type over the single and double quotation marks in my code;  sometimes pasting turns them into curved rather than straight, and Access only likes straight ones.  :)

Hi Paul;

Thank you for the tip because I would not have known this and it will definitely save us time in trying to troubleshoot.


Regards;
Rosemary

Hi Paul;

We have been attempting to figure this our using your code but ran into some issues.     In the code, what should be the value for the Object or Object files.     Here is what is happening when we enter the date it appears all is well until Access returns a message that the file cannot be found.     We have the file name and information correct so it appears that we have missed something.     Can you assist us with were we have gone astray?

Appreciate the assistance

Regards;
Rosemary

Hi, Rosemary!
The value for objFiles is coming from your code, in line 3.  With what did you replace "YourPathToFolder"?
Let's confirm that the file name is being correctly identified.  on line 25 put

debug.print "strFileNa:  " & strFileNa 

Select allOpen in new window

If the Immediate pane is not already open, press Control-G to open it.
Go back to your form and click the button;  the code will drop the name of the file it found in the immediate pane;  that will help us figure out where the glitch is.

Hi Paul;

I apologize for the time lapse but working on several projects at the time and I have now had an opportunity to focus on attempting to import this file.     I have pasted the code as I have it in our access database and adjusted it to reflect mmddyyyy since this is actually what is entered by the users versus the mmddyy.       The error message being displayed is :  No file matching the name patter '03172017' was found in \\SFTP0100\AS400data\..........' Confirm the date of the data to be imported and restart this process.     I have confirmed that the directory and file does exist in the appropriate place file name is 201703201111626_time Detail03172017b.csv.      I did enter the debug.print logic you provided on line 25 however it did not provide any details.    I left it in the code.      I am uncertain if what I have provided will assist with trouble shooting but if you have time to review an provide us with some direct it would once again be appreciated.


Dim strDate As String
Dim strFolderNa As String
Dim fso As Object
Dim objFiles As Object
strFolderNa = "\\SFTP0100\AS400data\Prod\ProductivityStandards\Activity\Archive"
strDate = Me.Text4
strDate = Format(strDate, "Short Date")
strDate = Format(strDate, "mmddyyyy")

Set fso = CreateObject("Scripting.FileSystemObject")
Set objFiles = fso.GetFolder(strFolderNa).Files
For Each obj In objFiles
   If InStr(obj.Name, "*" & strDate & "b.csv") > 0 Then
        strFileNa = obj.Name
        Set objFiles = Nothing
        Set fso = Nothing
        Set obj = Nothing
        GoTo LinkCSVFile
   End If
Next obj

strMsg = "No file matching the name pattern '" & strDate & "' was found in " & strFolderNa & ". " & "Confirm the date of the data to be imported and restart this process."
MsgBox strMsg, vbOKOnly, "Packing GOH Backup"
Exit Sub
Debug.Print "strFileNa:  " & strFileNa

LinkCSVFile:
DoCmd.TransferText acImport, , "ADP_File", strFolderNa, True
Exit Sub:
    Set objFiles = Nothing
    Set fso = Nothing
    Set obj = Nothing
    Exit Sub

Thank you
Rosemary

Let's try this:
a) put the debug.print immediately below strFileNa = obj.Name so we know exactly what we're trying to match, and
b) take a screenshot of \\SFTP0100\AS400data\Prod\ProductivityStandards\Activity\Archive so we know what we're looking in.  

P

Hi Paul;
I have attached a snip of the directory \\SFTP0100\.......    Currently the files are showing a name of yyyymmddHHMMSS_Time DetailMMDDYYb.csv but the users actually enter MMDDYYYY for the importing so if you look at the bottom of the snip you see the file YYYYMMDDMMHHSS_Time DetailMMDDYYYYb.csv which is my test file.

I placed the debug as you instructed but still did not receive an debug information on the error that I mentioned in my previous email.   I have also attached a snip of the logic so you can see I did place the debug in the appropriate location.

Thank you
Rosemary
Directory-ADP.JPG
Logic-for-Date-in-GOH.JPG

Do you have the Immediate pane open so you can see the result of the Debug.Print?  (if not, open it.  You should also try stepping through the code  with the Locals pane open so you can see the code work it's way through the file.

I think the problem is the way the search string gets the formatted date.  Let's say the user wants the file for March 23, 2017.
The user enters 03232017 in Text4, and expects to find file 20170324111630_Time Detail032317b.csv
strDate = Format(strDate, "Short Date")  formats strDate = "3/23/2017"
strDate = Format(strDate, "mmddyyyy")  formats strDate = "03232017"

When the code starts looping through each obj In objFiles, it is looking for a file named *03242017b.csv.  It's not going to find one, because the string in the file name is *032417b.csv.

Delete (or comment out) the first strDate = Format.... statement;  the format it is applying is wiped out by the next statement.
Change the next statement to strDate = Format(strDate, "mmddyy");  you're changing the format for the Year part of the string from 2017 to 17.

Give that a whirl, and let us know how it works out.  :)

Hi Paul

Thank you for your patience with us.      I will be attempting these changes shortly and let you know how it works out.

Regards;
Rosemary

Hi Paul;

Once again I apologize for the delay in my response.    We did attempt the changes you suggested and we are still having issues.  

If you would not mind further assisting us I have assigned Nichelle who works for me and is dedicated to this database to contact you using this email train.      If this is acceptable to you I will have here send you a note tomorrow as she is out of the office today.

My apologizes again;
Rosemary

I'm happy to help;  if Nichelle would like we can schedule a LiveSupport session through EE and get it resolved. :)

Hello Paul. This is Nichelle now. Sorry that I was unable to contact you yesterday; doctor's appointment.

Anyways, I walked through the code step by step and in theory, everything checks out. However, it still won't allow me to import the file.
There is also the fact that when I try to do the import using a different kind of code, I get both a confirmation message and a failure message.
 Is there a time later today that we could maybe go over this? I'm free for most of the day up until 4:15pm.

Thank you so much for taking the time out of your day to help!
Code.JPG
Should-I-Import..JPG
Could-Not-Find.JPG

GM, Nichelle.  :)  Yes, let's find a time that works;  what time zone are you in?  And do you have screen-sharing software ( like https://chrome.google.com/webstore/detail/chrome-remote-desktop/gbchcmhmhahfdphkhkmpfmihenigjmpp?hl=en or something else) so we can watch the code execute together?

I'm Eastern standard time; at the time I'm writing this it is 11:42 am. I have join.me active, and the code is NHalliday1. Would that work for you?

https://join.me/NHalliday1

Nichelle, what's your phoone numbedr?

Here is the form you wanted.
Database1.accdb

Hi Paul; Nichelle again. Are you having any luck with figuring out the code problem?

Nichelle, I'm sorry-- I got hit with a huge project at work, and haven't had a chance to look at the db.  I don't expect to have any bandwidth until Monday afternoon, at the earliest-- I'm really sorry.

Three suggestions you might try:  Use InStr to match the string your're searching for to the obj.Name, or modify the code to accept the full file name from the input box, or use the FilePicker to select the file you want to import.