Importing large text files that are fixed width

[cabsandy]

Hi,

Looking for some help-I have some large text files that have more than the Excel limited 65000 lines in them. The records use space to differentiate between the columns (9 colums in total-see below).I want to use FP to import them but I find I have to break them up, use excel to convert them to .csv and then import them. Is there anyway that I can import these files directly for onward use within FP?

Attached is a sample

Thanks in advance

Sandy

example of text.txt

[IdealData]

Your problem is the format of the source data, as it uses SPACE instead of TAB to delimit fields.

Could you use another text editor to replace the SPACE with TAB - then you're nearly there.

[Lee Smith]

I agree with Mark,

If all of the records are the same as as your attachment, it is a piece of cake to change the spaces to tab.

You can approach it two ways.

Start by replacing 5 spaces with a tab, then 4 spaces with a tab, then 3 etc.,

Or

Replace all spaces with a tab, then then replace 2 tabs with 1 tab, a few times until you only have one (1) tab between the fields.

If you are on a Mac, download the free version of BBEdit, it was made for thing like this. There is an equivalent windows, I believe it is TextPad.

HTH

Lee

[cabsandy]

Shucks-thought that might be the answer ;-) I thought I'd ask anyway in case I'd missed something. Thanks for the replys anyway guys

Cheers

Sandy

[Lee Smith]

Hi Sandy,

I just learn a new way of handling a situation like yours.

I made a text file of your attachment and then opened it in Excel. Excel has an option that says:

Fixed width - Fields are aligned in columns with spaces between each field.

Walla, it opened without any spaces and the information was separated into columns.

HTH

Lee

[cabsandy]

Lee,

Problem is if the file is over 65504 lines, which my files are, Excel stops loading at that point-big limitation in Excel I'm afraid.

I've got Textpad but cant find where the part is to covert my Space files to tab/csv format-any clues anybody?

Sandy

[cjaeger]

Hi Lee,

why use Excel?

It's soo easy to do in FileMaker:

import as tab-delimited into one field, named import.

Make all other fields use an auto-enter calculation as follows:

Trim(Middle(import, start at character, length of column))

for the last value use a length of 999999, this will give you all the remaining characters

[Lee Smith]

Hi Sandy,

Why don't you send me the file Zipped off list at my [email protected] email address, I would love to see if it would make a difference on a differenct system.

As for TextPad, you should be using the find and replace funtion.

HTH

Lee

[Lee Smith]

Hi Christian,

Wow, that's a second thing that I learned today. It works great with the sample sent. I'm can send a rouch demo file for Sandy if she wants it.

HTH

Lee

[cabsandy]

Christian, Lee,

First of all, Sandy in Scotland is a well known guys name-which I am but I'll forgive you once :-)))

I've seen a previous post of Christians's but I didn't think it would work due to the fact that my columns can have a different number of characters each time the text file is produced therfore this would be a problem?

I'm glad to be corrected

Sandy

[Lee Smith]

Hi Sandy,

I'm glad to be corrected too.

Lee

[-Queue-]

cabsandy said:

I'm glad to be corrected

Sounds like another candidate for the sadistic solution, if you were to ask me.

[cjaeger]

Hi, Sandy,

having looked at your example, you may be able to use the wordsmiddle() function to separate the text.

Another way is to just let a script determine theUnix logfiles always insert at least one space between columns, so this should be save if field import is indexed as ASCII.

you could also make the calculation more flexible by using globals to indicate the field lengths in the calculations.

[cabsandy]

Hi all,

Sorry to take so long but I managed to crack it with the help of Christian-changed the fields to Calculation and used a text editor to exactly calculate the position of characters, length etc (got a bit lazy:-)).

Works a treat-I can now import directly SPACE text files, without using Excel to convert first. Some of them are in excess of 500,000 lines. I've scripted the lot and saved them as templates.Another thing I learned was to keep some of them(fields) as Text but use the options/Calculate value-this allows me to manipulate the results which I couldn't do with the field set as Calculation. If anybody wants me to send them the templates for a nosey, email me at [email protected] (remove stickyourspam.)

Thanks for all the help

Sandy