Link to home
Create AccountLog in
Avatar of Stephen Roesner
Stephen RoesnerFlag for United States of America

asked on

Cleaning Text File

I have a fixed delimited text file that I would like to import into access. The problem is that the record length is 1225 and it seems that is too large for access to import. When I try to do it manually I get a corrupt record. I only need the first 305 characters since that is where the data is and will never be beyond 305. Is there a way to go into the file say maybe using filesystem object or any way for that matter to delete the empty spaces so I could then import the txt file.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

you can open the text file, grab the first 305 characters and write to a new text file.

or open the text file grab the first 305 characters and store it to the Access table.

<The problem is that the record length is 1225 and it seems that is too large for access to import.>

how does this text file look like. upload a copy of the text file
so one of the delimited fields is 1225 characters long?  

to import the entire field you will need to change the corresponding field type in the table you are importing to,  to a Memo type.

For things like this I generally create a table I am going to use in the database, and a table to import to and clean data from.  So if you have tbl_MyData as the table the system was going to use, I'd create tbl_MyData_temp with appropriate field specs to accept all the data from your text file.  Once imported, you can then clean the data and transfer it to tbl_MyData.

Avatar of Stephen Roesner


Cap your solution is what I am looking for - give me a day or two to upload the file - it contain sensitive data SSN's and such so I will grab a few lines and 1's and x's the sensitive stuff. How would you grab just 305 chars anyway?
you can import the whole record but you're going to have to chunk it up; distribute your import first 205 characters into a column, next 100 characters into next column, then add columns to fill. is one column 305 characters? you append the two columns into a memo, or slice and dice into multiple cols, or better yet use sql server
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
if you text file is always going to be in the same format and can contain any number of records, then I still believe importing as is and then dealing with the data is going to be the most efficient means of managing the data.

Reading external text files with string manipulation code is, in my humble experience & probably challengable experience, less efficient and 'tricky'

What I described above is very simple.  Import the entire file once manually to an import table designed to accept the data whatever it may be. When you run through the import wizzard, save the Import Specification with a logical name.

Then your simple coded proceedure would be:
a) Import the a new raw file to temp table
b) Execute a query or set of queries to chop & cut up the data and transfer to the final table
c) Remove any residual undeeded information from the temp table
Gentlemen I appreciate your suggestions and help but no matter how I tried to import - using import spec - adding fields to encompass space - it continuously came in corrupted.
However Cap you are just a genius!!!! I poped your code in thinkin yea this won't work and bam it created a perfect file that my import with spec divided it right up to the correct fields. If I could give you a million points I would. This finally breaks the limit I have had in my new job where they have been using this stupid program called monarch using mods to break it apart and then put in excel. Now I can just download from the mainframe and suck it into access like it should be done. I can't hank you enough.
This guy is a genius! I can't tell you how this has helped me!!!!