importing 4,000 fixed field length records - parsing - removingspaces - etc

[Mel]

Status:
- I have 4,000 records in a giant text file.
- Each record has about 30 fields
- The fields are fixed field length
- In the text file there is a Field Name followed by the Field Data
- There is no "end of record" character(s) between records.
- I think I can load this into Word and use "search & replace" to put
a CRLF at the end of each record.by searching for the first field name
and putting a CRLF in front of it.
- I think I can use search and replace to find each Field Name and
replace it with a comma.
- I may also be able to use search and replace to find "spaces" before
each comma and delete the extra spaces in each field... but may just
be able to import as fixed field length records.
- then save the Word file again as a text file so that I can then
import it into Access
My question:
1 - If I import into Access as fixed field length records... how can I
get the trailing spaces out of each field?
2 - Got any better idea's on any of this?

Thanks for any help.

 

[Albert D. Kallal]

Have you tried the fixed length import options?

I don't think there is a requirement for a record delimiter if you set
things to fixed length.

The data area(s) with the field names in the data can be skipped with the
fixed length wizard.

 

[Dad]

Status:
- I have 4,000 records in a giant text file.
- Each record has about 30 fields
- The fields are fixed field length
- In the text file there is a Field Name followed by the Field Data
- There is no "end of record" character(s) between records.
- I think I can load this into Word and use "search & replace" to put
a CRLF at the end of each record.by searching for the first field name
and putting a CRLF in front of it.
- I think I can use search and replace to find each Field Name and
replace it with a comma.
- I may also be able to use search and replace to find "spaces" before
each comma and delete the extra spaces in each field... but may just
be able to import as fixed field length records.
- then save the Word file again as a text file so that I can then
import it into Access
My question:
1 - If I import into Access as fixed field length records... how can I
get the trailing spaces out of each field?
2 - Got any better idea's on any of this?

Thanks for any help.

 

[John W. Vinson]

Status:
- I have 4,000 records in a giant text file.
- Each record has about 30 fields
- The fields are fixed field length
- In the text file there is a Field Name followed by the Field Data
- There is no "end of record" character(s) between records.
- I think I can load this into Word and use "search & replace" to put
a CRLF at the end of each record.by searching for the first field name
and putting a CRLF in front of it.
- I think I can use search and replace to find each Field Name and
replace it with a comma.
- I may also be able to use search and replace to find "spaces" before
each comma and delete the extra spaces in each field... but may just
be able to import as fixed field length records.
- then save the Word file again as a text file so that I can then
import it into Access
My question:
1 - If I import into Access as fixed field length records... how can I
get the trailing spaces out of each field?
2 - Got any better idea's on any of this?

Thanks for any help.

In addition to Albert's good advice, be aware that Access trims trailing
blanks automatically (it's actually rather difficult to enter a field and keep
the trailing blanks). The Import Wizard will probably make going to Word
unnecessary.