This method involves inserting special markers within the text file to control the translation process.
|
Marker |
Purpose |
|
>>n |
Table Start, where n is the table number |
|
<<n |
Table End |
|
-------- |
Single Column Field. Copies data to one Excel Column. |
|
========= |
Multiple Column Field. Copies data to many Excel columns |
Summary of Markers Table
Using a combination of these simple markers, many complex data parsing tasks can be achieved.
To copy a text table to Excel, insert two control lines before the top row of the data. The two control lines you should insert are the Table Start marker followed by the required field markers. Then after the last data row of the table, insert the Table End Marker as shown below:
>>1 Table Start Marker (n=1)
------------ ------------- ----------- Field (or Column) Template
xxxxxxxxxxxx yyyyyyyyyyyyy zzzzzzzzzzz Text data to import to Excel
xxxxxxxxxxxx yyyyyyyyyyyyy zzzzzzzzzzz Text data to import to Excel
. .
. .
. .
<<1 Table End Marker
Short Cut Markers:
In the case where only a single type of field marker is required, you can use a short cut marker instead of typing many repeated "-" (dash) or "=" (equal) characters:
1. Single Filed Only: - (a single "-" at the beginning of the first column means all "–" to the end of line)
Sample 1 and Sample 2 are exactly equivalent
Sample 1 Sample 2
>>1 >>1
- -------------------------
aaaa bbbb cccc dddd aaaa bbbb cccc dddd
aaaa bbbb cccc dddd aaaa bbbb cccc dddd
. . . .
. . . .
<<1 <<1
2. Multi-Field Only: = (a single "=" at the beginning of the first column means all "=" to the end of line)
Sample 3 and Sample 4 are exactly equivalent
Sample 3 Sample 4
>>1 >>1
= ===============
aaaa bbbb cccc dddd aaaa bbbb cccc dddd
aaaa bbbb cccc dddd aaaa bbbb cccc dddd
. . . .
. . . .
<<1 <<1
The following example illustrates how and where to use the file
markers.
Example: Importing a text file to Excel
Assume you have a text file that looks something like this:
xxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxx yyyyyyy zzzz wwwwwwwwwwww
yyyyyyy wwwwwwwwwwww
xxxxxxx yyyyyyy zzzz wwwwwwwwwwww
xx yyyyyy zzzzzzzzzzzz wwwwwwwwwwwwww
xx zzzzzzzzzzzz wwwwwwwwwwwwww
xx zzzzzzzzzzzz wwwwwwwwwwwwww
It is possible to fit the above text (represented by the x's, y's, z's, and w's data columns) correctly into 3 tables. Each table may have different number of rows and columns.
The process is very simple:
Step 1: Edit the text file (or a copy of it) by inserting Markers to define the start and end of table rows and columns as follows:
|
FILE CONTENTS |
COMMENTS |
|
>>1 |
Table 1 start marker Import as one Column First data row . . Last data row Table 1 end marker
Table 2 start marker Import as 4 Columns First data row . Last data row Table 2 end marker
Table 3 start Import 4 Columns First data row . Last data row Table 3 end marker |
Step 2: Select "Use a Marked up text file", then click convert. This will launch Microsoft Excel and copy the data into the correct cells.
If the TXL Wizard Option Clear sheet contents before processing check box is checked then the existing data and formatting in the output file will be cleared before processing. If the Clear sheet contents before processing option has been unchecked, and the Excel file name selected actually exists, the corresponding cells will be overwritten with all other cells and formatting information preserved.
In many cases, you can make the TXL software automatically determine the columns. For example we can alternatively mark table 2 as follows:
>>2 Table 2 start
===================================== Import as multiple (4) Columns
xxxxxxx yyyyyyy zzzz wwwwwwwwwwww . First data row
yyyyyyy wwwwwwwwwwww .
xxxxxxx yyyyyyy zzzz wwwwwwwwwwww . Last data row
<<2 Table 2 end
- Sometimes you may need to mix the column markers to achieve a desired effect. For example:
>>1 Table 2 start
==================== ------------- Creates 3 + 1 Columns
xxxxxxx yyyyyyy zzzz ww wwwwwwwwww . First data row
yyyyyyy ww wwwwwwwwww .
xxxxxxx yyyyyyy zzzz ww wwwwwwwwww . last data row
<<1 Table 2 end
This will import the data into Excel in 4 columns (3 columns for XYZ data, followed one column for the "w" data in a single column. Note that the "w" data is not split into 2 columns since it has been marked with a Single column marker "-" instead of the "=".
- If you wish to import the spaces between the z's and the w's into a separate column in Excel, you should do this instead:
>>1 Table 2 start
==================== --- ------------- Creates 5 Columns
xxxxxxx yyyyyyy zzzz ww wwwwwwwwww . First data row
yyyyyyy ww wwwwwwwwww .
xxxxxxx yyyyyyy zzzz ww wwwwwwwwww . last data row
<<1 Table 2 end
Sometimes the columns are not separated by any spaces; rather they are determined by exact location. For example the table:
>>1
-- -- ----
MM/DD/YYYY
01/09/2001
02/15/2001
02/17/2003
.
.
<<1
As marked above, the second data row will copy "01/", "09/" and "2001" into Excel cells A2, B2, C2, respectively, with the slash marks being included. When being used for exact location, the dash markings should be interpreted as meaning the exact number of characters represented by the dashes plus one additional character at the end of each column, with the exception of the last column. The additional character is not automatically added onto the last column.
If you wanted "01", "09" and "2001" without the slashes, do this:
>>1
- - ----
MM/DD/YYYY
01/09/2001
02/15/2001
02/17/2003
.
.
<<1
Here is another example to illustrate this concept. If your file markings on your input file look like this:
>>1
- -- --- ---- ----
1234/5678/1234/5678/1234
1234/5678/1234/5678/1234
1234/5678/1234/5678/1234
. .
. .
<<1
Your output in A1:E2 will look like this:
12 567 1234 5678/ 1234
12 567 1234 5678/ 1234
. .