Converting a text file to Access 2003

Topic

New Reply

I have a text file (see attached) that I need to convert to a meaningful table in Access 2003. Before I accept the project, I would like to know if it is possible to loop through the imported table and get the data into a format that users can read.
Instead of all data in one column, I need the data to be shown in multiple fields and records.
I am hoping somebody can point me in the right direction.
Thanks a lot

Reply | Quote

Replies

Most of the text file has a consistent structure:
– If the first two or three characters on a line aren’t spaces, they indicate a field name.
– Text from position 5 on the line forms the field value, together with the text on subsequent lines that start with spaces.
– A line that only consists of three spaces indicates that the next line starts a new record.
It’s possible to write code to convert this to a table with fields L22, AN, TI, IN etc.

But there are lines with a completely different structure, such as

Code:

FAN.CNT 1
     PATENT NO.          KIND   DATE        APPLICATION NO.        DATE
     ---------------     ----   --------    --------------------   --------
PI   US 7594994           B1    20090929    US 2005-244589         20051006
PRAI US 2005-244589             20051006     
ASSIGNMENT HISTORY FOR US PATENT AVAILABLE IN LSUS DISPLAY FORMAT

How should these be converted?

Reply | Quote

Most of the text file has a consistent structure:
– If the first two or three characters on a line aren’t spaces, they indicate a field name.
– Text from position 5 on the line forms the field value, together with the text on subsequent lines that start with spaces.
– A line that only consists of three spaces indicates that the next line starts a new record.
It’s possible to write code to convert this to a table with fields L22, AN, TI, IN etc.

But there are lines with a completely different structure, such as

Code:

FAN.CNT 1
     PATENT NO.          KIND   DATE        APPLICATION NO.        DATE
     ---------------     ----   --------    --------------------   --------
PI   US 7594994           B1    20090929    US 2005-244589         20051006
PRAI US 2005-244589             20051006     
ASSIGNMENT HISTORY FOR US PATENT AVAILABLE IN LSUS DISPLAY FORMAT

How should these be converted?

Thanks, Hans
I am not sure where to go from here, but have some ideas after reading your reply. I will play with this and see if I have any more questions.

Reply | Quote