Course contents · Import, templates, protection

Importing text files and templates

Open the practice in the trainer →

Importing a text file

Accounting systems often export data as .txt or .csv. Excel opens them with Data → Get Data → From Text/CSV (the Text Import Wizard in older versions):

  1. Choose the format: delimited (tab, semicolon, comma) or fixed width.
  2. Pick the delimiter — the preview should split the data into columns.
  3. Set column formats (e.g. Text for codes with leading zeros, Date for dates).
  4. Choose where to put the data. With Power Query the import can be refreshed when the source file changes.

Templates

Tasks in the trainer

  1. The lines were imported into one column. In B2:B6 extract the product code — the first 5 characters (as text, keeping leading zeros).
  2. In C2:C6 extract the product name: it starts at character 7 and ends before the second “;”.
  3. In D2:D6 extract the amount — everything after the second “;” — and turn it into a number.
  4. In D7 calculate the total. If the amounts stayed text, the total is 0 — that’s how step 3 is checked.
  5. In E2:E6 build a label like “Laptop (00125)”.

Solve the tasks in the trainer →

Check yourself

  1. An export file looks like this: Code;Product;Amount. Which delimiter do you choose?
  2. Product codes like 00125 turned into 125 after import. How do you prevent it?
  3. Which format is used for a template without macros?