Jenkins
asked on
Import selected text file records into Access
I need to import into an Access table selected segments of selected data records that are saved in a text file.The text file has a lot of garbage records and data within each record not useful to me that I need to skip over.
In plain English, this is what I need to accomplish:
1. Read the text file from top to bottom
2. If bytes 1 thru 9 = 'Marketing', then import bytes 1 thru 9 of that record into Field1 of the table and bytes 30 thru 40 of the same record into Field2 of the table. Otherwise skip the record.
Thank you.
In plain English, this is what I need to accomplish:
1. Read the text file from top to bottom
2. If bytes 1 thru 9 = 'Marketing', then import bytes 1 thru 9 of that record into Field1 of the table and bytes 30 thru 40 of the same record into Field2 of the table. Otherwise skip the record.
Thank you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
By the way, I had already tried importing the file directly into a new Access table for it to act as a staging area for further processing. The problem I had with that method is that in my source file, there is a debit column and a credit column. When I import the text file, both of those columns wind up being combined into one field in Access instead of staying separate. I can import the file into Excel first but I wind up having to move the verticle lines around manually in the Text Import Wizard window to get them where I need to be. I'm trying to automate the process as much as possible so that I don't always have to deal with manually moving those lines around before importing.
There is no easy way to import a file with inconsistent row formats which is what it sounds like you have. If you have debit and credit fields on different rows, your code will need to combine them into one row as you are looping through the import file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER