ajWriteFile function

Description

The ajWriteFile function exports data from a range of cells in an Excel worksheet to a file.

Syntax

ajWriteFile(data, file_location, [write_option], [column_separator], [left_enclosure_character], [right_enclosure_character], [character_set], [number_of_retry], [interval_of_retry], [skip_empty_row], [run_condition],[run_by_function_point_only])

Argument Name	Argument Type	Description
data (required)	Range	The range of cells to be written into the file. The range of cells can span across multiple rows and columns. Each row represents a broken line and all the columns will be concatenated.
file_location (required)	String	The path of the file to be written.
write_option (optional)	Double	0 indicates to append the content of the range of cells selected in ‘Data’ to the file. 1 indicates to overwrite the file if it already exists. The default value is 0.
column_separator (optional)	String	The character that is used to split input record into fields. In the case of a CSV file, a comma is usually used.
left_enclosure_character (optional)	String	The left enclosure character. When a Column_separator is used, a field value itself can contain a column separator character. For example, when using a comma delimited format and with a field containing "Cathy, Chow" as its actual value, the field would be separated into two fields as there is a comma in. Using an enclosure character in writing and reading operations can solve this problem. The default value is an empty string (“”) which means no enclosure character.
right_enclosure_character (optional)	String	The right enclosure character. The default value is an empty string (“”) which means no enclosure character.
character_set (optional)	String	The character set used when writing the file. Some common character set codes are UTF-8 (ISO 10646 Unicode), GB2312 (Chinese Simplified), Big5 (Chinese Traditional), us-ascii. The default value is UTF-8.
number_of_retry (optional)	Double	The number of retries if the file to be written is locked by other processes. The default value is 3.
interval_of_retry (optional)	Double	The interval between each retry in milliseconds. The default is 1000 which means 1 second.
skip_empty_row (optional)	Boolean	If it equals FALSE, the empty rows will be written to the file. If it equals TRUE, the empty rows will not be written to the file. The default value is FALSE.
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: TRUE / FALSE

2) Return Type: Single Value

Example

Here are a few examples of the ajWriteFile function.

Example 1

This example demonstrates the append option. The below screenshot shows the file content before we run the function. It has one line "Hello World~".

Write_option is omitted. Hence, the default value of 0 (append) is used.

Values in the range of cells B1:E5 are appended to the end of the file. Note that since no separator was defined, cell values on the same row are concatenated.

Example 2

Let us see an example of overwriting. Similar to example 1, the contents of the file is ‘Hello World~’ before the function is run.

Write_option is set to 1 (override).

Values in the range of cells B1:E5 overwrite the previous content. Note that each value is separated by a comma and surrounded by round brackets since the comma was set as the column separator and round brackets were set as the left and right enclosure characters.

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Invalid file path.
Invalid write option, it can only be 0 or 1.
Character set is not supported.
Failed to write file.