Downloaded jobs all include a .sii job file. To view
the job file start 'Convert XLS', select File\'Open a Conversion Job' then
browse to locate the file. The job will appear in the 'Convert XLS'
interface for your examination. In some cases a data file has also
been included so you can actually run the job.
|
# |
Job Description |
How It Does It |
Download the Conversion
Job |
|
1 |
NAME:
Convert a
CSV (comma delimited) file with data containing leading zeros to an
Excel file
Input:
Text File, Bar "|" delimited. Some of the data contain
comma's further complicating the task.
Output:
Excel file
NOTES: This retains all textual data with
leading zeros such as:
000123, 121133, 001211 |
Step 1:
Convert the CSV to an XLS
Step 2:
Special Process 108 Change Number format to TEXT
Step 3:
Special Process 109 Search and Replace the ' character. |
Download
2 files are included:
FileLeadningZeros.csv and
CXLS_Convert-CSV-With-Leading-Zeros-To-XLS.SII
|
|
2 |
NAME:
Bar Delimited Text File to Excel
Input:
Text File, Bar "|" delimited. Some of the data contain
comma's further complicating the task.
Output:
Excel file |
Step 1:
Special Process 56 Search and replace the Bar with a tab
character.
Step 2:
Convert the Tab Delimited file, TSV, to an Excel file. |
Download
2 files are included:
Tryme.Tsv and
CXLS_Convert-From-Bar-Delimited-To-XLS.SII
|
|
3 |
NAME:
Transpose
Data then Export to CSV
Input:
Excel File. Records are in columns (not the usual row
formation).
Output:
CSV Text file, data is shown with records in rows. |
Step 1:
Special Process 110. Transpose the Excel file by swapping
columns to rows and rows to columns.
Step 2:
Convert the Excel file to a CSV file.
Step 3:
Special Process 110. Restore the original Excel file back to
its original state. |
Download
CXLS_TransposeXLS_Export_To_CSV.Zip
2 files are included:
CXLS_TransposeXLS_Export_To_CSV.SII
Book1.XLS |
|
4 |
NAME:
Append
Worksheet data, Specify rows or columns to skip.
Input:
Whole folder of excel files
Output:
Excel file with specified data
NOTES: Append numerous Excel sheets from many
workbooks, always skipping the first row. You can skip any rows or
columns by reviewing this solution.
|
Step 1:
Special Process 101. Copy Specified data from sheet to the
same or different XLS file.
Step 2:
'Copy Specified Range', in this case it will be 2:30. We assume it
we will never have more than 30 rows of data. The key of this job is
how we specify the range. To specify whole sets of rows you use this
format,
#:#
(for example, 2:30)
Where # is
the first and last rows. Note a similar technique can be
applied for for specifying Columns. In this case the format is:
x:y
(for example, B:ZZ)
Where x is
the lettered first column, and y is the lettered last column |
Download
CXLS_Append_XLS_Data_Skip_1st_Row.zip
1 file is included:
CXLS_Append_XLS_Data_Skip_1st_Row.SII
|
|
5 |
NAME:
Select
specific Columns to export to a text/csv file, and rearrange the
order of the columns. Remove header (1st row)
Input:
Excel File
Output:
Text File with specified columns, in the specified order with 1st
row removed.
NOTES: For this example, we want columns A, C,
and E to to be exported to a CSV/Text file, but we also want to
rearrange the columns. The order in the final output will be
C, E, A. The first row (containing header information) will be
removed.
|
Step 1:
Special Process 101. Copy Specified data from sheet to the
same or different XLS file. Copy Column C ("C:C") to a temporary XLS
file, Target.XLS.
Step 2:
Special Process 101. Same as step 1, this time specify column
E.
Step 3:
Special Process 101. Same as step 1, this time specify column
A.
Step 4:
Special Process 107. Delete Specified Rows. Delete Row 1.
Step 5:
Convert from XLS to CSV |
Download
CXLS_Rearrange_Specific_Columns_Save_As_Text.zip
2 file is included:
CXLS_Rearrange_Specific_Columns_Save_As_Text.SII
TryMe.XLS |
|
6 |
NAME:
Convert
from XLS to Fixed Width Text File
Input:
Excel File
Output:
Fixed Width Text File
NOTES: This solution uses a special character
that should not be used in any of the data. The special
character is ASCII 171 and looks like this: "«"
If you are using the "«" simply replace it with
something else. |
Step 1:
Convert from XLS to CSV
Step 2:
Special Process 209. Pad CSV to a fixed length field
Step 4:
Special Process 75. Replace the "," delimiter with the "«"
character, but not within double quotations.
Step 4:
Special Process 56. Search and replace the "«" character with
nothing. |
Download
CXLS_Save_XLS_As_Fixed_Width_Text_File.ZIP
2 file is included:
CXLS_Save_XLS_As_Fixed_Width_Text_File.SII
TryMe.XLS |
|
7 |
Name:
Convert whole folder of CSV files to PDF with formatting.
Input: CSV File
Output: PDF File
NOTES: Here we create fancy PDF reports from plain CSV files by
using a template.xls file. |
Step 1. Special Process 112: Clears a template.xls of data, file
which has formatting in it.
Step 2. Special Process 101: Copies csv data to template.xls
Step 3. Convert template.xls to a pdf |
Download
CXLS_Convert-CSV-To-PDF-With-Formatting.zip
4 file is included:
Test.SII
Report1.CSV
Report1.PDF
Template.xls
|
|
8 |
Name:
Remove Linefeed (CR+LF) from worksheet cells.
Input: XLSX File
Output: XLSX File
NOTES: When converting from Excel to a CSV file,
newline characters can cause problems. Look at this example to see
how to remove newline characters prior to converting to CSV or TXT. |
Step
1:
Special Process 109 Search and Replace the Linefeed (ASCII
10) character with the Space (ASCII 32) character. |
Download
CXLS_SP109_Remove_CRLF_from_worksheet.zip
2 file is included:
CXLS_SP109_Remove_CRLF_from_worksheet.SII
TryMe.XLSX
|
