Profile manager

The Profile manager can be used to edit the conversion parameters that are part of a conversion profile.

Profile manager

A conversion profile is a collection of settings used to determine the parameters and rules that are applied during the conversion. It is possible to manually create and edit a profile (*.xcp file) but it is recommended to use the Profile manager for this purpose.

Toolbar

The toolbar provides access to the most common functions related to the conversion.

Profile manager - Toolbar

Listed in following order:

  • Save profile
  • Cancel
  • Validate profile

Save profile

Clicking on this option Save saves the currently selected profile to disk.

If the profile is not yet saved, the user will be able to select the location for the profile and the filename. If the profile has already been saved, it will be overwritten. It’s also possible to save a profile to a new file by clicking on the dropdown arrow next to the save icon and selecting save as….

Cancel

This cancels the profile edit. All changes since the last save will be discarded.

Canceling a profile edit will return to the conversion setup page.

Validate profile

The profile validator is executed automatically while editing a profile.

  • Ok for a valid profile
  • Error for an invalid profile

It’s also possible to get more information about the errors by clicking on the Error icon.

While a profile is invalid, it cannot be saved.
To save, first resolve the profile issues.

Profile settings

The profile settings are listed at the left of the profile manager window. Clicking on a setting will refresh the right-side of the window with the content of the selected setting.

General

This setting page shows the general settings for a profile.

The Profile name is used to give the profile a custom name which is displayed elsewhere in the application (such as the profile list in the conversion setup page).
The Path shows the location where the profile is saved.
The Profile type identifies the type of profile.
The Version shows the version of the application with which the profile was created.
Finally the creation and modified date & time are also stored for future reference.

Directory

These settings relate to the destination directory.

Clean up destination folder

By checking this option, the destination folder will be emptied before the conversion starts. This will not delete the folder itself but wipes all content from this folder.

Use this option with care as it might also remove important files in any directory the destination is set to.

Copy source folder structure

Check this option if you want to maintain the source folder structure.

If checked, all subfolders in the source folder are copied into the destination folder. If not checked, all worksheets will be converted directly into the destination root folder.

Folders in the source directory that have no worksheets selected for conversion, shall not be copied into the destination folder.

There is a higher risk that output files will already exist during conversion of multiple sheets in case the option to re-create the source folder structure is set to ‘No’ as below example illustrates.

Examples

Suppose following workbook exists:
c:\source\subdir1\workbook.xls

If the option to copy the source folder is not checked, the output will be saved to:
c:\destination\workbook_sheet1.csv

If the option to copy the source folder is checked, the output will be saved to:
c:\destination\subdir1\workbook_sheet1.csv

Beware of the risk of creating duplicate files.
Suppose we have a workbook in following two locations:
c:\source\workbook.xls
c:\source\subdir1\workbook.xls

Both workbooks have a sheet called Sheet1.

If the option to copy the source folder is not checked, the output will be saved to:
c:\destination\workbook_Sheet1.csv
c:\destination\workbook_Sheet1.csv

This causes a file conflict which can be easily avoided by checking the option to copy the source folder structure.

Workbooks

It is possible to select the type(s) of workbook(s) that need to be selected for the conversion. By default, all supported workbooks will be selected. In version 2.x of Excel Converter, the NPOI engine is used and supports the following types.

Option Description
*.xls Excel 97-2003 workbooks
*.xlsx Excel 2007 (and later) workbooks
*.xlsm Macro-enabled Excel 2007 (and later) workbooks

In order for something to be selected for conversion, at least one of the workbook types above MUST be set to ‘Yes’.
In version 1.6.2, there is also support for *.xlsb.

Worksheets

This setting controls which worksheets are selected for the conversion. By default, all worksheets are selected.

Option Description
All All worksheets are selected
First Only the first sheet (by tab order) is selected
Filtered The selection of a workbook is based on filters

Inclusion & exclusion filter

In case the option Filtered is selected, the inclusion and exclusion filters are shown. Using these filters, it is possible to define exactly what worksheets need to be included or excluded from the selection.

Use exact names or use wildcards (* or ?) to specify what sheets need to be selected or skipped.

The following wildcards can be used:

  • * : Allows selection based on none, one or more characters
  • ? : Allows selection based on exactly one characters

The inclusion filter is the first filter to be applied to the entire workbook followed by the exclusion filter.
The filters are case insensitive.

Examples

Suppose the following worksheets are present in a workbook:

  1. firstSheet
  2. sheet 2
  3. Sheet 3
  4. last sheet

Following examples can be used to include or exclude certain sheets:

Filter Description
firstSheet literal selection: will select sheet 1 (firstSheet)
sheet ? everything that starts with ‘sheet ‘ and has 1 character: will select sheets 2 & 3
*Sheet everything that ends with ‘Sheet’ (case-insensitive): will select sheets 1 & 4
*sheet* everything that has the word ‘sheet’: will select all sheets, 1 to 4

Combining the inclusion and exclusion filter is possible:

Filter Description
Inclusion: *sheet* select all sheets with the word ‘sheet’ inside
Exclusion: *first* except for the sheets with the word ‘first’ inside

The combination of the above filters will select sheets 2, 3 and 4.

Skip empty sheets

Specify whether or not to select empty sheets for conversion.

When enabling this option, empty sheets will not create files (or folders). Additionally, a warning is shown in the conversion log for each sheet that has been skipped for this reason.

When disabling this option, empty sheets will create empty output files.

Skip hidden sheets

Specify whether or not to select hidden sheets for conversion.

When enabling this option, hidden sheets will not create files (or folders). Additionally, a warning is shown in the conversion log for each sheet that has been skipped for this reason.

When disabling this option, hidden sheets will be selected and will create output files.

Range

Using the Range options, it is possible to determine the range (columns and rows) to select for the conversion.

Two strategies can be configured, one to find/set the start position and one to find/set the stop position.

Start

Defines the position in the worksheet from which to start the conversion.

Option Description
Beginning of file The first column/row (A1) of the worksheet will be selected as starting position for the conversion
Fixed A fixed row and column can be set as starting position
Dynamic The starting position is determined by finding a keyword inside a cell

Stop

Defines the position in the worksheet where to stop the conversion.

Option Description
End of file The last ‘used’ (active) column/row of the worksheet will be selected as stop position for the conversion
Fixed A fixed row and column can be set as stop position
Dynamic The stop position is determined by finding a keyword inside a cell

Select all

To export all the cells in a worksheet that are part of the active range, choose Beginning of file as start and End of file as stop.

Fixed selection

It is possible to specify a fixed start and/or stop position in a worksheet by selecting Fixed as strategy. When fixed is selected, it is possible to determine the Row and Column number on which to start/stop the conversion. The number of the column matches the order in Excel. E.g. Column C = 3.

Using a value of 0 in Row or Column will ignore the setting.
In other words, row 0 as start will start at the first row, row 0 as stop will end with the last (active) row. The same goes for columns.

Configuring an invalid range will yield an empty selection. E.g. selecting row 3 as start and row 2 as stop will not select anything.

Examples

Convert a specific range:

Filter Description
Start A1 - Stop D4 selects 4 rows and 4 columns as the range
Start A2 - Stop EOF selects all rows and columns starting on the second row
Start C2 - Stop B1 invalid range, nothing will be selected
Start BOF - Stop D5 selects everything until the 5th row/4th column
Start BOF - Stop EOF selects everything until the 5th row/4th column

Dynamic selection

Besides configuring a fixed position to start or stop the conversion, it is also possible to search for the position dynamically based on the content of a cell. This is done by selecting Dynamic as strategy.

Enter the keyword on which to search for in the worksheet. Optionally, it is possible to set a row/column offset if you want the range to be extended or shrunk compared to the location of the keyword. Leaving the offset to 0 will ignore this setting.

When a keyword is not found in a spreadsheet, the selection will be extended to either the beginning of the worksheet or the end of the worksheet.

When a keyword is used in combination with the fixed location option and the keyword is found before a fixed start range or after a fixed end range, this will yield an empty selection.

Skip empty rows

To avoid converting empty rows, it is possible to ignore these by checking the option Skip empty rows.

Cells

There are different types of cells that are recognized by Excel Converter. Each type of cell can be set up with a different extraction option.

Cell type Extraction options
Text Text, value
Boolean Text, value
Date Text, value, sortable date/time, universal date/time, long date, short date
Time Text, value, long time, short time
Formula Resulting text, resulting value, formula
Numerical Text, value
Percentage Text, value
Currency Text, value
Fraction Text, value
Scientific Text, value

Text

The textual representation and applied formatting (how it is represented in Excel) is used for the conversion.

Value

The actual value of the cell is used for the conversion.

Date

  • Full date/time format, e.g. 08 April 2007 21:08
  • Sortable date/time, e.g. 2007-04-08T21:08:59
  • Universal date/time, e.g. 2007-04-08 21:08:59Z
  • Long date, e.g. 08 April 2007
  • Short date, e.g. 08/04/2007

Time

  • Long time, e.g. 21:08:59
  • Short time, e.g. 21:08

Formula

  • Resulting text: the represented text (and applied formatting) of the result of the formula is used
  • Resulting value: the actual value of the result of the formula is used
  • Formula: the actual formula itself is used

E.g. suppose in a worksheet that cell A1 has a value of 1 and B1 has a value of 1,255. The formula in cell C1 contains =A1+B1 and is formatted to show only 2 decimals.

The option Resulting text will export 2,26.
The option Resulting value will export 2,255 or 2.255 depending on the decimal character for the system.
The option Formula will export =A1+B1.

Unmerge merged cells

By default, content of merged cells is repeated for every cell in the merged cell range. To disable this behavior, check the option ‘Unmerge merged cells’. This will ensure the content is only exported once.

Newline

This option is used to specify the characters to be used for encoding newlines in the output file. Optionally, this setting will also be used to normalize the newline characters found in cell values.

The following variables are available:

Option Description
CRLF Used on Windows systems
CR Most commonly used on MAC OS systems up to version 9 and OS-9
LF Used by Unix and Unix-like systems such as GNU/Linux, MAC OS X, FreeBSD …

Normalize newline characters

When enabled, this setting will convert all newline characters found in each cell to the selected newline character. Both carriage returns (CR) as well as line feeds (LF) and combination (CRLF) will be replaced with the selected newline character. This will ensure all content in the output file consistently uses the same newline character.

Add newline at the end of the file

Specify whether or not the very last row should be followed by a newline character or not. Adding a newline character at the end of the file effectively creates one new empty line.

Delimiter

The delimiter is the character that is put BETWEEN each value in the output. The default option is set to System and will use the system’s default list separator.

The option Custom can be used to specify a custom delimiter character.
Multiple characters are allowed.

The delimiter or list separator is different for each system depending on the regional settings. E.g. for the US this is usually a comma (,) and in Europe this is a semi-colon (;).

Quote

The quote character is the characters that is put BEFORE and AFTER each value in the output. It is only used to make the output CSV and/or Excel compliant and is not required in every situation.

Use the option Custom to specify a custom quote character.
Multiple characters are allowed.

This option is only available in CSV profiles following the CSV compliancy rules.

CSV Compliancy

These settings ensure CSV compliancy so that the output can be used safely with external systems.

CSV compliancy could break in case a cell value contains one of the following characters:

  • Delimiter
  • Quote
  • Newline
  • Leading/trailing space(s)
  • Empty value
  • Excel formatting

The output is made CSV compliant (CSV formatted) by applying quotes around a cell value when one of the above characters or values is present. Cells that do not contain the above values do not require the quotes, but formatting these anyway will still output a valid CSV file.

CSV Compliancy settings are only available in the CSV profile.

The following options are available:

Option Description
Always All values will be CSV formatted regardless of their content
Optional Only certain values will be CSV formatted depending on their content and the sub-options set

In case Optional is selected, additional settings are made available to tweak the rules.

Mind that the option Ignore in the additional settings will break CSV Compliancy!

Delimiter

Specify whether or not to apply CSV formatting if the value contains the delimiter character.

The following options are available:

Option Description
Quote Output value will be surrounded by quotes
Ignore Output value will be ignored and NOT surrounded by quotes
Replace Delimiter will be replaced by a custom character
Remove Delimiter will be removed from the output value

Quote

Specify whether or not to apply CSV formatting if the value contains the quote character.

The following options are available:

Option Description
Quote Output value will be surrounded by quotes (and doubles each quote found inside the value)
Ignore Output value will be ignored and NOT surrounded by quotes
Replace Quote character will be replaced by a custom character
Remove Quote character will be removed from the output value

Newline

Specify whether or not to apply CSV formatting if the value contains the newline character.

The following options are available:

Option Description
Quote Output value will be surrounded by quotes
Ignore Output value will be ignored and NOT surrounded by quotes
Replace Newline character(s) will be replaced by a custom character
Remove Newline character(s) will be removed from the output value

Leading/trailing spaces

Specify whether or not to apply CSV formatting if the value contains leading and/or trailing spaces.

The following options are available:

Option Description
Quote Output value will be surrounded by quotes
Ignore Output value will be ignored and NOT surrounded by quotes
Trim spaces Leading and trailing spaces will be removed

Empty value

Specify whether or not to apply CSV formatting if the value is empty.

The following options are available:

Option Description
Quote Output value will be surrounded by quotes
Ignore Output value will be ignored and NOT surrounded by quotes

Excel formatting

Specify whether or not to apply CSV formatting if the value is Excel-formatted. Excel formatting ensures that any leading zeroes or spaces are maintained when imported in Microsoft Excel.

An Excel formatted value is represented by an equal (=) character and a value between quotes as shown here:
="0001"
=" spacesBefore"

The following options are available:

Option Description
Quote Output value will be surrounded by quotes (and doubles each quote found inside the value)
Ignore Output value will be ignored and NOT surrounded by quotes

Excel Compliancy

These settings ensure that leading zeroes and leading spaces in the output are maintained when the file is imported in Microsoft Excel.

Excel compliancy could break in case a cell value contains one of the following characters:

  • Leading zeroes
  • Leading spaces

The output is made Excel compliant (Excel formatted) by applying quotes around a cell value and preceding it with the equal character (=). Cells that do not contain the above values do not require the formatting, but formatting these anyway will still output a valid Excel and CSV compliant file.

Excel Compliancy settings are only available in the CSV profile.

The following options are available:

Option Description
Always All values will be Excel formatted regardless of their content
Optional Only certain values will be Excel formatted depending on their content and the sub-options set

In case Optional is selected, additional settings are made available to tweak the rules.

Mind that the option Ignore in the additional settings will break Excel Compliancy!

Leading zeroes

Specify whether or not to apply Excel-formatting if the value contains leading zeroes.

The following options are available:

Option Description
Excel-format Output value will be Excel-formatted
Ignore Output value will be ignored and NOT Excel-formatted

Leading spaces

Specify whether or not to apply Excel-formatting if the value contains leading spaces.

The following options are available:

Option Description
Excel-format Output value will be Excel-formatted
Ignore Output value will be ignored and NOT Excel-formatted
Examples

E.g. importing a CSV file with the following values:
VAL1,001234, VAL2 VAL3,VAL4
will end up in Microsoft Excel as:
VAL1 1234 VAL2 VAL3 VAL4

By Excel-formatting the values:
VAL1,="001234",=" VAL2 VAL3",VAL4
the leading zeroes and spaces are maintained when opened in Microsoft Excel:
VAL1 001234 [space][space]VAL2 VAL3 VAL4

DSV Compliancy

These settings ensure DSV compliancy so that the output can be used safely with external systems.

DSV compliancy could break in case a cell value contains one of the following characters:

  • Delimiter
  • Escape
  • Newline
  • Tab
  • Single-quote
  • Double-quote

The output is made DSV compliant (DSV formatted) by escaping the above mentioned characters if present in the cell value.

DSV Compliancy settings are only available in the DSV profile.

Delimiter

Specify whether or not to apply DSV formatting if the value contains the delimiter character.

The following options are available:

Option Description
Escape Delimiter will be escaped
Ignore Delimiter will be ignored and NOT escaped
Replace Delimiter will be replaced by a custom character
Remove Delimiter will be removed from the output value

When the option Escape is selected, the delimiter will be replaced with \x and the hex value of the ASCII code of the delimiter character:

  • [COMMA (,)] will be replaced with \x2c
  • [SEMI-COLON (;)] will be replaced with \x3b

Escape

Specify whether or not to apply DSV formatting if the value contains the escape character (\).

The following options are available:

Option Description
Escape Escape character will be escaped
Ignore Escape character will be ignored and NOT escaped
Replace Escape character will be replaced by a custom character
Remove Escape character will be removed from the output value

When the option Escape is selected, the escape character will be escaped as follows:

  • [ESCAPE (\)] will be replaced with \\.

Newline

Specify whether or not to apply DSV formatting if the value contains the newline character.

The following options are available:

Option Description
Escape Newline character will be escaped
Ignore Newline character will be ignored and NOT escaped
Replace Newline character will be replaced by a custom character
Remove Newline character will be removed from the output value

When the option Escape is selected, the newline character will be escaped as follows:

  • [LF] will be replaced with \n
  • [CR] will be replaced with \r
  • [CRLF] will be replaced with \r\n.

Tab

Specify whether or not to apply DSV formatting if the value contains the tab character.

The following options are available:

Option Description
Escape Tab character will be escaped
Ignore Tab character will be ignored and NOT escaped
Replace Tab character will be replaced by a custom character
Remove Tab character will be removed from the output value

When the option Escape is selected, the tab character will be escaped as follows:

  • [TAB] will be replaced with \t.

Single-quote

Specify whether or not to apply DSV formatting if the value contains single-quotes.

The following options are available:

Option Description
Escape Single quote will be escaped
Ignore Single quote will be ignored and NOT escaped
Replace Single quote will be replaced by a custom character
Remove Single quote will be removed from the output value

When the option Escape is selected, the single-quote will be escaped as follows:

  • [SINGLE QUOTE (')] will be replaced with \'.

Double-quote

Specify whether or not to apply DSV formatting if the value contains double-quotes.

The following options are available:

Option Description
Escape Double quote will be escaped
Ignore Double quote will be ignored and NOT escaped
Replace Double quote will be replaced by a custom character
Remove Double quote will be removed from the output value

When the option Escape is selected, the double-quote will be escaped as follows:

  • [DOUBLE QUOTE (")] will be replaced with \".

Outputfile

These settings allow the configuration of the resulting output files. This includes the filename convention, the file encoding and the action that needs to be taken in case a file already exists in the output location.

Filename

Enter the filename convention for the resulting output files including the filename extension (e.g. “.csv”).

The following variables are available to dynamically create the filename:

Variable Description
{workbook} The workbook name (without extension)
{worksheet} The worksheet name
{ext} The extension of the workbook (e.g. xlsx)
{year} The current year (e.g. 2017)
{month} The current month (01 to 12)
{day} The current day (01 to 28/29/30-31)
{hour} The current hour in PM (00 to 23)
{min} The current minutes (01 to 59)
{sec} The current seconds (01 to 59)

It is possible to create a folder structure by using the “\” character.

In case a filename convention is defined without using variables, the risk is high that output files with the same name already exist during conversion of multiple sheets. To prevent this from happening, it is recommended to define a filename convention based on workbook and worksheet and possibly the use of date & time parameters.

If a worksheet name contains characters that are not supported by the Windows file system, these will simply be removed from the resulting filename.

Examples

Suppose a workbook with worksheets exists:

  • Workbook: My Workbook.xlsx
  • Worksheets:
    • Sheet 1
    • Sheet<2>

Filename convention: {workbook}_{worksheet}.csv
Output:

  • My Workbook_Sheet 1.csv
  • My Workbook_Sheet2.csv

Note: < and > are not supported by the filesystem and were stripped.

Filename convention: {workbook}.{ext}\{worksheet}.csv
Output:

  • My Workbook.xlsx\Sheet 1.csv
  • My Workbook.xlsx\Sheet2.csv

Note: ‘Workbook.xlsx’’ is a folder containing both sheets.

Encoding

The encoding determines the character set to be used for encoding the resulting output files. By default, the current system encoding is selected for any new profile.

File conflicts

Determine the action to take if a file already exists in the destination folder.

Option Description
Append The existing file is updated/appended with the new data
Overwrite Overwrites the existing file with the new content
Rename Renames the new file by adding a sequence number, e.g. Workbook (1).csv
Skip Skips the conversion of the worksheet

Logfile

Specify whether or not the results of a conversion is automatically written to a logfile after the conversion has finished.

When the option to save the results to the logfile is enabled, additional file output options are shown. The file output options are identical to the outputfile options described above.