Need to trim *all fields* across *all tables*

[darkwind]

Darkwind:

thank you for the continued assistance. I appreciate it.

Ok, I added the rs.Close line but now get a different error (3144). Am I still missing something else?

Same bug, different implementation... we need a conditional so the trim query is only executed when there's something to trim...

    Dim bTrimData As Boolean

...

                     If fld.Type = dbText Then
                        sSQLCreateTable = sSQLCreateTable & " WITH COMPRESSION,"
                        sSQLTrimData = sSQLTrimData & "[" & fld.Name & "] = Trim([" & fld.Name & "]),"
                        bTrimData = True
                     Else

...

               'Trim
               If bTrimData Then
                  Debug.Print sSQLTrimData
                  db.Execute sSQLTrimData, dbFailOnError
                  bTrimData = False
               End If

Not to muddy the waters, but I'm curious as to the bloat created by creating and deleting the tables.

I would normally be importing the data to a temp table in a temp database and then clean it up when appending to my real tables.


When done I then delete the temp database.

Yeah, deleting and recreating a ton of tables this way is suboptimal, at the least. I would certainly run a compact/repair once done with a big batch of this kind of thing. Overall this solution is fairly messy. If it were my project, I would just fix my original data import to construct the requisite tables WITH COMPRESSION before initial import and split out the date/time in the process. That would mean just creating the tables once, no need for this copy/trim/delete. But that's technically out-of-scope of the request... and at the time I started looking into this thread, I wasn't even aware these were imported tables to begin with.

[skydivetom]

Moke123:

That's exactly what we're doing here:
1. [tbl_One] and [tbl_Two] are the *temp* tables into which data is imported.
2. Darkwind's TRIM function performs the cleanup and
3. Creates the new/real tables...
4. Lastly, temp tables are deleted and real tables are renamed.

From my perspective, the previous version (my actual app has a different table naming convention), it works BRILLIANTLY.

The only issue we're now dealing with is the creation of "milli-seconds". Well, the process already creates the real tables with milli-seconds.

Now, we're just left w/ the "cleanup".

[skydivetom]

darkwind:

Sorry, you lost me on your last post wrt the " bTrimData = True" and the next Trim lines.

Kindly request to post the entire DB. Is it working for you now?

Thank you,
Tom

[darkwind]

Here's the full file.

[skydivetom]

Darkwind:

This last solution is BRILLIANT!!! Thank you sooo very, very much for assisting me in this one.

I'll review the code in more detail and attempt to add some comments for the high-level procedures.

Again, thanks!!!

Tom

[skydivetom]

@Darkwind:

In one of your previous posts, you mentioned there was an issue (that you noticed) w/ the erroneous hh:nn:ss:mls of, e.g., 24:54:30:949.

In the latest version, when running the TRIM function, this issue now makes the function fail. It's NOT your function but unclean data on my end.

I have posted a new thread pertaining to identifying a solution which would clean the erroneous data BEFORE executing the TRIM function.

https://www.accessforums.net/showthr...401#post502401

'Thought you might be interested given you have familiarity w/ the function and the underlying data issue. Thanks!

[accesstos]

Please forgive me if I missed something but, I think that this thread would not have been created if, in the data entry routine of OP, the VARCHAR data type had been used instead of CHAR.
Nevertheless, it was an occasion to learn something more and become wiser.

Tom,

Did you try to run the import routine with VARCHAR data type?

Sub ImportFiles(myDir)

    ...
                For i = 0 To UBound(y)
                    s = s & IIf(s <> "", ", ", "") & "[FIELD" & i + 1 & "] VARCHAR(80)"
                Next

    ...
    
End Sub

In my case, using VARCHAR in place of CHAR, the created tables appears with the data trimmed. So, no need to farther trimming and "magic" properties/technics.

[skydivetom]

John:

thanks for chiming in. Upon the data import, the date/time + milliseconds are stored as text. Not only does the TRIM function removes the trailing characters, it also converts the text string (date/time + milli-secs) into date format + numeric format (for calculations).

So, @Darkwind's function accomplished both... removal of the trailing spaces and conversion into fields for calculation purposes.

As indicated, the only issue occurs when the raw data (for some reason) was entered/created erroneously. Thus, it brings me back to the new thread that I opened where invalided hh:nn need to be converted into the next day.

Hopefully I didn't misread your comment.

Thanks,
Tom

[moke123]

Hopefully I didn't misread your comment.

I think what AccessTOS and Darkwind are trying to say is that you should probably look back at your original import procedure
and avoid all the bandaids.

[accesstos]

I think what AccessTOS and Darkwind are trying to say is that you should probably look back at your original import procedure
and avoid all the bandaids.

Exactly!

I think that accidentally have been used the CHAR datatype at the beginning from the creator of the import procedure. Changing the CHAR data type to VARCHAR renders any later TRIM procedure useless. Moreover, CHAR data type provides fixed-length text fields. That's why the fixed length of 80 in all records. You can't trim a CHAR datatype field!

I don't want to belittle anyone's effort. I just want to say that, generally, when a solution leads to more problems than it solves, it is good to reconsider the solution.

[skydivetom]

Moke123 & John:

thank you for the additional, valuable information. It the problem persists, I will to have investigate another method. For now though, Darkwind's function does do the trimming and milli-seconds extraction.

Thanks,
EEH