Importing fixed length text file

[josebetzy]

I would like to import a text file that is just over 6000 characters in length and needs to be imported into 526 columns. I can export to Excel 2007 and then import, but I was hoping to use FM directly. This file size is a standard record file from the government and their reference file tells us where the columns are in fixed length within this file.

FM does not handle fixed length as an import option and the government will not change to a CSV or delimited file format.

Any suggestions are welcomed.

[bcooney]

What delimits a record? With consistency, you can parse anything. You may need to import into a temp field, then use Set field and the text functions to "break" up the data into the appropriate fields.

[josebetzy]

The file is not delimited. We have to import from starting point to end point of the file. This establishes the field. Your idea of a temp file is excellent as then we could manage the data within FM.

If I understand correctly, import the total file into one field, all 6000 characters. Then break it up using Set field and text functions. Could you please place an example of say the following file.

1AU582124567Rogers

It should then look like: 1 AU 582124567 Rogers

Thanks.

[bcooney]

Here's a very simple example, that just counts characters and chops. You may need to use Position ( ) to "find" certain strings in the text block. Do they give you any reference points?

For instance, in your example there is the string "Rogers." However, if it could be any name, how will you know the start of the next block?

EDIT: Just read your first post, that columns are fixed width. So I am assuming that the column for Rogers is a fixed width. Then, it must be padded somehow, spaces?

ParseExample.fp7.zip

Edited March 31, 2010 by Guest

[josebetzy]

Thanks. There is a technical reference that tells us exactly the breakdown and what type of data it represents (date, number, text, etc.). It tells us where each resulting data starts and ends. Empty spaces are padded by spaces. It is just more work for us because the file is sent in that manner.

Your example helps quite a bit. Again thank you.

[bcooney]

Sounds like MiddleValues ( ) is your new best friend, lol. You might also check out Trim ( ), and GetAsDate ( ). Glad I could help.