Imports csv file into access database with a timestamp

I am trying to automate a process that imports csv files on daily bases into a access database.  The filesname makes a little difficult for me to automate the process.  The word document below shows that task I am trying to complete in the module. So I am including the access database and the two csv files
importingfiles.accdb
DIV-OFFICE20150424042344.csv
DIV-OFFICE20150425042344.csv
Import-Automation.docx
centralmikeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
First - I'd suggest you ask the questions here on EE, and not bury them in your Word document. Many Experts simply will not open those types of documents, and it makes helping you more difficult. I'd also suggest you break out your questions into separate posts. You have 3 distinct questions related to the same process, but they could each stand on their own. Many Experts will not get bogged down in these "project" type questions.

Are your files actually named "DIV_OFFICExxxx"? Or will the word "DIV_OFFICE" be replaced by something like "ATLANTA", or some other text?

You can loop through the files in a directory with the DIR command:

Dim sFile As String
sFile = Dir("YourFolderPath\*.csv")

Dim sCurrentFile As String
dim dCurrentDate as Date
'/ set to a very old date
dCurrentDate = #01-01-1900#

Do Until Len(sFile) = 0
  If Len(sCurrentFile) = 0 Then
    sCurrentFile = sFile
  End If
  Dim sDate as string = Mid(sFile, 10, 8)
  Dim dte As Date = DateSerial(Left(sDate, 4), Right(sDate, 2), Mid(sDate,2, 2))
  If dte > dCurrentDate Then
    dCurrentDate = dte
    sCurrentFile = sFile
  End If
  '/ get the next file
  sFile = Dir
Loop

At this point, sCurrentFile should contain the most recent file. From there you can use the TransferText or TransferSpreadsheet method:

DoCmd.TransferText acImportDelim, "myDiv_OfficeImportSpecification", "TempTable", "YourfolderPath\" & sFile & ".csv", True

Note I'm importing to a Temporary table named "TempTable". Once you do that, you would then use basic VBA/SQL to validate the incoming data and then move it to your live table.

As to not importing the file more than once, you could add a new table to track which files have been imported. If you do that, then before you run the TransferText process, you'd check that table to determine if the file has already been imported. Is so, then abort the process, or send your email - you can use Sendobject for that.
0
centralmikeAuthor Commented:
The files are actually named like this "DIV_OFFICExxxx".  I thought the word document made it a little more easier to read.  i created a macro1 it runs successfully.  I tried running the following code module an got a error runtime 3011. I changed your sdate string from 10 to 11. Cant figure out the reason it erroring.



Public Function testing()
Dim sFile As String
'sFile = Dir("YourFolderPath\*.csv")
sFile = Dir("Y:\My Documents\IFS\Lead Attribution\Mike\DivisionOffice\*.csv")
'Y:\My Documents\IFS\Lead Attribution\Mike\DivisionOffice\DIV_OFFICE20150424042344.csv"

Dim sCurrentFile As String
Dim dCurrentDate As Date
'/ set to a very old date
dCurrentDate = #1/1/1900#

Do Until Len(sFile) = 0
  If Len(sCurrentFile) = 0 Then
    sCurrentFile = sFile
 End If
 
  Dim sDate As String
  sDate = Mid(sFile, 11, 8)
  Dim dte As Date
  dte = DateSerial(Left(sDate, 4), Right(sDate, 2), Mid(sDate, 2, 2))
  If dte > dCurrentDate Then
    dCurrentDate = dte
    sCurrentFile = sFile
  End If
  '/ get the next file
 
  sFile = Dir

Loop

'At this point, sCurrentFile should contain the most recent file. From there you can use the TransferText or TransferSpreadsheet method:

DoCmd.TransferText acImportDelim, "test", "TempTable", "Y:\My Documents\IFS\Lead Attribution\Mike\DivisionOffice\" & sFile & ".csv", True






End Function
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you pinpoint the line that errored?

Error 3011 is a "can't find the file" error, so be sure that the filename is properly formatted. I don't recall if you need the ".csv" on the end or not, for example.

If you can set a breakpoint on the TransferText line, run the code and then type this in the Immediate window:

?sFile

Post back here what your code is reporting.

Also, you might try this for your TransferText line:

DoCmd.TransferText acImportDelim, "test", "TempTable", "Y:\My Documents\IFS\Lead Attribution\Mike\DivisionOffice\" & sCurrentFile & ".csv", True
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

centralmikeAuthor Commented:
Scott, I tried putting the code into module as you suggested.  The module updates the file with 04/25 records instead of the 04/24 records.  I will send a copy of the database with you proposed changes.
importingfiles.accdb
0
centralmikeAuthor Commented:
Scott, I think I figured it out.  The following statement should be a equal sign instead of the > in the following code statement.
 If dte > dCurrentDate - the date would be greater than the current date
 If dte = dCurrentDate - the date would be equal to the current date
0
centralmikeAuthor Commented:
Sorry Scott, My mistake that changed didn't work either.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My code just gets the "latest" date. If you only want to work with the current date, then do this:

Do Until Len(sFile) = 0
  If Len(sCurrentFile) = 0 Then
    sCurrentFile = sFile
  End If
  Dim sDate as string = Mid(sFile, 10, 8)
  Dim dte As Date = DateSerial(Left(sDate, 4), Right(sDate, 2), Mid(sDate,2, 2))
  If dte = Date Then
    sCurrentFile = sFile
    Exit Do
  End If

  '/ get the next file
  sFile = Dir
Loop
0
centralmikeAuthor Commented:
Scott, Code still not working quite right.  The first issue is with "Dim Sdate" and  "Dim dte" so i changed the code to like this:
 Dim sDate As String
  sDate = Mid(sFile, 11, 8)
***if you leave the code at 10 you get a type error.  The date string does not start until position 11
  Dim dte As Date
  dte = DateSerial(Left(sDate, 4), Right(sDate, 2), Mid(sDate, 2, 2))
_____________________________________________________________________________________
I added a third file to directory with csv files of with a date of 04/23/2015. the function you sent loaded the 04/23/2015 file instead of the 04/25/2015 which is the current date.
here is a copy of the module
Public Function testing()
Dim sFile As String
'sFile = Dir("YourFolderPath\*.csv")
sFile = Dir("Y:\My Documents\IFS\Lead Attribution\Mike\DivisionOffice\*.csv")



Dim sCurrentFile As String
Dim dCurrentDate As Date
'/ set to a very old date
dCurrentDate = #1/1/1900#

Do Until Len(sFile) = 0
  If Len(sCurrentFile) = 0 Then
    sCurrentFile = sFile
 End If
 
  Dim sDate As String
  sDate = Mid(sFile, 11, 8)

  Dim dte As Date
  dte = DateSerial(Left(sDate, 4), Right(sDate, 2), Mid(sDate, 2, 2))

   If dte = Date Then
    sCurrentFile = sFile
    Exit Do
  End If
  '/ get the next file

 sFile = Dir

Loop

'At this point, sCurrentFile should contain the most recent file. From there you can use the TransferText or TransferSpreadsheet method:

DoCmd.TransferText acImportDelim, "test", "TempTable", "Y:\My Documents\IFS\Lead Attribution\Mike\DivisionOffice\" & sCurrentFile, True


'DoCmd.Hourglass False
'MsgBox "Transfer complete"


End Function
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Place a Breakpoint at this line:

If dte = Date Then

Run the program, and determine the value of "dte" for each. To do that, in the Immediate window enter this:

?dte

And press the Enter key. Post back here with the Dates you're seeing.
0
centralmikeAuthor Commented:
when loops through the module these are the dates that show up
02/01/2017
01/01/2017
12/01/2016
11/01/2016
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Ahhh ... I've got the DateSerial wrong. Should be:

  dte =DateSerial(Left(sDate, 4), Mid(sDate, 5, 2), Right(sDate, 2))
0
centralmikeAuthor Commented:
Scott, I wanted to run last test before closing this post.  If I have no files for the 04/27/2015 should this process still import the last file.  I would have expected the module to have an empty temptable with no records.  But when I ran the process it imported records from 04/25/2015 which is the first date in the directory.

-------------------------------------------------------------------------------------------------------------

Public Function testing()

Dim sFile As String
'sFile = Dir("YourFolderPath\*.csv")
sFile = Dir("Y:\My Documents\IFS\Lead Attribution\Mike\DivisionOffice\*.txt")

'DoCmd.Hourglass True
'DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "TempTable"
DoCmd.RunSQL "DELETE * FROM T_DIVISION_OFFICE_STG_import"


Dim sCurrentFile As String
Dim dCurrentDate As Date
'/ set to a very old date
dCurrentDate = #1/1/1900#

Do Until Len(sFile) = 0
  If Len(sCurrentFile) = 0 Then
    sCurrentFile = sFile
 End If
 
  Dim sDate As String
  sDate = Mid(sFile, 11, 8)

  Dim dte As Date
  dte = DateSerial(Left(sDate, 4), Mid(sDate, 5, 2), Right(sDate, 2))
   If dte = Date Then
    sCurrentFile = sFile
    Exit Do
  End If
  '/ get the next file

 sFile = Dir

Loop

'At this point, sCurrentFile should contain the most recent file. From there you can use the TransferText or TransferSpreadsheet method:

DoCmd.TransferText acImportDelim, "import_div_spec", "TempTable", "Y:\My Documents\IFS\Lead Attribution\Mike\DivisionOffice\" & sCurrentFile, False


'DoCmd.Hourglass False
'MsgBox "Transfer complete"
'DoCmd.SetWarnings True

End Function
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It would import the first file it finds, if there is no "current" file.

To fix that, I moved the TransferText to the IF block that checks the date, and also removed the first few lines after the DO loop starts, and a couple of the DIM statements we no longer need:

Dim sFile As String
sFile = Dir("Y:\My Documents\IFS\Lead Attribution\Mike\DivisionOffice\*.txt")

DoCmd.DeleteObject acTable, "TempTable"
DoCmd.RunSQL "DELETE * FROM T_DIVISION_OFFICE_STG_import"

Do Until Len(sFile) = 0
  Dim sDate As String
  sDate = Mid(sFile, 11, 8)

  Dim dte As Date
  dte = DateSerial(Left(sDate, 4), Mid(sDate, 5, 2), Right(sDate, 2))
  If dte = Date Then
    DoCmd.TransferText acImportDelim, "import_div_spec", "TempTable", "Y:\My Documents\IFS\Lead Attribution\Mike\DivisionOffice\" & sFile, False
    Exit Do
  End If
  '/ get the next file
  sFile = Dir
Loop

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
centralmikeAuthor Commented:
Thanks Scott for all your help.  I learned a lot from the exercise.  Everything worked out great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.