1 / 24

Working With Files

Working With Files. Sources of Data. Adding data to a spreadsheet can be done in several ways, including: Type it in piece by piece Read it from a file Link to a database In this presentation we focus on reading data from a file. Data File Formats.

flavio
Download Presentation

Working With Files

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Working With Files

  2. Sources of Data • Adding data to a spreadsheet can be done in several ways, including: • Type it in piece by piece • Read it from a file • Link to a database • In this presentation we focus on reading data from a file

  3. Data File Formats • There are some standard data formats you can easily read into Excel • Perhaps the most common one uses comma separated values (csv) • The idea is that the data is in lines of text, one line for each row, with the entries for each column separated by commas • Many kinds of software allow you to export data in this format with the idea that you will then read it into Excel

  4. Text Files • Many of the files produced by software products have extra information in them besides just text • For example, Word documents contain the information about fonts, bold, italics, bullets and indenting, etc., along with the actual text • The same is true for Excel files, whether .xlsx or .xlsm • A comma separated values file (.csv file extension) is basically just a text file. Using .csv rather than .txt lets Excel know it’s in a format that can be read directly into a spreadsheet

  5. The Input Function • The Input function is used to read data from files • There are several ways to use it. Examples are in the InputDemo workbook; you should try them out. • [It’s especially interesting to try versions of Input on files not designed for them and see what happens.]

  6. Setting up to read… • The first step is to open a file for reading. • Excel has a nice dialog box that lets the user browse for a file. • The next slide shows the relevant code from the InputDemo

  7. Code to open a file.. And close it fName = Application.GetOpenFilename() IffName = False Then 'user cancelled Exit Sub End If OpenfNameFor Input Access Read As #1 <program body> Close #1 'close the file you opened

  8. File Number • Where we used #1, you can use any number • Using the number lets you have several files open and choose which ones to read and/or write to • Be sure to close any files you open in your program when you are done with them. Windows will probably take care of this for you, but you risk a corrupted file if you don’t

  9. Reading a Whole Line • One way to use Input is to read a whole line at a time. Here is the code for that version: Line Input #1, oneLine • Here oneLine is a string variable

  10. Whole Line Text File George Washington John Adams Thomas Jefferson John Quincy Adams Abraham Lincoln

  11. Whole Line Output The program prints a line saying “done” if it finishes normally

  12. Reading a Single Item • To read single items, you separate them with commas. Excel is primed to expect comma delimited values • It also regards the end of a line as a separator. • Here’s the code (oneItem is a string variable): Input #1, oneItem

  13. Single Item Text File George, Washington John, Adams Thomas, Jefferson John, Quincy, Adams Abraham, Lincoln

  14. Single Item Output

  15. You can read multiple items • To do this, you provide more than one variable as a target • If your file does not have the right number of items, your program will terminate abnormally • Here’s the code for reading two items at once: (ours are both strings but you can use other types if you know your data is numbers, for example) Input #1, item1, item2

  16. Two Item Text File George, Washington John, Adams Thomas, Jefferson Andrew, Jackson Abraham, Lincoln

  17. Two Item Result

  18. Odd number result Here we used our one time file, which has an odd number of items. The program terminated abnormally when it couldn’t find an item in the last read

  19. Read N Characters • You can also read a block of N characters • This may behave in an unexpected way since there are some “invisible” characters in text files, such as carriage return and line feed. • Here’s the code; note the different syntax. Variable chars is of type String. chars = Input(N, #1)

  20. The Data File This file ends with a return, which you can’t see. But look at the results…

  21. Reading one character at a time Lines 21 and 22 contain the carriage return and line feed characters

  22. Two at a time: Now line 11 has the carriage return and line feed characters

  23. Four at a Time Abnormal termination. The number of visible characters is 16, but the actual number is 18.

  24. Why Read Characters? • You might want to write code that picks out only certain kinds of items from the text, for example. • You can use string functions along with concatenation to build up things like a catalog of words in a text while ignoring punctuation • Usually, though, reading one item at a time is the right approach

More Related