Import multiple (changing) text files

August 25, 200817 yr

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

August 25, 200817 yr

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?

August 25, 200817 yr

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.

August 25, 200817 yr

Author

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

August 25, 200817 yr

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.