Trim a Field once a csv file has been uploaded to a table

[gmazza76]

Good afternoon,

I think (hope) this is a simple query.

I have created a Macro to pull data from a stored ".csv" file into a table already saved in Access, by re writing over the current data.
The issue is the file once its in could do with a column (or the whole) table trimming to make it easier for me to create queries as I currently have to have another column that trims a field.

Is there anyway I can add a line in to trim [Field3] before the "StatusMsg"?

Sub ImportCSV(TableName, SourceFilepath, ImportMethod As String)

    Dim StatusMsg As Variant
    
    StatusMsg = SysCmd(acSysCmdSetStatus, "Clearing " & TableName)
    DoCmd.RunSQL "DELETE * FROM " & TableName & ";"
    
    StatusMsg = SysCmd(acSysCmdSetStatus, "Updating " & TableName)
    DoCmd.TransferText acImportDelim, ImportMethod, TableName, SourceFilepath, True
        
    StatusMsg = SysCmd(acSysCmdClearStatus)
End Sub

Sorry if this is an easy one, but my brain is going to sleep after reading so much on it.

thanks in advance
Gavin

 

[ranman256]

run update query:
update table set [field] = Trim([field])

 

[gmazza76]

Sorry @ranman256,

Where would I put this as I get a compile error, expected End of Statement

    Dim StatusMsg As Variant
    
    StatusMsg = SysCmd(acSysCmdSetStatus, "Clearing " & TableName)
    DoCmd.RunSQL "DELETE * FROM " & TableName & ";"
    
    StatusMsg = SysCmd(acSysCmdSetStatus, "Updating " & TableName)
    DoCmd.TransferText acImportDelim, ImportMethod, TableName, SourceFilepath, True
    
    update EMPInfo_Cell set [EMP_ID] = Trim([EMP_ID])
    
    StatusMsg = SysCmd(acSysCmdClearStatus)
End Sub

thanks

 

[Micron]

I thought TransferText into Access would automatically trim spaces (at least trailing spaces)? If your spaces are wrapped within rtf or html characters, Trim won't work.
I believe the suggestion applies to running the update in Access, not in your transfer code. Otherwise, you'd need a reference to the Access library in your Excel project and attempt to do it from there. Much easier to open Access and run that query.

 

[ranman256]

don't put it in code, make a query, run the query.
docmd.openquery "quFixMyQry"

 

[Micron]

Appreciate the recognition of my post as a solution but I think ranman deserves it unless your issue had something to do with rtf formatting.

 

[gmazza76]

Apologies @Micron & @ranman256,

I did create a query to adjust the data as needed and as SQL it shows

SELECT TABLENAME.THISONE, Trim([THISONE]) AS [trimdetails] FROM TABLENAME;"

Can I add this into the current code above or would I need to add a column called [trimdetails] to the exisitng importing table?

thanks

 

[welshgasman]

No, follow ranmans code in post 2 and run that query where you have your update line.
However as that update line is sql and not vba, why not run it as you do the delete sql?

 

[gmazza76]

I wanted to do it as sql as I believe I can do multiple tables in 1 go instead of creating individual queries?
this would need to be done across 8 tables

 

[Micron]

" AS [trimdetails] " will create a column. However, that is just a select query - it won't actually change your data. You could use query design and change to update query to generate the update sql you need. Switch to sql view in the query then copy/paste and do as you did with the Delete in your posted code. Add that after the transfer line. Consider CurrentDb.Execute "DELETE * FROM " & TableName & ";", dbFailOnError to avoid the action warnings. You don't need the ; at the end of vba sql but it doesn't hurt to have it.