UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Exporting Multiple Text Files, Access 2016    
 
   
ollyhutsy
post Feb 26 2020, 09:01 AM
Post#21



Posts: 95
Joined: 16-October 19
From: United Kingdom


my plan is to distinguish them by time, so no one would clock in after 11:00AM. and by it been the second clock of the day for that employee. but ill cross that bridge when i come to it.
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 09:03 AM
Post#22


UtterAccess Moderator
Posts: 12,605
Joined: 6-December 03
From: Telegraph Hill


OK.

Are you sure that the value 1 in Column D doesn't distinguish them (eg 0 for checkout)?

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


Regards,

David Marten
Go to the top of the page
 
ollyhutsy
post Feb 26 2020, 09:07 AM
Post#23



Posts: 95
Joined: 16-October 19
From: United Kingdom


No its 1 for every record unfortunately.
Go to the top of the page
 
ollyhutsy
post Feb 26 2020, 09:37 AM
Post#24



Posts: 95
Joined: 16-October 19
From: United Kingdom


I have looked on the clock in software and on there you distinguish clock in and clock outs by time scales, i would have to replicate this on access. my main concern is looping through the files and getting the data into access, without duplicating any records.
Go to the top of the page
 
projecttoday
post Feb 26 2020, 11:35 AM
Post#25


UtterAccess VIP
Posts: 11,782
Joined: 10-February 04
From: South Charleston, WV


What's the name of this file? How often and how many do they generate them? More than 1 employee in a file?

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 12:16 PM
Post#26


UtterAccess Moderator
Posts: 12,605
Joined: 6-December 03
From: Telegraph Hill


OK,

Create your ClockIns table. I would have the following columns:
ID - PK, auto
ClockInDateTime - DateTime
EmployeeID - Long Integer
SourceFile - String

Create a unique index over ClockInDateTime and EmployeeID (shout if you need help creating an index that uses more than one field
Also, create an (non-unique) index on SourceFile - it will be need for searching on

Then you need code to:
1. Loop through files in a folder
2. Read a text file
3. Parse the lines in a text file
4. Insert parsed data in to your table

2. Read a text file. In a standard module (name it something like 'modTextFromFile') add the following code:
CODE
Option Compare Database
Option Explicit

Private Const UTF8 As String = "utf-8"

Private Enum StreamReadEnum
  adReadAll = -1
  adReadLine = -2
End Enum

Private objStream As Object

Function ADOStream() As Object

  If objStream Is Nothing Then
    Set objStream = CreateObject("ADODB.Stream")
  End If
  Set ADOStream = objStream

End Function

Public Function fStreamTextGetAll(strFullPathToTextFile As String) As String

  Dim strRet As String

  With ADOStream
'    .Charset = UTF8      ' you probably don't need this
    .Open
    .LoadFromFile strFullPathToTextFile
    strRet = .ReadText(adReadAll)
    .Close
  End With
  fStreamTextGetAll = strRet

End Function


1, 3, 4: In another module (named something like 'modClockinImport') add the following code:
CODE
Function ImportFolderCSVs(strPathToCSVFolder As String) As Boolean

  Const BS As String = "\"
  Const FILE_SPEC As String = "A300_*.csv"
  Dim strFile As String

  strFile = Dir(strPathToCSVFolder & BS & FILE_SPEC)
  Do While Len(strFile) > 0
    If DCount("*", "ClockIns", "SourceFile = '" & strFile & "'") = 0 Then
      Call ImportClockIn(strPathToCSVFolder & BS & strFile)
    End If
  Loop
  ImportFolderCSVs = (Err = 0)

End Function

Function ImportClockIn(strPathToCSV As String) As Boolean

  Dim arrLines As Variant, i As Integer, _
      arrFields As Variant, j As Integer, _
      strText As String, strSQL As String, _
      strFile As String, iInsertCount As Integer

  Const DATE_FLD As Integer = 1, _
        EMP_ID_FLD As integer = 2

  strFile = Mid(strPathToCSV, InStrRev(strPathToCSV, "\") + 1)
  strText = fStreamTextGetAll(strPathToCSV)
  arrLines = Split(strText, vbNewLine)
  If UBound(arrLines) >= 0 Then     ' Check the files has contents
    For i = 0 To UBound(arrLines)
      arrFields = Split(arrLines(i), ",")
      If Ubound(arrFields) >= 2 Then     ' Check we have at least 3 fields
        strSQL = "INSERT INTO ClockIns (ClockInDateTime, EmployeeID, SourceFile) VALUES (" & _
                 Format(Trim(arrFields(DATE_FLD)), "\#yyyy\-mm\-dd hh:nn:ss\#") & ", " & _
                 Trim(arrFields(EMP_ID_FLD)) & ", " & _
                 "'" & strFile & "');"
        Debug.Print strSQL
        With CurrentDb
          .Execute strSQL, dbFailOnError
          iInsertCount = iInsertCount + .RecordsAffected
        End With
      End If
    Next i
  End If
  ImportClockIn = (iInsertCount = i) Or (i = -1)

End Function


Then you can put code behind the Click event of a button in a form:
CODE
Private Sub cmdImportClockIns_Click()

  Call ImportFolderCSVs("C:\Users\OllyHutsy\Documents")   ' <-- change the folder path as required

End Sub


*** NOTE: all the above is aircode - so will likely need correction!!! ***

First thing after copying is to: Debug Menu -> Compile.

hth,

d


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


Regards,

David Marten
Go to the top of the page
 
ollyhutsy
post Feb 27 2020, 04:42 AM
Post#27



Posts: 95
Joined: 16-October 19
From: United Kingdom


Hi David, thanks for taking the time to write that. I have an issue though. I followed your instructions and initially got an error message : https://www.UtterAccess.com/forum/index.php...st&id=93301

This is what i saw in the immediate section: https://www.UtterAccess.com/forum/index.php...st&id=93302

i assumed the formatting was wrong in regards to which column i was trying place the data in so i changed around the integer numbers 1 and 2 for date and employee ID as shown here: https://www.UtterAccess.com/forum/index.php...st&id=93303

Now this made not respond and ultimately crash, however it did copy my data correctly into the table... Any suggestions on what might be going wrong?
Attached File(s)
Attached File  error_message.JPG ( 17.55K )Number of downloads: 1
Attached File  1st_error.JPG ( 179.97K )Number of downloads: 3
Attached File  edit.JPG ( 37.75K )Number of downloads: 2
 
Go to the top of the page
 
cheekybuddha
post Feb 27 2020, 04:49 AM
Post#28


UtterAccess Moderator
Posts: 12,605
Joined: 6-December 03
From: Telegraph Hill


Well spotted that I got the field order mixed up. thumbup.gif

>> Now this made not respond and ultimately crash <<

Ouch! I left out an essential line in function ImportFolderCSVs():
CODE
' ...
  Do While Len(strFile) > 0
    If DCount("*", "ClockIns", "SourceFile = '" & strFile & "'") = 0 Then
      Call ImportClockIn(strPathToCSVFolder & BS & strFile)
    End If
    strFile = Dir      ' <-- ADD THIS LINE
  Loop
' ...


hth,

d

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


Regards,

David Marten
Go to the top of the page
 
ollyhutsy
post Feb 27 2020, 05:21 AM
Post#29



Posts: 95
Joined: 16-October 19
From: United Kingdom


works a treat! No limit to what you can do with VBA if you have the knowledge, Thanks a lot. thumbup.gif
Go to the top of the page
 
cheekybuddha
post Feb 27 2020, 05:56 AM
Post#30


UtterAccess Moderator
Posts: 12,605
Joined: 6-December 03
From: Telegraph Hill


yw.gif

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


Regards,

David Marten
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    29th March 2020 - 10:32 AM