Solved: Access txt import alterations

[chris_uk_lad]

I have a delimited text file that i need to make some alterations to before importing into an access table but theres too many records to do manually. Was hoping you can assist on a way to edit the txt file via a VBA procedure.

The way i want to edit is by changing the lengths of the columns. So for the second column i want to truncate start 22 (length 45) to 26 characters by removing start 44 for a length of 21.

Column value example:

08765653452WK433424_________________
(__ representing spaces in that value that i want to remove). I know theres the find / replace method in Access but i dont want to remove all spaces, just restrict the cell value.

Another method i need for another cell is:
Where start (length 2) is AA,
Truncate the start 91 (length 40) to 20 characters by removing start 111 for a length of 20.

Many Thanks for any assistance.

[OBP]

This is best done After you have Imported the data in to Access using either queries or VBA.

[chris_uk_lad]

Hi, not able to do that, my mistake though, its actually a Fixed Width file, so some of the fields are mushed together without a space making it difficult to export into access http://www.access-programmers.co.uk/forums/images/smilies/frown.gif. I do know the length of each field though so need to somehow import in based on these lengths, then change to new lengths, in esence, whether query or macro.

[Mavyak]

Can you:
1. Create the table structure you need.
2. Create a table structure that suits the current file as is.
3. Import to the suitable table.
4. Write an append query that appends the data to the table you need?

[chris_uk_lad]

Sorry for late response, solved it by writing straight to a dif text file :D

[Mavyak]

Thanks for the follow-up. Please mark the thread as solved.