…The previous move discussed the basics of…parsing Microsoft Excel CSV files using AWK, including…dealing with the MS-DOS line endings, quoted…cell values, and quotes within quoted cell values.…This movie continues Excel CSV parsing, including the more…difficult problems of commas and new lines within cell values.…The second case in which Excel will surround a…field with quotes is if it contains a comma.…If this occurs, there is no easy way for AWK…to tell which commas in the CSV file actually delimit fields.…
If you Excel data contains commas, my recommendation is to open the CSV file in…Excel or the original Excel file and resave it in tab delimited text format.…So we'll do that.…Yes, we really meant it.…Once you have done that, all we need to…do is change the field specifier from a comment to…a tab, and work with the .TXT file we just…created instead of the CSV file we were starting with.…Other than that, you can use the exact same AWK program…we developed in the previous lesson to deal with any quotes.…
Author
David D. Levine
Released
5/21/2014In this course, award-winning author and teacher David D. Levine shows you how to use AWK to read and write data in a variety of formats, produce reports, and automate repetitive tasks. He reviews the nuts and bolts of the language, such as field separators, pattern matching, variables, operators, expressions, and control structures; functions available for manipulating data; and integration with other programs like Excel.
- Determine what AWK is.
- Recognize how to write an AWK program.
- Determine how to use AWK command-line flags.
- Identify how to specify field and record separators with variables.
- Distinguish how to change a CVS file to a tab-separated one.
- Break down how to work with operators and arrays.
- Discover how to format output.
- Interpret how to string data with functions.
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome59s
-
-
1. What Is AWK?
-
What is AWK?3m 11s
-
-
2. AWK Command Line Basics
-
Writing an AWK program2m 51s
-
Using AWK command-line flags6m 18s
-
-
3. Understanding Records and Fields
-
4. Understanding Variables and Operators
-
5. A Quick Introduction to Regular Expressions
-
Regular expression basics5m 19s
-
-
6. Using Control Structures
-
Building control structures4m 14s
-
Creating an HTML table4m 45s
-
-
7. Formatting the Output
-
8. Functions and Arrays
-
Manipulating strings7m 28s
-
Using associative arrays4m 55s
-
-
9. Combining AWK with Other Tools
-
Using pipes7m 6s
-
Scripting with AWK4m 56s
-
Challenge: Perform a join1m 13s
-
Solution: Perform a join2m 55s
-
-
Conclusion
-
Goodbye1m 10s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Parsing Excel CSV files: Commas and new lines