Reading CSV files

The CSV format

Comma separated values (CSV) files are common in business. Database systems, statistical software, accounting systems, learning management systems, and many other programs can read and write CSV files. They're often used to take data from one system to another.

Here's an example, part of a CSV file of Cthulhu's favorite goating firms around the world.

  • "Convallis Inc.","Brazil",966,100
  • "Nascetur Ridiculus Mus Company","Nicaragua",990,94
  • "Quisque, Layton, and Qump LLP","British Virgin Islands",998,95
  • "Tristique Senectus Corp.","Vanuatu",942,98
  • "Orci Institute","Saint Pierre and Miquelon",1015,101

Each line is a record about one thing, in this case, one company. There are four fields in every row:

  • Name (string)
  • Country (string)
  • Number of goat employees (single)
  • Number of human employees (single)

CSV files are simple text files. They can be read and written by simple text editors, like Windows Notepad, or Mac TextEdit. You can also use better (and free) editors, like Notepad++ for Windows, or Brackets for both Mac and Windows.

In fact, you should use text editors when you're messing with CSV files for this course. Don't use Excel to look at the CSV files themselves.

Note

Excel is greedy. It hijacks CSV files, making it harder to work with them. You can fix that, so that Notepad open CSV files. Your choice, but it would makes things easier.

Opening and closing CSV files

You open and close them just like unformatted files. For example:

  • Open ThisWorkbook.Path & "\cthulhu-favorite-companies.csv" For Input As #1
  • ...
  • Close #1

You can write VBA to output CSV files as well:

  • Open ThisWorkbook.Path & "\cthulhu-party-invitations.csv" For Output As #1
  • ...
  • Close #1

Note

Be careful if Cthulhu invites you to party. You might be part of its dinner plans.

Reading CSV data

Let's take the data set above:

  • "Convallis Inc.","Brazil",966,100
  • "Nascetur Ridiculus Mus Company","Nicaragua",990,94
  • "Quisque, Layton, and Qump LLP","British Virgin Islands",998,95
  • "Tristique Senectus Corp.","Vanuatu",942,98
  • "Orci Institute","Saint Pierre and Miquelon",1015,101

How about we write a program that will output the first company's data in a workbook. It starts out like this:

Start

Click the button, and the program reads the first company's data, and shows:

Output

Here's some code for the button:

  1. Dim tTempLine As String
  2. Dim tCompany As String
  3. Dim tCountry As String
  4. Dim sGoats As Single
  5. Dim sHumans As Single
  6. Dim sGoatAdvantage As Single
  7.  
  8. ' Input.
  9. Open ThisWorkbook.Path & "\goat-companies-sample.csv" For Input As #1
  10. ' Read the first company record.
  11. Input #1, tCompany, tCountry, sGoats, sHumans
  12. Close #1
  13.  
  14. ' Processing.
  15. sGoatAdvantage = sGoats - sHumans
  16.  
  17. ' Output.
  18. Cells(5, 2) = tCompany
  19. Cells(6, 2) = tCountry
  20. Cells(7, 2) = sGoats
  21. Cells(8, 2) = sHumans
  22. Cells(10, 2) = sGoatAdvantage

Let's check the input part. Here's the file again:

  • "Convallis Inc.","Brazil",966,100
  • "Nascetur Ridiculus Mus Company","Nicaragua",990,94
  • "Quisque, Layton, and Qump LLP","British Virgin Islands",998,95
  • "Tristique Senectus Corp.","Vanuatu",942,98
  • "Orci Institute","Saint Pierre and Miquelon",1015,101
  1. ' Input.
  2. Open ThisWorkbook.Path & "\goat-companies-sample.csv" For Input As #1
  3. ' Read the first company record.
  4. Input #1, tCompany, tCountry, sGoats, sHumans
  5. Close #1

Line 9 opens the file as usual. Line 11 reads the fields into the variables. Line 12 closes the file

That's all there is to reading CSV files. Well, reading one line, anyway. Later in the course, you'll learn how to read an entire file.

Pattern

Input from a file (CSV)

Open the file, Input a record, Close the file.

Exercise

Exercise

Aussie rules with input file

Earlier, you computed the score of an Aussie rules game. Do it again, but with the input from a CSV file.

The worksheet starts like this:

Start

The program reads the file name from the worksheet, reads goals and behinds from that file, computes the score, and outputs the data. For example, if the file is...

  • 5,8

... then goals is 5, and behinds is 8. Each goal is worth six points, and each behind is worth one point.

Here's the output with that data:

Output

Here's another example. To start:

Start

In goat007.csv:

  • 3,2

Output:

Output

You need to make the input CSV file(s) yourself, with Notepad (Windows), or TextEdit (Mac).

Upload your workbook, and a test CSV file.