This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
en:process_steps:worksheet:wsheet_use [2021/04/30 17:38] tiger [WSheet Use] |
en:process_steps:worksheet:wsheet_use [2024/02/18 01:56] (current) tiger |
||
---|---|---|---|
Line 1: | Line 1: | ||
{{indexmenu_n> | {{indexmenu_n> | ||
====== WSheet Use ====== | ====== WSheet Use ====== | ||
- | |||
- | **See the older version of this step (before May 2021) below** | ||
WSheet Use is the most frequent process step in Djeeni to tell that the process has something to do with a worksheet. It does much more than just ' | WSheet Use is the most frequent process step in Djeeni to tell that the process has something to do with a worksheet. It does much more than just ' | ||
* Based on settings it can also create a new worksheet in an existing workbook; even further it can create a new workbook with the first worksheet; | * Based on settings it can also create a new worksheet in an existing workbook; even further it can create a new workbook with the first worksheet; | ||
- | * It can use a template // | + | * It can use a template // |
- | * You can assign a logical | + | * You can assign a friendly |
+ | * It works with CSV (Comma Separated Value) files, too | ||
* Last but not least you can define a dynamic name to the workbook and worksheet using Djeeni formulas. | * Last but not least you can define a dynamic name to the workbook and worksheet using Djeeni formulas. | ||
The WSheet Use process step has these parameters | The WSheet Use process step has these parameters | ||
* **Worksheet Djeeni name**: this name identifies the worksheet in the process | * **Worksheet Djeeni name**: this name identifies the worksheet in the process | ||
- | * **Worksheet Excel name**: name of the worksheet as displayed in Excel. The speciality | + | * **Worksheet Excel name**: name of the worksheet as displayed in Excel. The specialty |
* **From workbook**: The physical location (path) of the workbook to be used: | * **From workbook**: The physical location (path) of the workbook to be used: | ||
* it can be a fixed path like C: | * it can be a fixed path like C: | ||
* you can specify dynamic path like c: | * you can specify dynamic path like c: | ||
* even combine Excel functions with Djeeni formulas like c: | * even combine Excel functions with Djeeni formulas like c: | ||
- | * **File type**: Djeeni currently supports standard XLSX and CSV (Comma Separated Values) files. We are continuously expanding the covered file types. | + | * **File type**: Djeeni currently supports standard XLSX, XLS and CSV (Comma Separated Values) |
- | * **Using Template**: Fill this optional parameter if you want to copy the content of another worksheet to it. It can be used only for Target worksheets (see the next parameter). The template data will overwrite the existing content. If you use it then you should follow the Excel syntax by adding the worksheet Excel name behind the workbook path separated by an exclamation mark. Example: if you want to use the worksheet **EmptyBudget** from the workbook **c: | + | * **Using Template**: Fill this optional parameter if you want to copy the content |
* **Use worksheet as**: You can choose from Source and Target. Source worksheets will be protected by Djeeni and their data will not be accidentally overwritten or otherwise modified. Target worksheets can still be used to read data from them for processing. | * **Use worksheet as**: You can choose from Source and Target. Source worksheets will be protected by Djeeni and their data will not be accidentally overwritten or otherwise modified. Target worksheets can still be used to read data from them for processing. | ||
* **Target worksheet Exists / Not exists action**: It can be specified what should happen if a target worksheet already exists or does not exist yet. Possible settings: | * **Target worksheet Exists / Not exists action**: It can be specified what should happen if a target worksheet already exists or does not exist yet. Possible settings: | ||
- | * //If worksheet does exist then use it as is; If worksheet does not exist then stop with error:// this option is useful when we want to add data to an existing worksheet | + | |
- | * //If worksheet does exist then use it as is; If worksheet does not exist then create it:// this option is useful when we want to create a worksheet and then add data to it (e.g. start a new worksheet every month and then add daily data to it) | + | |
- | * //If worksheet does exist then delete and recreate it; If worksheet does not exist then create it:// this option is useful when we want to create the worksheet always from scratch (e.g. if the source is modified after an error is discovered in it then everything should be reprocessed again) | + | |
- | * //If worksheet does exist then stop with Error; If worksheet does not exist then create it:// this option avoids accidentally overwriting an existing worksheet (probably an older version that must be kept) | + | |
- | * **Options**: | + | * **Options**: |
+ | * **UpdateLinks**: | ||
+ | * **CSV:**: If you import a CSV (Comma Separated Value) file then you can specify its structure using a 6-character string as follows: | ||
+ | * First position: | ||
+ | * Second position: The character that encloses string values. It can be a double quote (**" | ||
+ | * Third position: The decimal separator in numbers. It can be a dot (**.**) or a comma (**,**) | ||
+ | * Fourth position: The thousand separator in numbers. It can be a dot (**.**), a comma (**,**) or a space (**s**) | ||
+ | * Fifth position: If the CSV file comes with Windows (**w**) or Macintosh (**m**) format | ||
+ | * Sixth position: If the negative sign can come after numbers in values. It can be yes (**-**) or no (**+**) | ||
+ | * Note the **:** between the option name CSV and the value. It is an error to omit this **:**; you can use space before and after the **:**, though. | ||
+ | * Default: if a CSV file is imported and the CSV option is not provided then the default value is **CSV:," | ||
+ | |||
+ | |||
+ | ===== Examples ===== | ||
+ | |||
+ | ==== Opening an existing | ||
+ | |||
+ | The worksheet does exist and its workbook has a (possibly dynamic) location therefore the workbook path and the Excel name are known. Also it is known if the worksheet is used only as a data source or will also be modified (target). In case of the **Target** value is set then the Target action parameter can be set to **Exists: use as is / Not Exists: Error**. | ||
+ | |||
+ | ==== Adding | ||
+ | |||
+ | As it will be a new worksheet the **Use worksheet as** parameter must be set to **Target**. The **Target worksheet Exists / Not exists action** can have two values: either **Delete and Recreate / Create** or **Error / Create**. You do not have to take care of the workbook. If the workbook does exist then Djeeni simply uses it. If the workbook does not exist then Djeeni will create it as your request to have a new worksheet in this workbook implies that the workbook also must exist. | ||
- | ===== WSheet Use - older version ===== | + | ==== Creating a new workbook with its first worksheet |
- | WSheet Use is an acronym for worksheet | + | When you want to create a new workbook with one worksheet, you only have to take care of the worksheet |
- | {{djeeni: | + | ==== Opening a CSV file ==== |
- | Every worksheet | + | CSV files contain exactly one 'worksheet'. Unlike MS Excel, |
- | * **Worksheet Djeeni name: | + | |
- | * **Workbook folder:** The physical location of the workbook | + | |
- | * **Workbook | + | |
- | * **Workbook filetype/ | + | |
- | * **Worksheet | + | |
- | * **Use worksheet as:** here you can either choose Source or Target (default). Source worksheets are not allowed to be modified so Djeeni protects your data sources from accidentally being overwritten. Target worksheets on the other hand can be modified in consecutive process steps | + | |
- | * **Update links in the workbook?:** If links in the workbook must be updated before using it: Yes (default) / No | + | |
- | | + | |
- | * //If worksheet does exist then use it as is; If worksheet does not exist then stop with error:// this option | + | |
- | * //If worksheet does exist then use it as is; If worksheet does not exist then create it:// this option is useful when we want to create a worksheet and then add data to it (e.g. start a new worksheet every month and then add daily data to it) | + | |
- | * //If worksheet does exist then delete and recreate it; If worksheet does not exist then create it:// this option is useful when we want to create the worksheet always from scratch (e.g. if the source is modified after an error is discovered in it then everything should be reprocessed again) | + | |
- | * //If worksheet does exist then stop with Error; If worksheet does not exist then create it:// this option avoids accidentally overwriting an existing worksheet | + | |
- | * **Workbook open password (Optional): | + | |
- | * **Workbook write-reserved password (Optional): | + | |
+ | {{ : |