Import / Export Data File Formats
- Morten Lindequist Køhler
Analytics
The import data is a flat table like data and can be specified as import data headers and import data values.
Import data headers – table column names.
Import data values – table row values, where each row corresponds to import item.
Example of import data structure:
Import data header 1 | Import data header 2 | … | Import data header N |
Item 1 value for header 1 | Item 1 value for header 2 | … | Item 1 value for header N |
Item 2 value for header 1 | Item 2 value for header 2 | … | Item 2 value for header N |
… | … | … | … |
Item M value for header 1 | Item M value for header 2 | … | Item M value for header N |
Almost all imports in Perfion use this data format. The exception is only the translation data import, where a special data format must be used. Refer to “Perfion Translations - XLIFF” manual for more information.
The names of data headers and data value types are defined by each type of import. Refer to a specific import manual for more information.
Export Data Format
The export data has the same data format as import data. Refer to Import Data Format for more information.
All exports in Perfion have equivalent imports and the exported data will be in the format which can be used for the import of the same data.
File Formats
It is possible to import/export data to/from Perfion in several different file formats:
File format | Import | Export |
Microsoft Excel 97/2003 or newer (.xlsx, .xls) | Yes | Yes |
XML (.xml) | Yes | Yes |
CSV (.csv) | Yes | No |
The files must be formatted in some special way in order to be imported to Perfion. Excel, XML and CSV import data content will have the same table like structure for all file formats (e.g. table column names and data values will be the same), but each file format will encapsulate the same data in different ways.
Example Data
In order to show how the import data looks like for all file formats, we will use the same data as shown in the table below:
ItemNumber | _Value_EN |
v0001 | Audi |
v0002 | BMW |
The data shown in the table is for feature data import, but the concept is the same with all types of imports and exports. In case of other type of import, the content of the table will be different, but the structure will be the same.
Supported characters
All importers will remove most of ASCII control characters, because they are not supported by Perfion and may cause various issues if not removed. The supported characters are:
x09 (TAB)
x0A (LF)
x0D (CR)
[x20 - xD7FF]
[xE000 - xFFFD]
[x10000 - x10FFFF]
Microsoft Excel File Format
When handling the data in Microsoft Excel file format the actual data handled by Perfion is in the first sheet in the spreadsheet.
The first row in the sheet is used for data headers and any following rows are used as data values.
It is important to ensure, that data types in the Excel sheet are correctly defined. It is especially important that numeric values do not contain string values and date values are formatted correctly and recognized by Excel as dates. The imported values will depend on the regional PC settings from which the import is performed.
It is recommended to close the spreadsheet in Excel application before loading it in Perfion. If the spreadsheet is opened in Excel application, then the file can be locked and not accessible by other applications, e.g. Perfion.
Example of import data in Excel format is shown in Figure 1.
NOTE: Avoid using formulas in Excel sheets for importing. Though some formulas are calculated correctly, other formulas (such as VLOOKUP) does not work properly. Use “Copy”, “Paste Special”, “Paste values” to convert formulas to values.
XML File Format
XML file should include XML schema.
The XML document structure is 100% identical to the XML representation of a standard serialized .NET DataSet.
Example of data in XML format.
<?xml version="1.0" standalone="yes"?>
<PerfionDataSet>
<xs:schema id="PerfionDataSet" xmlns="" xmlns:xs=" " xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="PerfionDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="PerfionTable">
<xs:complexType>
<xs:sequence>
<xs:element name="ItemNumber" type="xs:string" minOccurs="0" />
<xs:element name="_Value_en" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<PerfionTable>
<ItemNumber>v0001</ItemNumber>
<_Value_en>Audi</_Value_en>
</PerfionTable>
<PerfionTable>
<ItemNumber>v0002</ItemNumber>
<_Value_en>BMW</_Value_en>
</PerfionTable>
</PerfionDataSet>CSV File Format
CSV file should have data header line as the first row and data values starting from the second row.
Default CSV file parameters:
Separator: semicolon (;)
Qualifier: double quote (“)
NOTE: There is no possibility to change default CSV file parameters using GUI based import.
Example of data in CSV format.
Example of CSV file |
ItemNumber;_Value_en v0001;Audi v0002;BMW |
Custom Data and/or File Format
In case the data for import is in a data or file format which is not supported by Perfion, one can perform conversion from any other data format to the data format supported by Perfion using Actions. Refer to “Perfion Actions” manual for more information.