Solved

Rename CSV file before reading in data

  • 12 February 2020
  • 5 replies
  • 7 views
B

I have a CSV file that is downloaded from a source that I cannot edit. The CSV that is exported contains a character ("?") in its file name which results in FME (I'm using 2019.2.3) failing to read the file. Ultimately this workspace will run on FME Server and will access this CSV from an email that it receives.

The filename is FILE_(?)_FILE.csv and this flags an error in FME Desktop (which I expect will be replicated on FME Server) on attempting to load the data into the workspace:

CSV reader: Failed to open file 'C:\\FILE_(?)_FILE.csv' for reading. Please ensure that the file exists and you have sufficient privileges to read it

It looks like FME is incorrectly interpreting the file name string and is obviously then unable to find it.

An obvious solution is to ask the end user to remove the "?" symbol from the file name before running the workspace but this is prone to error and I would much rather handle it in the FME workspace itself. Is this possible?

Thanks in advance.

icon

Best answer by david_r 12 February 2020, 10:14

View original

5 replies

david_r
Rank
Userlevel 4

Reproduced with FME 2019.2.2.0 (20200103 - Build 19817 - WIN64), both CSV and TEXT readers exhibit the same behavior.

david_r
Rank
Userlevel 4

A possible workaround is to use the following code (Python 3.x) in a PythonCreator:

import fmeobjects
import csv

class FeatureCreator(object):
    def __init__(self):
        self.inputfilename = FME_MacroValues['INPUT_CSV_FILE']
        self.csvdelimiter = ';' # Modify this as needed
        self.csvquotechar = '"' # Modify this as needed
        self.log = fmeobjects.FMELogFile()
        self.fieldnames = []
        
    def input(self, feature):
        pass
        
    def close(self):
        with open(self.inputfilename, 'r') as csvfile:
            csvreader = csv.reader(csvfile, 
                                   delimiter=self.csvdelimiter, 
                                   quotechar=self.csvquotechar)
            for n, row in enumerate(csvreader):
                if n == 0:
                    self.fieldnames = row
                    self.log.logMessageString("Attributes to expose in the PythonCreator:", 
                                              fmeobjects.FME_WARN)
                    for field in row:
                        self.log.logMessageString("    "+field, fmeobjects.FME_WARN)
                else:
                    feature = fmeobjects.FMEFeature()
                    for m, value in enumerate(row):
                        feature.setAttribute(self.fieldnames[m], value)
                    self.pyoutput(feature)

This replaces the CSV reader with the CSV module in the python library, it seems to be able to better handle these filenames. It assumes that the CSV filename is available in the published parameter INPUT_CSV_FILE and that the first line of the CSV contains the attribute names. If necessary, it's fairly easy to modify the script for use in a PythonCaller and to pass the CSV filename as an attribute.

Note that you will have to manually expose the attributes from the CSV file in the PythonCreator. When your run the workspace, it will output some warnings in the FME log (in blue) that shows which attributes to expose.

B

A possible workaround is to use the following code (Python 3.x) in a PythonCreator:

import fmeobjects
import csv

class FeatureCreator(object):
    def __init__(self):
        self.inputfilename = FME_MacroValues['INPUT_CSV_FILE']
        self.csvdelimiter = ';' # Modify this as needed
        self.csvquotechar = '"' # Modify this as needed
        self.log = fmeobjects.FMELogFile()
        self.fieldnames = []
        
    def input(self, feature):
        pass
        
    def close(self):
        with open(self.inputfilename, 'r') as csvfile:
            csvreader = csv.reader(csvfile, 
                                   delimiter=self.csvdelimiter, 
                                   quotechar=self.csvquotechar)
            for n, row in enumerate(csvreader):
                if n == 0:
                    self.fieldnames = row
                    self.log.logMessageString("Attributes to expose in the PythonCreator:", 
                                              fmeobjects.FME_WARN)
                    for field in row:
                        self.log.logMessageString("    "+field, fmeobjects.FME_WARN)
                else:
                    feature = fmeobjects.FMEFeature()
                    for m, value in enumerate(row):
                        feature.setAttribute(self.fieldnames[m], value)
                    self.pyoutput(feature)

This replaces the CSV reader with the CSV module in the python library, it seems to be able to better handle these filenames. It assumes that the CSV filename is available in the published parameter INPUT_CSV_FILE and that the first line of the CSV contains the attribute names. If necessary, it's fairly easy to modify the script for use in a PythonCaller and to pass the CSV filename as an attribute.

Note that you will have to manually expose the attributes from the CSV file in the PythonCreator. When your run the workspace, it will output some warnings in the FME log (in blue) that shows which attributes to expose.

This is fantastic, thank you @david_r.

Just for extra info, I also had an encoding error in the attribute names so I've just arbitrarily set the attribute names as Column_NUMBER:

feature.setAttribute('Column_' + str(m), value)

and then exposed the attributes of Column_0, Column_1 and Column_2. This makes it a bit more flexible if the column headers in the CSV change names.

Thank you very much for your help, it is greatly appreciated.

david_r
Rank
Userlevel 4

This is fantastic, thank you @david_r.

Just for extra info, I also had an encoding error in the attribute names so I've just arbitrarily set the attribute names as Column_NUMBER:

feature.setAttribute('Column_' + str(m), value)

and then exposed the attributes of Column_0, Column_1 and Column_2. This makes it a bit more flexible if the column headers in the CSV change names.

Thank you very much for your help, it is greatly appreciated.

Glad to hear the workaround was successful. I've flagged the issue with Safe, hopefully they'll take a closer look.

jovitaatsafe
Rank
Userlevel 1
Badge +11

Glad to hear the workaround was successful. I've flagged the issue with Safe, hopefully they'll take a closer look.

Thanks @david_r! I can reproduce the behavior mentioned and I've filed (FMEENGINE-63184) in our tracking system. I'll let you and @bi know as soon as the issue has been addressed.

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
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.