Skip to main content
Solved

Rename CSV file before reading in data

  • February 12, 2020
  • 5 replies
  • 47 views
B
Forum|alt.badge.img
  • bi

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.

Best answer by david_r

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.

View original
Did this help you find an answer to your question?

5 replies

david_r
Celebrity
  • david_rCelebrity
  • February 12, 2020

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

david_r
Celebrity
  • david_rCelebrity
  • Best Answer
  • February 12, 2020

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
Forum|alt.badge.img
  • bi
  • Author
  • February 12, 2020
david_r wrote:

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
Celebrity
  • david_rCelebrity
  • February 12, 2020
bi wrote:

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
Safer
Forum|alt.badge.img+11
david_r wrote:

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


Helpful Members This Week

Community Stats

31,176
Posts
118,181
Replies
38,791
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.