Skip to main content
  • EN

I have a folder with multiple text files.

I need every file to be read and parsed, then I need them to be put into an excel file. for every file there needs to be a sheet. Noteworthy is that I need to be able to do this in different folders. I don't always know how many files there is but it can be everything from 1-1000 (very unlikely that it is 1000 though).

 

I can't seem to find a good way to do this. I think its best if I just show you an example of the text file, images below. FME might not be well suited for this, I know I can do this in VBA in Excel, But I really want to do this in FME because it is way easier to share it with colleagues and more reusable.

 

This is what I have, this is how one text file looks like:.gvr file1: I need the file name to be the sheet name.

2: This is meta data, I need this to also be in the sheet, or in a separate file but with the correct file namne.

3: This is the table with information that I also need.

 

So what I need is this. A clean table, and for every file in the folder, a sheet with the file name. Note that the metadata is not in the picture but would be needed as well. somewhatAny starting suggestions? I have a very messy workspace and I could share some things. But I think I mostly need to think about this in the right way first. Also I didn't really know where to put this question.

  1. I would expose the fme_dataset format attribute on the text file reader. This contains the path for the input text file. image
  2. Using a StringSearcher, use the regular expression ([^\\\\/]+$) to extract everything after the last slash in fme_dataset. Make the Matched Result parameter 'filename'image
  3. Then on your Excel writer in the parameters, set the Sheet Name to 'filename'. This should fanout the results of each text file into it's own tab.image

As for parsing the text file, this can be done with some AttributeSplitters, some List transformers, and a few AttributeManagers.

Parse_Text_File

If you're ever needing to read an unknown number of files within folders, use a Path Reader set to read all subfolders. That'll get you the filepaths to all the files, which you can then filter what you want, and simply pass them into a FeatureReader to read all of the files.

If you're ever needing to read an unknown number of files within folders, use a Path Reader set to read all subfolders. That'll get you the filepaths to all the files, which you can then filter what you want, and simply pass them into a FeatureReader to read all of the files.

That sounds like something I need! Will test this later today. Will the files be read sequentially? One for one?

That sounds like something I need! Will test this later today. Will the files be read sequentially? One for one?

A FeatureReader transformer will run/read once per feature that is input into it. In this use, there are multiple filepaths being input, so they will read sequentially.

Thank you guys so far. I've managed to get this output, but I'm not quite done yet

output1I need the headers from this to be in the output.

output2 

There's probably an easy way I'm not thinking of.

Right now what I'm doing is that I have two excel writers, the first one is writing the meta data (the two first rows), the second one is writing everything else (Table Values) to the same excel file. It's kind of like two tabels in one sheet.output3I know it's not ideal but right now I'm a bit stuck. I'm sorry for giving you guys an aneurysm looking at my illigal way of outputing this, but surely there has to be a better way to output this?

​

Edit: I also tried with a feature writer but with the exact same output result​

 

As for parsing the text file, this can be done with some AttributeSplitters, some List transformers, and a few AttributeManagers.

Parse_Text_File

This was a tremendous help, thank you. I rarely work with lists in FME, but maybe I should since I often get tasks similar to this. Parsing weird excel or text files and such.

As for parsing the text file, this can be done with some AttributeSplitters, some List transformers, and a few AttributeManagers.

Parse_Text_File

Thanks for the feedback.

Lists have great functionality, but can be a bit overwhelming at first.

Reply


Community Stats

30,162
Posts
114,512
Replies
37,820
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.