TRIM function changes data type from "Short Text" to "Long Text" (when creating new table)

[skydivetom]

Good morning:



In a previous thread, forum expert @Darkwind assisted me with developing a TRIM function. Including the background (for purposes of this thread) in not necessary in this new thread.

The attached DB includes the following objects:
- 2 tables {[T801_TEMP_Syslog_ac], [T801_TEMP_Syslog_ac_audit]}
- 1 form (open by default)
- 1 module (TRIM function)

Existing Process:
- In the form, click on command button "Trim Fields"
- The TRIM FIELDS function will then create a copy of the 2 existing tables and add object name extension = "_1".
- As part of the function, the following 2 activities occur:

1. Removes all "trailing" spaces (hugging each value in original tables)
2. Converts the *string* date/time (original table) and converts into a true date time field WHILE also inserting a new field [FIELD2Ms] which stores the milliseconds as numeric value.

All of the above works great and all credit must be given to @Darkwind for the development.

However, as part of this process, there's one undesired, secondary effect that I'd like to solve (w/o having re-writing the entire code).
a. The fields in the original tables are stored as "Short Text"
b. Upon executing the function, however, both [FIELD1] and [FIELD3] are stored as "Long Text" (memo).

Please see below code snippets:

' ... more code

'Loop thru fields in table
For Each fld In rs.Fields
   
   'Test first field value against format
   If fld.Value Like "####/##/## ##:##:##:###*" Then
      
      'If datetime contains milli-seconds (MS), split into two fields
      sSQLCreateTable = sSQLCreateTable & fld.Name & " " & ToName(dbTimeStamp) & ","
      sSQLCreateTable = sSQLCreateTable & fld.Name & "Ms " & ToName(dbInteger) & ","
      
      sSQLInsertData = sSQLInsertData & "CDate(Left([" & fld.Name & "], 19)),"
      sSQLInsertData = sSQLInsertData & "CInt(Right([" & fld.Name & "], len([" & fld.Name & "]) - 20)),"
      
      sFieldList = sFieldList & "[" & fld.Name & "],"
      sFieldList = sFieldList & "[" & fld.Name & "MS],"
   Else
      
      'Otherwise, import wholesale
      sSQLCreateTable = sSQLCreateTable & fld.Name & " " & ToName(fld.Type)
      sSQLInsertData = sSQLInsertData & "[" & fld.Name & "],"
      sFieldList = sFieldList & "[" & fld.Name & "],"
      If fld.Type = dbText Then
         sSQLCreateTable = sSQLCreateTable & " WITH COMPRESSION,"
         sSQLTrimFields = sSQLTrimFields & "[" & fld.Name & "] = Trim([" & fld.Name & "]),"
         bTrimFields = True
      Else
         sSQLCreateTable = sSQLCreateTable & ","
      End If
   End If
Next fld

' ... more code

When going through the loop, fields [FIELD1] and [FIELD3] enter the "Else" clause (e.g., "... import wholesale").
And, its nested If/Else statement should then interpret the original field names as "Short Text".

If fld.Type = dbText Then

Now, in the function "ToName" (see below), it is my understanding that CASE **Case DataTypeEnum.dbText** should be met and, therefore, "TEXT" will be read in.

If, and only if, that's the case, why would the new tables create [FIELD1] and [FIELD3] as "Long Text" (memo)?

Private Function ToName(ByVal iType As DataTypeEnum)

   Select Case iType
      Case DataTypeEnum.dbBoolean
         ToName = "YESNO"
      Case DataTypeEnum.dbByte
         ToName = "BYTE"
      Case DataTypeEnum.dbInteger
         ToName = "SHORT"
      Case DataTypeEnum.dbLong
         ToName = "LONG"
      Case DataTypeEnum.dbCurrency
         ToName = "CURRENCY"
      Case DataTypeEnum.dbSingle
         ToName = "SINGLE"
      Case DataTypeEnum.dbDouble
         ToName = "DOUBLE"
      Case DataTypeEnum.dbDate, dbTimeStamp, dbTime
         ToName = "DATETIME"
      Case DataTypeEnum.dbBinary
         ToName = "YESNO"
      Case DataTypeEnum.dbText
         ToName = "TEXT"                                            '** I believe the ELSE clause should meet this CASE for [FIELD1] and [FIELD3].   I maybe be wrong though.
      Case DataTypeEnum.dbLongBinary
         ToName = "LONGBINARY"
      Case DataTypeEnum.dbMemo
         ToName = "MEMO"                                            '** Does the function anywhere interpret [FIELD1] and [FIELD3] as memo?  Otherwise, why would table copies with "_1" extension end up as "Long Text"?
      Case DataTypeEnum.dbGUID
         ToName = "GUID"
      Case Else
         Err.Raise 5
   End Select
   
End Function

My question:
How should the existing (functioning) TRIM function be modified so that [FIELD1] and [FIELD3] keep their data type = "Short Text"?

Note: [Field2] is okay since I do need to concert it into 2 fields (Date/Time & Number]

P.S. Please allow me to re-iterate that I am extremely happy with the assistance I received from @Darkwind. His function is a SIGNIFICANT time-saver. For data analysis purposes, however, having all text fields (e.g., [FIELD1] and [FIELD3] in "Short Text" would be needed.

[darkwind]

The TEXT command in DDL creates long text fields. The datatypes stored in Access for the tables do not map 1:1 with datatype keywords available for the CREATE TABLE command - so, for maximum compatibility, I used the largest-sized text field that would return the dbText datatype.

If you want short text instead, just replace: ToName = "TEXT"

with: ToName = "CHAR"

Bearing in mind that the function will no longer work with any text field too long to fix in a short text field size.

[skydivetom]

Darkwind:

Thank you for the prompt response. That worked great!!

Wrt "Bearing in mind that the function will no longer work", how long is "too long"? Anything >= n ? characters and <= 255 characters?

Thanks,
Tom

[orange]

Darkwind, Tom,

Then this is related to the With Compression/Unicode? If a character could be stored in 2 bytes vs 1, then the space occupied could double - so a text field longer than ~128 could overflow a short text. Just guessing after looking at response #2.

[darkwind]

Now that I think of it, there's a better method:

Private Function ToName(ByVal iType As DataTypeEnum, ByVal iSize As Integer)


   Select Case iType
      Case DataTypeEnum.dbBoolean, DataTypeEnum.dbBinary
         ToName = "YESNO"
      Case DataTypeEnum.dbByte
         ToName = "BYTE"
      Case DataTypeEnum.dbInteger
         ToName = "SHORT"
      Case DataTypeEnum.dbLong
         ToName = "LONG"
      Case DataTypeEnum.dbCurrency
         ToName = "CURRENCY"
      Case DataTypeEnum.dbSingle
         ToName = "SINGLE"
      Case DataTypeEnum.dbDouble
         ToName = "DOUBLE"
      Case DataTypeEnum.dbDate, dbTimeStamp, dbTime
         ToName = "DATETIME"
      Case DataTypeEnum.dbText
         If lSize > 255 Then
            ToName = "TEXT"
         Else
            ToName = "CHAR"
         End If
      Case DataTypeEnum.dbLongBinary
         ToName = "LONGBINARY"
      Case DataTypeEnum.dbMemo
         ToName = "MEMO"
      Case DataTypeEnum.dbGUID
         ToName = "GUID"
      Case Else
         Err.Raise 5
   End Select
   
End Function

Then, adjust the calling line:

sSQLCreateTable = sSQLCreateTable & fld.Name & " " & ToName(fld.Type, fld.Size)

Now if the field's length is >255, it will create a long text. Otherwise, it will create a short text. So no compatibility issues and it should create short texts when possible.

[skydivetom]

Orange -- thank you for the additional info. Very helpful.

Darkwind -- again, thanks for helping me get to the "next level".

//

In the event I encounter short text values >= 128, I will investigate another method. For right now, looks like all of my short text value <= 80 characters. So, I'm think I'm good.

Again, I appreciate the continued assistance from all contributors.

Be blessed!!
Tom

[skydivetom]

Darkwind:

I just saw post #5. I will have to step away from my desk for a moment but I'll come back to it (testing) within 1-2 hours.

I'll provide feedback afterwards. Thank you for the alternative recommendation.

Be back shortly.

[skydivetom]

Darkwind:

With the modified routine, I'm now getting a compile error. Here's what I've done.

a. Per post #2, I only changed below in the ToFunction:

with: ToName = "CHAR"

... that code change worked w/o issues!

c. Per post #5, I replaced the ToFunction AND then replaced the sSQL line **ONLY** in the ELSE clause.

sSQLCreateTable = sSQLCreateTable & fld.Name & " " & ToName(fld.Type, fld.Size)

Now, however, when executing the TRIM function, I get a compile error in the **IF** clause. So, my string date/time stamp conversion fails.

I tried adding the ", fld.Size" into the **IF** clause but that one also fails.

From:
& ToName(dbTimeStamp) & ","
& ToName(dbInteger) & ","


To:
& ToName(dbTimeStamp, fld.Size) & ","
& ToName(dbInteger, fld.Size) & ","

Based on the nested IF/ELSE in the ToFunction, what else should be modified in the TRIM function? See attached DB containing the complete module.

Thanks,
Tom

[darkwind]

This will get it to run:

Private Function ToName(ByVal iType As DataTypeEnum, Optional iSize As Integer = 0)

Alternatively, you could change each instance of ToName() to include the fld.Size parameter. So, for example:

SQLCreateTable = sSQLCreateTable & fld.Name & " " & ToName(dbTimeStamp, fld.Size) & ","
sSQLCreateTable = sSQLCreateTable & fld.Name & "Ms " & ToName(dbInteger, fld.Size) & ","

Doesn't really matter which.

[skydivetom]

FANTASTIC!!! That did the trick:

- All existing .log files (imported in DB) currently contain string values <= 80 characters.
- Thus, the modified TRIM routine re-creates TEMP tables in permanent tables with the correct (preferred) data type = "short text".
- In the event that *future* .log files (with short text and values >= 128 but <= 255), I will "cross that bridge" then.

So, for now, I think I'm good w/ the parsing, cleansing, etc. routine.

Darkwind/Orange -- I very much appreciate the continued assistance.

[skydivetom]

Update... I had to change to following from CHAR to VARCHAR... otherwise, the trim function did not execute properly and included the trailing characters.

            If lSize > 255 Then
                ToName = "TEXT"             'Will result field being stored as "Long Text"
            Else
                ToName = "VARCHAR"          'Will result field being stored as "Short Text" (see note/issue in comments box above)
            End If

[accesstos]

Tom,

Before you come back with a new TRIM thread, and before you create your own "TRIM TEAM", please give a try to the function below with an original log file:

Function accSysLogs(ByVal strLogFile As String) As Long
    'Prepares the text from strLogFile to import in CurrentDB as CSV file.
    Dim strSQL As String, strTable As String, strRet As String
    Dim db As DAO.Database
    Dim RegX As Object
    Dim ff As Long

    If Len(Dir(strLogFile)) > 0 Then
        On Error Resume Next
        ff = FreeFile
        Open strLogFile For Input As #ff
        strRet = Input(LOF(ff), ff)
        Close #ff
        If Len(strRet) Then
            Set RegX = CreateObject("VBScript.RegExp")
            With RegX
                .Global = True
                'Remove useless parts.
                .Pattern = "(<.*\n*)|\[ |((\n.*)$)"
                strRet = .Replace(strRet, "")
                'Separate any first log line in "fields" with commas
                '(also explode time in its parts)
                .Pattern = " \$ *| *\]\n|((?!\d):(?=\d))| +(?=.*\$.*\:)"
                strRet = .Replace(strRet, ",")
                'Remove last "new line(s)" if exists
                .Pattern = "\n*$"
                strRet = .Replace(strRet, "")
                'Add lines
                .Pattern = "\n"
                strRet = .Replace(strRet, vbCrLf)
                .Pattern = "\w,"        'Check pattern for comma(s)
            End With

            If RegX.test(strRet) Then        'There is a comma.
                'Create temp file to import.
                ff = FreeFile
                Open CurrentProject.Path & "\tempSF.txt" For Output As #ff
                Print #ff, strRet
                Close #ff
                If Err = 0 Then
                    strLogFile = Mid$(strLogFile, InStrRev(strLogFile, "\") + 1)
                    strTable = "tblSysLog_" & Left$(strLogFile, Len(strLogFile) - 4)
                    'Import sys log records.
                    strSQL = "SELECT * INTO " & strTable _
                             & " FROM [Text;HDR=NO;FMT=Delimited;Database=" _
                             & CurrentProject.Path & "\].tempSF.txt"
                    Set db = CurrentDb
                    db.Execute "DROP TABLE " & strTable
                    db.Execute strSQL
                    db.TableDefs.Refresh
                    accSysLogs = db.RecordsAffected
                    Set db = Nothing
                End If
            End If
        End If
        Set RegX = Nothing
        If (Err <> 0) And (Err <> 3376) Then Debug.Print "Import " _
           & strLogFile & " ERROR #" & Err & " " & Err.Description
        On Error GoTo 0
    End If
End Function

For example, in immediate window, type:

?accSysLogs("C:\???\ac.log")

(replace the ??? with the actual path of sysLogs folder)
and hit Enter.

After that, open the query below:

SELECT [F1], 
Format([F2]+TimeSerial([f3],[f4],[f5])+([f6]/84600000),"yyyy/mm/dd hh:nn:ss:") & Format([f6],"000") AS DATETIMESTAMP, 
([F2]+TimeSerial([f3],[f4],[f5])+([f6]/84600000)) AS DTM, F7, F8, F9, F10, F11 
FROM tblSysLog_ac;

and let us know if the results are the desired.

Cheers,
John

[skydivetom]

John -- thanks... I'm away from my desk right now but I'll check it out later on.

Sorry for asking a fundamental question, but how do I need to call the function from a forms' command button?

The following won't work since it's not a Public Sub, right?

In form:

Private Sub cmdImportSyslogs_Click()
                              
    Call ImportSyslogs
               
End Sub

In module:

Public Sub ImportSyslogs()

'... which is different than **Function ImportSyslogs(ByVal strLogFile As String) As Long**

'... some code

End Sub

Sorry for asking if you consider it a silly question. Just wanna give your code a shot later on/tomorrow morning.

Thanks!

P.S. Not trying to create a "Trim Team"

[accesstos]

Hi Tom,

If you don't want the returned value of a function, you can call it as a subroutine:

Private Sub cmdImportSyslogs_Click()
     accSysLogs "C:\Sys_Logs\ac.log"
End Sub

However, in most cases, we call functions to get a value:

Dim n as Long

n = accSysLogs("C:\Sys_Logs\ac.log")

Msgbox n & " sys logs were imported."

Take a look at the attached sample with a slightly improved version of accSysLogs(). The queries provides the data (datetimestamp) in useful and readable format.

[skydivetom]

John:

Wow... this is a very much improved solution to the data import routine. I am extremely impressed w/ this much improved IMPORT routine. Allow me to summarize comments, questions, and *potential* change recommendation in bullet format.

COMMENTS:
- This data import routine is *much faster* compared to my original import routine. For example, in my existing (soon to be "retired" version), the importing of, e.g., 3,680 records took approximately 45-55 seconds. In contrast, your version processes the same amount of records/message between 2-4 seconds. This performance improvement is amazing!!
- Compared to my existing import routine, the fields are do NOT have any trailing characters. As you previously pointed out, this mitigates the requirement for trimming the data.
- At first glance, I thought "Oh, oh, the date/time column only stores the date!" But then I noticed that you split hh:nn:ss:millisecs into fields [F3:F6]. It certainly looks much cleaner. I will have to "play" as to how calculations between 2 messages can be done but I'm sure that shouldn't be a problem. Speaking of "problem"... there is one slight one in here that requires adjustment. I'll get back to it under "change recommendations".

... so, overall, this import function looks much cleaner and is definitely significantly faster than the original import routine. That's awesome!


QUESTIONS:
- After each import process, file "tempSF" is generated in the folder where the DB resides. What is the purpose of this temp file? Do I need to keep it or does it contain some valuable info?
- Your DB includes two (2) queries: "qryDTMac" and "qryDTMps". What is their purpose? Do I need to keep these 2 queries (i.e., are they part of the import function)?


CHANGE RECOMMENDATIONS (nice-to-have):
- Some log files are empty. That is, although they contain data, they do NOT contain any *actual* messages. Please refer to sample log file "other.log".
- Thus, upon importing the sample data, 3 tables are successfully loaded into the database. Great!
- However, the "Status" message box indicates that 4 tables were loaded. Naturally, I understand as to why it says "4", but is there any way the message box could indicate that either a) "3 files were successfully processed" or b) "3 out of 4 files were successfully processed." Again, this is only a nice-to-have.


CHANGE RECOMMENDATIONS (important adjustment):
- The data import routine processes the content of the .log file regardless of the .log file containing invalid dates.
- Per the other post Date issue where *string* (hh:nn) equals "23:54" (vs. "00:54" or 12:54 AM) (accessforums.net), a subset of records include invalid dates (e.g., "24:19:45:745").
- As we previously established, there's no such date. Thus, in my existing process, I execute a ForLoop that updates all records (in all imported tables) based on the following business rule:

            'An invalid date of, e.g, "2018/10/31 24:35:06:112" will be updated to "2018/11/01 00:35:06:112" (i.e., plus 1 day AND minus 24 hours)
            strSQL = "UPDATE " & t.Name & " SET " & t.Name & ".FIELD2 = " & _
                     "Format(DateValue(Left([FIELD2],10))+TimeSerial(Mid([FIELD2],12,2),Mid([FIELD2],15,2),Mid([FIELD2],18,2))," & _
                     "'yyyy/mm/dd hh:nn:ss:' & Mid([FIELD2],21,3));"

- Naturally, the above won't work any longer given that invalid hours/minutes are now stored separately in fields [F3] AND [F4]. I have attached a DB containing a few sample records containing both valid and invalid dates.
- My question: How would your business rule have to be changed so that invalid dates are fixed based on this new table format? In a "perfect world", the import routine would already include the business rule and I would never end up with erroneous/invalid hh:mm. But, as long as I have a method to immediately execute/update the tables upon import, that certainly would work, too.