|
|
- |
The macro is for importing to Excel a fixed lenght ASCII file or its lines that meet the criteria that you have set.
Data will be imported to a worksheet divided into cells in accordance with the record definition that you make yourself.
In a record definition you define, what positions on a text line
are belong to what columns, whether to import the data [in certain positions] or not,
and in positive case, shall the data be import as chartacters or as numbers.
With Excel functions you can easily change data format from one to another, e.g. from text to date.
|
- |
You have three basic alternatives in filtering what rows to import to Excel.
You can limit the import to certain count of lines [in their original order],
e.g. rows from and including 64.000 to 128.000.
Or you can import only the lines, where there is a certain string (import criteria)
starting in certain position or - alternatively - anywhere on the line.
You can use two import criteria at a time (e.g. voucher type and month).
Third alternative is to import all lines except those in alternatives told earlier in this chapter.
You can use several alternatives at a time.
|
- |
There are several alternatives where to import the data:
a new worksheet of 'ExcAud' workbook or a new workbook.
You can import the data also after the data that you have already imported.
|
|
|
|
- |
With this macro you can import any text file or selected lines from it to Excel workbook or to a new text file.
No problem, if the lines vary in lenght, or there is no common structure in the file.
|
- |
You can limit the import to certain count of lines in order
or import only the lines, where there is a certain string (import criteria)
starting in certain position or - alternatively - anywhere on the line.
|
- |
In addition, you can search for interesting data by bombing the text file
with an enormous amount of import criteria: like bank accounts,
names of persons or words associated to certain phenomena or themes.
Making the criteria file is easy.
You just write the criteria, each on its own line, in a plain text file
that you can make e.g. with Notepad (comes with windows).
|
- |
The macro is also able to add additional information to the lines imported:
their original row number [in the source file] and/or the criteria,
based on which the data was imported [3].
The order number helps to locate the text line in a source file.
The criteria can be useful e.g. in data re-classification
and when you want to add summary codes to account codes.
|
- |
In Excel, with its functions, you can devide the text lines to cells.
If the text lines are of same lenght or you make them such [with 'TexEdi'],
you might be able to import the text lines with 'ImpFix' (earlier 'TexImp3').
|
|
|
|
- |
The macro is able to combine data in two source tables in a third [new] table
provided that source table share the same information (e.g. product code).
Source tables can be in any workbook, on any worksheet, anywhere on an integral cell area.
|
- |
The new table will be in new workbook.
Based on the match type a user has have three alternatives what rows shall be written to the new file.
(1) all rows of the primary table, and those rows of the secondary table,
where the join key values match [4].
(2) only those rows of source tables, where values of join key value match.
(3) only those rows of the primary table, where the join key values do not match.
|
- |
In addition, there will be other information in the new table.
The will be three new columns in it: row number in the primary table; row number in the secondary table;
hit code (only primary table data / data of both source tables).
There will be also history about the run: what Excel workbooks, worksheets, and cell areas were seevted,
and when the run took place.
|
|
|
|
- |
The macro cross-tabulates phrases and other text in terms of two classification factors.
Text will be put into the cell, where the values of the classification factors overlap.
|
- |
Originally the macro was made for cross-tabulating audit findings in terms of internal control objects
and internal control elements [of the ECAR model].
|
|
|
|
- |
The macro helps in examining a text file and in editing it.
These measures can prove necessary, when you want to import text to Excel.
|
- |
The macro gives the following information about examination of a text file:
count of lines, the shortest and lonest lines; count of empty lines; and
frequences of chracters position by position.
The last mentioned information can be useful when assessing whether the structure of the text file is
fit for your purpose and when identifying data fields in fixed lenght ASCII file.
|
- |
The functionality in text file editing within 'TexEdi' is wide. You can make text lines to same fixed lenght;
remove empty lines; replace characters and strings with another chracters and strings;
remove carriage return (CR) from end of a text line;
append lower line to upper one; add CR to Text File [lacking it];
remove thousands separator from numbers; and change text to uppercase or to lowercase.
The macro does not harm the original text file, but makes a new text file.
|
|
|
- |
The macro detects gaps and duplicates in series containing running numbers in a column of an Excel
worksheet. Series can consist of numbers only, or they can consist of numbers with prefix (e.g. 'AA001').
Series must end with a number. Series are in a column of an Excel worksheet.
Series in any column of a table can be analyzed, and even several series in one column during one run.
|
- |
Information about the run and its results will be reported in a new table in the workbook where the table
analyzed exists. The results worksheet contains three section: Run, Gaps, and Duplicates.
Information will be supplied about date and time of run; selected workbook and worksheet, and
column; list of missing numbers, and their count; list of duplicates, and total count of duplicate cases.
|
|
|
[1] Earlier name: 'TexImp3'; 'TextImport3'
[2] Earlier name: 'TexImp4'
[3] If more than one import criteria applies, the macro uses only the criterion with most characters.
[1]Join key is the column values which the join is based on.
|