span8
span4
In this article,我们将执行一个简单的转换;converting a dataset from CSV to Microsoft Excel format.We will also demonstrate how to set date formats,and to split string data into separate fields,使用日期格式化程序和属性垃圾转换器。
Use the FME Data Inspector to view the cellular tower information for this example.You will see that the dataset includes the location,quality,检验日期,and a unique identifier ("Code") for all cell towers.由于此信息是以csv格式提供的,our job will be to translate it into a Microsoft Excel format.It is important to ensure that the Excel data includes a valid date format,我们还需要将唯一的标识符代码分割成正确的格式;with the three leading characters,and the remaining trailing characters,each stored in their own attribute field.
拉链 (包含已完成的工作区和数据)
1。生成新工作区
Open up FME Workbench and generate a new workspace.对于读者来说,set the format to CSV,and select the attached CellSignals.csv file.For the writer,choose Microsoft Excel,and select a location and name to save the new file.
2.Format the date with the DateFormatter transformer
FME will read the Date format from the CSV as a string type,so let's do some quick formatting to ensure that it is stored as a date,and write the output using an ISO date specification.将datetimeconverter转换器添加到画布,并将其连接到读卡器和编写器之间。
Open the DateTimeConverter parameters,and set the date fields to "recorded_tstamp",which is the name of the field containing the date information.将源日期格式设置为%e/%n/%y%k:%m。然后使用目标日期格式旁边的下拉菜单将其设置为:ISO日期(%y-%m-%d)。
在DateTimeConverter中,set the Input Format to %e/%N/%Y %k:%M and the Output Format to %m-%d-%Y
三。Split fields (attributes) using the AttributeSplitter transformer
In order to split the leading and trailing characters on the unique Identifier attribute,we will use an AttributeSplitter transformer.Add an AttributeSplitter after the DateTimeConverter and open its parameters.
This will split the field (attribute) into the leading 3 characters,and the trailing characters,and populate them in a list array.
使用格式字符串3s15s拆分属性
4.Set Fields (Attributes) using the AttributeManager Transformer
We now need to clean up the names for the attributes created while using the AttributeSplitter transformer.To do so,添加一个属性管理器转换器,并将其连接到属性页。Let's assign new names for the values we will be reading from the list array.要做到这一点,go the "Attribute Value" column and from the Attribute Value selector,choose "list{}".You will be prompted to select a list element.Enter 0,to create the attribute value "_list{0}",then assign _list{0} the Output Attribute name "ID".Enter 1,要创建属性值“list 1”,then assign _list{1} the Output Attribute name "Number".
Use the AttributeManager to create new attributes using _list{}
5.Set the writer parameters
下一步,Set up the writer so that it creates a new worksheet for each individual ID that appears in our dataset.For example;原始csv文件中使用了4个ID代码:abc,ABD,TXU,and TXV.We can create new worksheets for each by using a feature type fanout on the writer.要做到这一点,open the writer parameters for the Sheet Name enter @Value(ID)
6。Add newly created fields to the writer
Finally,we need to update the writer to include the two new attributes we created.In the User Parameters tab,单击自动,you will see the new attributes populate into the list.切换回手动,and change the ID data type to String,then change the Number attribute's data type to number and finally,change the record_tstamp data type to date.
7。Run the workspace
打开包含文件夹并检查新创建的Excel文件。您将找到“id”的新格式化字段,"Number",和“记录放大器”。
Output in Excel with multiple sheets named with the ID
Data Attribution
此处使用的数据源于温哥华市,British Columbia.It contains information licensed under the Open Government License - Vancouver.
© 2019 亚搏在线Safe Software Inc |Legal