Import multiple (changing) text files

[cabsandy]

All,

Sorry if the title seems a bit confusing and hopefully I have it in the right forum-if not, please feel free to move/suggest.

I'm not looking to be spoon fed on this one, but to explore if the concept is viable.I have attached 3 files, 2 text files of the same type, and my miserable attempt at what I want to do in Excel

Here's the idea-I'm puling stats off some routers that show errors on customer devices-these are the text files. These can be pulled every hour or as frequently as we like.

The text files are repeating with 4 rows for each device

SID

Good Codewords

Correctable Codewords

UnCorrectable Codewords

The idea is to have a running total of the rows with the words "Codewords" in them. What I hope to achieve is to identify those that have the most errors (these would be the two marked Correctable and UnCorrectable), and do a delta, from the first set of records, to the most recent.A rolling delta, if you like. The main identifer is the SID row-this is the number by which we identify the erroring device. As can be seen by the xls file, I can do this manually but its a pain to cut and paste every time we get a dump from the router. The idea is also hampered by one major flaw which kicks Excel into touch-the SID row.The routers have new devices that come online, so the text files can change, with the SID row changing-by that, I mean the SID number you see on row 300 might be on row 304 on th next update because a new device has come online.So I need some way to "glue" the files together by the SID, then do the maths, and finally present it in a meaningful format.Plus have the ability (through a script I guess?) to continually import new text files and update.

Is it possible-if so, I'd love a steer in the right direction and then I can crack on

thanks

cab

cpe_fec_25th_Aug.zip

[comment]

The first thing would be to bring the data into Filemaker in the correct form - meaning that every 4 rows would add up to one record with 4 fields. The rest, I think, should be rather easy.

You need to import the data into a temp table, then parse it out to "real" records. This can be scripted. Are we really talking about version 6?

[Fenton]

Do you want ONLY a single record for a unique SID? That is, if 2 text files have the same SID, do you only want to store the last one imported? Or do you want a record for each occurrence of a SID? Because if the later, either your text files or the script is going to want to timestamp the records, so you can tell them apart.

[cabsandy]

Hi guys,

Cheers for the replies-firstly I'm on FM 8.5.I noticed I hadn't updated my profile-which I'm trying to find now! I'm sure I put the info on the boxes when I posted but it's definitely 8.5 .

On the first point of getting the data in, is this the same idea of the transpose function in Excel? So rather than having

SID

Good Codewords

Correctable Codewords

UnCorrectable Codewords

it would be

SID Good C Corr C UnCorr C ??

That makes sense-I'd done a few scripts in FM so I'll have a go at that-cheers

On Fenton's point-yes-it's a good question.If I keep them all, I'm going to have a huge bank of records when I view it in Excel, which is not desirable.Do you think there could be a compromise where I only keep the last 3 sets of text files? That way, I get the data and have a confidence factor that the data is coming in in the right order.

cheers

cab

[comment]

is this the same idea of the transpose function in Excel?

The idea is to have the same result, even if the process is entirely different. But once you have that, you can group your data by SID and produce any stats you want.

As Fenton says, it's important to mark each import with some sort of batch ID/timestamp.

[Fenton]

Here's my attempt at breaking the text import into proper records and fields.

cpe_fej.fp7.zip

[Lee Smith]

Hi guys,

Cheers for the replies-firstly I'm on FM 8.5.I noticed I hadn't updated my profile-which I'm trying to find now!

[color:blue]MY PROFILE >> [color:blue]Control Panel >>[color:red]FileMaker Questions ...

HTH

Lee

[cabsandy]

Lee,

Thanks for that -)

Fenton-many thanks for the file-I'm now going to go away and see what I can come up.No doubt I'll be back asking more questions!

cheers

cab