READ ASCII FILES MACRO v. 2.1 – 27/02/01 – By Gianpiero Torello
This macro reads a text file in an Excel sheet.
The file can be in one of the following formats: CSV, Tab separated fields or Fixed length.
I originally wrote it to read my address exported from Lotus Organizer. I want to print all my address in a tabular format with all the telephone numbers in the same column and the address, faxes and emails condensed. To achieve this I had to work a lot every time I wanted to update my address list, so I thought to add the possibility to insert a calculated field while reading the text file. It might sound strange but if you look at the example it will be clear.
You can use this macro for every text file you need to read repeatedly having to apply on it laborious and tedious formulas and/or formats.
You can even recode some or all the contents of the fields.
Let’s talk about the example straight away. Please note that the table below is not complete. Click here to download the MS Word version of this document where you can find this table in its entirety.
| Load Field | Field Name | Offset | Length | Recoding | Format | Function |
| Confidential | 00 | |||||
| Yes | Categories | CAT | @ | |||
| Yes | Firstname | |||||
| Yes | Lastname | |||||
| Title | ||||||
| Jobtitle1 | ||||||
| Yes | Company1 | |||||
| Work1street | ||||||
| Work1city | ||||||
| Work1state | ||||||
| Work1zip | ||||||
| Work1country | ||||||
| Work1tel | ||||||
| … | … | … | … | … | … | |
| Calc | Tel | IF(“WORK1TEL”<>””,”W1: “&”WORK1TEL”&” “,””)&IF(“WORK2TEL”<>””,”W2: “&”WORK2TEL”&” “,””)&IF(“WORK3TEL”<>””,”W3: “&”WORK3TEL”&” “,””)&IF(“HOME1TEL”<>””,”H1: “&”HOME1TEL”&” “,””)&IF(“HOME2TEL”<>””,”H2: “&”HOME2TEL”&” “,””)&IF(“HOME3TEL”<>””,”H3: “&”HOME3TEL”&” “,””)&IF(“CARTEL”<>””,”C: “&”CARTEL”&” “,””)&IF(“MOBILETEL”<>””,”M: “&”MOBILETEL”&” “,””)&IF(“PAGERTEL”<>””,”P: “&”PAGERTEL”&” “,””)&IF(“SCHOOLTEL”<>””,”S: “&”SCHOOLTEL”&” “,””)&IF(“OTHERTEL”<>””,”O: “&”OTHERTEL”&” “,””)&IF(“ISDNTEL”<>””,”I: “&”ISDNTEL”&” “,””) | ||||
| … | … | … | … | … | … | … |
The first field “Confidential” is a numeric and I want it to have a leading zero so I put “00” in the Format column. This is how Excel defines this type of format. It’s just an example because, as you can see, the column “Load Field” doesn’t contain “Yes” so it will not be loaded in the Excel sheet.
The second field “Categories” will get the Text format (@) and its content will be recoded according to the recoding table in the Recoding sheet, which looks like this:
| Code | Original Value | Recode To |
| CAT | Personal | PERSONAL |
| CAT | PUBLIC | PERSONAL |
This means that if the “Categories” field contains Personal or PUBLIC the macro will translate it in to PERSONAL. Any other value will remain unchanged.
Now let’s come to the last fields:
The field “Tel” has “Calc” in the “Load Field” column. This means that it won’t be loaded from the file but the macro will put the Excel formula in the “Formula” column.
IF(“Work1tel”<>””,”W1: “&”Work1tel”&” “,””)&IF(“Work2tel”<>””,”W2: “&”Work2tel”&” “,””)&IF(“Work3tel”<>””,”W3: “&”Work3tel”&” “,””)&IF(“Home1tel”<>””,”H1: “&”Home1tel”&” “,””)&IF(“Home2tel”<>””,”H2: “&”Home2tel”&” “,””)&IF(“Home3tel”<>””,”H3: “&”Home3tel”&” “,””)&IF(“Cartel”<>””,”C: “&”Cartel”&” “,””)&IF(“Mobiletel”<>””,”M: “&”Mobiletel”&” “,””)&IF(“Pagertel”<>””,”P: “&”Pagertel”&” “,””)&IF(“Schooltel”<>””,”S: “&”Schooltel”&” “,””)&IF(“Othertel”<>””,”O: “&”Othertel”&” “,””)&IF(“Isdntel”<>””,”I: “&”Isdntel”&” “,””)
The above formula simply concatenates all the non blank telephone numbers in one cell. But why does it contain “Work1tel”? Because Work1tel will not be loaded in the Excel sheet so it would not be accessible to a formula. Instead, the macro will replace “Work1tel” with its actual content. Note the speech marks: if you are treating a field as text you must use them. And remember that it’s case sensitive so it must match the name you wrote in the “Field Name” column (“WORK1TEL” wouldn’t work).
If you want to address an Excel cell dynamically, use the R1C1 reference style otherwise you will end up always addressing the same cell. Thus, for instance, to remove the last dash from the cell on the immediate left, the formula will be:
IF(RIGHT(rc[-1],3)=” – “,LEFT(rc[-1],LEN(rc[-1])-3),rc[-1])
Finally, when you run the macro you will be prompted to enter the number of the tab where the above template is. This is the number of the tab in the current sheet. So when you run the macro you must have opened the template sheet in advance and it must be the one with the focus. The data will be read in a new sheet.
