How to Read File in Excel Formula

Let's say you have a system that outputs a text file every day for you automatically and you need the data in the file in your Excel table. So what you do daily will be open the file, copy the data, delete the separators, delete the enclosure characters then format it in Excel.

Sounds familiar? This is very common when you have different systems that do not talk to each other.

Instead of doing the manual time-consuming process every day, this can be automated with AlchemyJ. By using the ajReadFile function from AJXF, you can easily import the contents of a file into a defined area in Excel worksheet.

Let me show you how it works. I have a sample text file that looks like the below.

Based on the text file above, we can observe the content as below

1. The data is separated with comma (,)

2. The data is enclosed with open and closed brackets ()

3. The data starts from line 2

With the data above, we can use the ajReadFile function to easily populate the content into our Excel file. The ajReadFile function works according to the syntax below.

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

So if we need the data from the text file inserted into Excel, we can easily do so as below.

As you can see, the content is populated automatically with all the separators and enclosure characters removed automatically. We can also specify the number of lines of data that we need.

For more information, you can refer here.

Download our Community Edition from this link and try it yourself today.