Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    #dodgers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    11

    How to import part of filename into a field in access 2007

    I have a file that i import into access 2007 and i was wondering if i can take part of that file name and put it into a field in access? For example here is one example of a file name:


    "20140211_agent_statistics.csv"
    I have done some research on this but cant seem to find the answer when numbers change all of the time. I just need to grab the numbers on this file name. However, these numbers change all of the time. Does anyone have a solution for this? Thank you in advance. Any help and code is much appreciated i am very new to vba.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You can get the numbers with Left(TheFileName,8)

    This will take the leftmost 8 characters from a string variable I called TheFileName

    You would assign like this"
    Code:
    Dim theNumbers as string
    Dim TheFileName as string
    TheFileName ="20140211_agent_statistics.csv"
    theNumbers = Left(TheFileName,8)
    ....

  3. #3
    #dodgers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    11
    Can i use this even if the filename will change frequently? I get emails a couple times a week with the same file name however the numbers are always different. Thank you for the reply orange.




    Quote Originally Posted by orange View Post
    You can get the numbers with Left(TheFileName,8)

    This will take the leftmost 8 characters from a string variable I called TheFileName

    You would assign like this"
    Code:
    Dim theNumbers as string
    Dim TheFileName as string
    TheFileName ="20140211_agent_statistics.csv"
    theNumbers = Left(TheFileName,8)
    ....

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    the numbers are always different
    If the file name always has 8 digits on the left, then it will work.
    How different are these different numbers?

  5. #5
    #dodgers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    11
    They arent too different. They are days 20140211 = yyyymmdd. But there are days we do not get them. Saturday and Sundays we do not get an email.

  6. #6
    #dodgers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    11
    Also Orange, because they are in this format is there a way when i import this information into the field it will go into the field as a general date? mm/dd/yyyy? Thank you

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Saturday and Sundays we do not get an email.
    If you don't get files on Saturday and Sunday, then there is nothing to do as far as getting part of the filename goes.

    You can change 20140211 to mm/dd/yyyy format and even make it a Date/Time data type.

    Code:
    Sub Test201402()
    Dim Filename As String
    Dim MyDate As Date
    Filename = "20140211"
    MyDate = DateSerial(Left(Filename, 4), Mid(Filename, 5, 2), Right(Filename, 2))
    Debug.Print MyDate
    End Sub
    Good luck.

  8. #8
    #dodgers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    11
    Thank you orange. i appreciate the help that you have given me. One last question. Is there a way i can use parameters for what i am trying to do?

    example. When i click a button to import can i have the user type in a date that will be put into that field? Does that make sense?

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not really. I thought you got the file sent to you. If the file is sent to you, then it should already have a name.

    How exactly do you see how a parameter might work?
    Do you store the files on your PC? Or somewhere in a standard manner?

  10. #10
    #dodgers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    11
    I get an email everyday and the file is saved into "C:/imports" folder i hit a button and it imports to a table automatically.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm guessing that the button checks for the latest file etc.

    What table does it import into? What happened to yesterday's data? Do you ever re-import a file by mistake?
    How important are these files? Are they archived for some reason - audit/history/analysis?
    Where did you see a use for a parameter?

  12. #12
    #dodgers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    11
    I have a code that after it imports into a table it deletes the file so the file is always empty until i save a file into that folder. The file is imported into a table called "Agents". What these files are, are a list of employees and the phone history throughout the day. It keeps track of how many phone calls are answered or unanswered throughout the day. What i was hoping to do was on import i can have an automated code that would place the date into a column named timestamp. I wanted a parameter so the user can type in the date of the data. However i just found out that the user should not be able to type in any information in the database. They want everything automated. So the parameter is no longer needed and i now have to find a way to do this automatticlly. If you have any suggestions that would be great. Thank you Orange for your help through my struggles.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How is the file created? There must be some means to add the time.
    This file is built each day, so you know the Day involved. It's possible that whatever is being used to create the file could add the time --if the time is important.
    Since you know the Day, you could add a field to your table and fill it with the Day/Date info.

  14. #14
    #dodgers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    11
    Our company uses a third party that keeps track of all of this through a database that they have. They only send us num of calls answered num calls unanswered avg call time and the persons name. The file is saved to that folder and then i import it through a button.

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What i was hoping to do was on import i can have an automated code that would place the date into a column named timestamp.
    What date?
    The date the file was imported?
    The date the file was created?
    The date the file was sent to you?
    The date of the data collected?

    If you have a file name of "20140211_agent_statistics.csv", is 20140211 the date of the calls, the date the file was created or the date the file was sent to you?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-30-2013, 01:37 AM
  2. Access 2003: import of csv fails due to period in filename
    By vanthien in forum Import/Export Data
    Replies: 5
    Last Post: 01-12-2013, 07:03 PM
  3. Replies: 2
    Last Post: 09-07-2011, 11:33 AM
  4. Replies: 3
    Last Post: 06-02-2011, 02:08 PM
  5. Prompt for filename for import/export??
    By stephenaa5 in forum Import/Export Data
    Replies: 23
    Last Post: 10-23-2009, 03:43 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums