Reading and Writing Text File

There are times when your API needs to read and write files to disk. For example, you may need to import data from a flat-file generated by another application or generate an interface file for a downstream system. In AlchemyJ, ajReadFile and ajWriteFile can do just that.

Reading data from a file

In this example, you will learn how to load data from a CSV to a business logic worksheet. The below screenshot shows the CSV file that will be loaded.

1. Prepare an input file similar to the one below. Save it at a location such as D:/AlchemyJ/Test-case/TestData/ajReadFile/Customer.csv (or .txt. The file extension does not matter).

2. ajReadFile has seven parameters. In this example, we only need to define the first two parameters of ajReadFile. In C3, input the text file location. In C4, put a comma (,) since we want ajReadFile to separate values into columns by a comma.

    ajReadFile(File_location, [Column_separator], [Left_enclosure_character], [Right_enclosure_character], [Begin_at_line], [Number_of_lines], [Character_set], [Skip_empty_row], [Run_condition], [Run_by_function_point_only])
   

3. As ajReadFile returns an array of records, highlight the range you want the data to be returned to, type in =ajReadFile(C3,C4) and press CTRL+SHIFT+ENTER to enter it as an array formula. The data will be imported as below when you run any function point.

If you want ajReadFile to run whenever Excel calculates the workbook, set the last parameter of ReadFlie, runningFlagControl, to FALSE.

Writing data to a file

Now let’s try to export some data from an Excel worksheet a file. This is very easy to do using ajWriteFile.

1. Prepare the data you want to write. In this example, we will export data from cell A27 to B40.

2. ajWriteFile can support many options

   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])
   

   In this example, we only need to define

   • Data: B10:C23

   • File_location: A full file path as defined in C4.

   • Write_option: Set it to 1 to overwrite the existing file content if any.

   • Column_separator: Put comma here as we want ajWriteFile to separate column values by a comma.

ajWriteFile will return TRUE if it writes the file successfully and will return FALSE otherwise. It is a single value return so it does not need to be keyed in as an array formula like ajReadFile.

3. The data will be saved to a file at the specified path when you run any function point.