Changing .txtfile To Another Format

[AccessTexas]

We have a year end inventory where we want to take our scanned data in a .txt table in Access and change the data to match a 50 character format that will upload directly into our count file and require no manual keying.

There are two variable fields that would need differing leading zeroes inserted, otherwise the rest would be fixed. I've attached the practice file with two records in the proper format. There is a blank in position 40 and a blank at the end to make the 50 digits.

I'm not sure where to begin or if it is possible but if you could offer any direction I would appreciate it greatly.

While waiting for a reply I did a query from the table and put in a SQL that looks like it should work. If anyone has worked on a similar project take a look and see if this data would work to upload.

 

[Galaxiom]

It would be done in VBA.

I have not looked at the database but basically you need to find the length of each record using the Len() function then concatenate the extra character in a loop by the number of times the length is short of the target number of characters.

There would be some logic in the loop to insert the blanks as required. Check the ASCII of the "blank" to be sure you are using the right one as they are not necessarily spaces.

The processing could even be done without ever putting the data into a table by working with the TextStreamObject. Use one TSO for the input and another for the output.

 

[AccessTexas]

Thank you.