Remove spaces from tab delimited text file
Thank you for taking the time to report an issue.
What's wrong... Please write below.
Basically I don't want to use the "saved import" option and run through a macro because the location of the file is going to constantly change.
The code will be run through a form button on-click command, then it will prompt to locate the file with a similar window to that of a "open" or "save as". Once the text file has been selected, it was delete the data currently in "tblImport" and then append the data from the selected tab delimited text file. Also, the text file has field headings.
I'm okay with parsing on tab character, chr(9), but I've hit problem if data record contains comma character. VBA INPUT verb appears to consider comma to be a variable delimiter.
The tab file is a delimited text file. It has never given me a problem,
Well I was going to show you an example, but apparently this forum automatically removes extra spaces, how coincidental, just what I'm looking for
So I tried to use the function Trim in Excel, but it was annoyingly troublesome with so many rows and columns, plus it didn't correct the actual columns, but instead displayed them in a new columns.
So I thought maybe it would be wiser to have Access remove the spaces. Of course with my limited knowledge of Access, I went to Google.
I found this script that looks like what I need exactly, I just don't know how to execute or apply it to my database, specifically to those four fields.
I have it as a file, trmStringExample.mdb and also in text format as I have shown below.
I have tried to use the import wizard and saving it spec then calling it using vba command however this seems to use the field width of the 1st few record/lines of the text which do notrepresent the full width of records.
An error occurred trying to import file [blah]. The file was not imported.
I imported this exact file yesterday without a problem. I was importing it again to overwrite the first one because I wanted to change some of the Long Integer fields to Text.
so I tried to just import the file again from scratch, not changing anything (leaving the Long Integer fields as theyare), but I still get this error.
I want to loop through a directory using VBA and create new tables in Access for each text file.
Right now, my VBA shell looks like this,
When I use Access's import wizard and step through (Delimited >> Delimiter = Comma, Qualifier = " >> Finish), my file imports without errors.
I save an import specification with those settings (Delimited, Delimiter = Comma, Qualifier = ") and re-import the file to verify the specification. Using the wizard, (Advanced, Specification = "Import" >> OK >> Finish). My file imports without errors.
But when I run the shell above, my file imports with 279 Type Conversion errors.
This occurs whether the syntax that follows TransferText is acImport or acImportDelim.