Split data of a text file into columns

Data values contained in a text file can be identified each time a certain character or string is encountered (delimited columns), or after each fixed number of characters (fixed-width columns).

On the File tab, in the Column split section, you can specify the method that the Data Import should use to split the data values into columns.

Figure 9: Column split section

• Delimiter

This option allows you to specify the delimiter used for splitting the loaded data into columns. When the data are loaded, the content is analyzed in search of a column delimiter, by looking for any of the following common values delimiters: comma ., space, tabulation ⇥, semicolon ;, and colon .. If the delimiters is not detected correctly, you have to manually specify the correct one. You can select the correct delimiter from the drop-down list. Figure 10: Common delimiters

You can specify a character or a string that is not in the list. Double-click the text field and type the character or string delimiter. Note Each time the Column split options are modified, the input file is automatically reloaded to take the new options into account. The Column split options are not used when importing data from Excel® files, so they are hidden.

When using delimiter split alternative, the additional option skip duplicates allows repeated delimiters to be combined. This option treats multiple delimiters as one. For example, the following two lines: Copy1:2:::3:4::5 6:7:8:9::10 ... are split into 5 columns if the Skip duplicates option is enabled:

... and in 8 columns if the option is disabled:

• Fixed-width

This option makes it possible to import files that contain data arranged in fixed-width columns, by specifying the number of characters in each column. For example, the following lines are delimited using a 5 characters column width value: CopySIZE 52362TYPE 00001VALUE9813624053736174924493462 SIZE 81492TYPE 00001VALUE3473617492412350317365328 SIZE 52022TYPE 00003VALUE9174917361749247347360539