Converting an ATC dictionary from Excel format to ASCII format

  • Published by Viedoc System 2022-02-10
  • Print

To convert an Anatomic Therapeutic Chemical classification system (ATC) dictionary:

1 Open the xlsx file in Microsoft Excel.
2

If Defined Daily Dose (DDD) is included in the file (columns C, D, E, and F), delete these columns, as well as the note column if there is one.
3

Insert a new column B.
4

In cell B2, write a formula. To add a formula, start by typing an equal sign (=). Then Excel interprets the text in that cell as a formula (unless otherwise specified).

The formula will look different depending on the language of your Excel installation. These are some examples:

  • English: =CONCAT(LEFT(CONCAT(A2;REPT(" ";5));4);" ";MID(CONCAT(A2;REPT(" ";20));5;6);C2)
  • French: =CONCAT(GAUCHE(CONCAT(A2;REPT(" ";5));4;" ";STXT(CONCAT(A2;REPT(" ";20));5;6);C2)
  • Spanish: =CONCAT(IZQUIERDA(CONCAT(A2;REPETIR(" ";5));4);" ";EXTRAE(CONCAT(A2;REPETIR(" ";20));5;6);C2)
  • German: =TEXTKETTE(LINKS(TEXTKETTE(A2;WIEDERHOLEN(" ";5));4);" ";TEIL(TEXTKETTE(A2;WIEDERHOLEN(" ";20));5;6);C2)
  • Swedish: =SAMMAN(VÄNSTER(SAMMAN(A2;REP(" ";5));4);" ";EXTEXT(SAMMAN(A2;REP(" ";20));5;6);C2)

Note! Depending on the regional settings in your operating system, you might need to replace the semicolons with commas.


The formula does the following:

  1. It takes the first four characters from column A (padded right with spaces unless the text is four characters long).
  2. It adds one space character.
  3. It adds characters 5, 6, and 7 from column A (padded right with spaces unless the text is seven characters long).
  4. It adds three space characters.
  5. It adds all of column C.
5

Fill all cells in the B column with the same formula, for example by dragging the small plus sign (+) downwards to cover the entire column.
6

Remove row 1 (the header row). Do to this, you might first need to turn off the header row on the Table Design page.


Then you can delete the sheet row from the Home page.
7

Select column B and copy it.
8 Paste the copied column into a raw text editor such as Windows Notepad. It is important to use an editor that does not add any formatting.
9

In the raw text editor, search for the quotation mark character (") and remove any such occurrences.
10

If there are empty lines at the end of the file, remove them.
11 Save your file with an appropriate filename that reflects the ATC version and with the filename extension asc.
12 Upload the file to Viedoc according to these instructions: Creating a dictionary instance.