Bash Read Comma Separated CSV File on Linux / Unix

H ow do I read comma separated CVS file under UNIX / Linux / BSD / MacOS bash script? My sample file is as follows
FirstName LastName,DOB,SSN,Telephone,Status
How can I parse a CSV file in Bash?

A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. A CSV file stores tabular data in plain text format. Each line of the file is a data record. You can use while shell loop to read comma-separated cvs file. IFS variable will set cvs separated to , (comma). The read command will read each line and store data into each field. Let us see how to parse a CSV file in Bash running under Linux, macOS, *BSD or Unix-like operating systems.

Tutorial details
Difficulty level	Easy
Root privileges	No
Requirements	bash and awk running on Linux, macOS or Unix
Est. reading time	3 minutes

Bash Read Comma Separated CSV File

The syntax is as follows phrase a CSV file named input.csv:

How to parse a CSV file in Bash

Create a file called test.sh using a text editor such as vim command/nano command:
vim test.sh
Append the following code:

Parse a CSV file in Bash

Dealing with missing data/value or field

Unfortunately, there is no easy solution for missing values in a given field. We can detect if a value is missing using the if statement or case statement. For instance:

• No ads and tracking
• In-depth guides for developers and sysadmins at Opensourceflare✨
• Join my Patreon to support independent content creators and start reading latest guides:
  • How to set up Redis sentinel cluster on Ubuntu or Debian Linux
  • How To Set Up SSH Keys With YubiKey as two-factor authentication (U2F/FIDO2)
  • How to set up Mariadb Galera cluster on Ubuntu or Debian Linux
  • A podman tutorial for beginners – part I (run Linux containers without Docker and in daemonless mode)
  • How to protect Linux against rogue USB devices using USBGuard

Join Patreon ➔

In other words, you need to build validation and look for invalid or missing values in your CSV file.

CSV application support

The CSV file format is supported by spreadsheets and database management systems, including LibreOffice Calc, and Apache OpenOffice Calc.

Importing and Exporting CSV Files

How to open/import a text CSV file in calc

One can read comma separated CSV file using GUI app too.

1. Start calc
2. Choose File >Open.
3. Locate the CSV file that you want to open.
4. If the file has a *.csv extension, select the file.
5. Click Open.
6. The Text Import dialog opens.
7. Specify the options to divide the text in the file into columns.
8. Click OK.

Many Linux and Unix command line utility programs such as cut, paste, join, sort, uniq, awk, sed can split files on a comma delimiter, and can therefore process simple CSV files. For example:
awk -F’,’ ‘< print $1 " " $2 >‘
For example, let us convert the following CSV file into HTML table code. Use the cat command to display the file
cat input.csv
Outputs:

Here is my awk code:

Here is what I get:

Conclusion

A CSV file is nothing but a file containing Comma Separated Value. You learned how to read and parse comma-separated (CSV) file under a Linux or Unix-like system using bash while loop, awk and read command. Although all examples are simple and easy to understand, you must take care while parsing/reading the CSV file for missing values or invalid data. See the following man pages using the man command/help command:
man bash
help read
man awk
help while

🐧 Get the latest tutorials on Linux, Open Source & DevOps via

Category	List of Unix and Linux commands
Documentation	help • mandb • man • pinfo
Disk space analyzers	df • duf • ncdu • pydf
File Management	cat • cp • less • mkdir • more • tree
Firewall	Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04
Linux Desktop Apps	Skype • Spotify • VLC 3
Modern utilities	bat • exa
Network Utilities	NetHogs • dig • host • ip • nmap
OpenVPN	CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04
Package Manager	apk • apt
Processes Management	bg • chroot • cron • disown • fg • glances • gtop • jobs • killall • kill • pidof • pstree • pwdx • time • vtop
Searching	ag • grep • whereis • which
Shell builtins	compgen • echo • printf
Text processing	cut • rev
User Information	groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w
WireGuard VPN	Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04

Comments on this entry are closed.

script that worked for me

How to skip commented/blank lines in the CSV file?

i have used the same code to read my csv file but i cant read the last row of my csv file using while loop. please help.

I’m having the same issue. The last row of my csv isn’t being read. Did you find a solution roop?

I am having the same issue. will try to figure out ans post it.

you can get the last line when you access the variables again outside the loop. using the example discussed in the post:

—————————–
while read flname dob ssn tel status
…..
done sunil May 17, 2012 @ 7:46

I have two Csv files one is for daily report purposed and another is for daily data purposed.
masterreport.csv file format is

CODE,1-May-12,2-May-12,3-May-12,4-May-12,5-May-12,
205,0,0,0,0,0,
23070,0,0,0,0,0,
Interface error response CRBT fail,0,1,0,0,0,
C2S exception,0,1,2,0,2,
Interface Customer Recharge Not Allowed For Receiver,2240,2078,2050,2007,2363,

—–Many more up to 45 Rows and 32 column are there
and every day we have to update the corresponding date part from below mention file.

dailyreport.csv
C2S ERROR EXCEPTION TAKING TIME TILL VALIDATION , 624
CHNL_ERROR_SNDR_AMT_NOTBETWEEN_MINMAX , 56
Card Group Slab Suspended , 7096

like how many error’s are coming on that day we have to update the main report , which are not match daily report we put the value 0 on main report.

i didn’t found any logic how to do this , can any body help this

15 years working with csv files in bash and I didn’t know this method!

Источник

Open CSV File

Information, tips and instructions

Open CSV File

CSV file extension is used by files in comma separated values format. CSV files typically contain table information. First line of CSV file usually contains a table header and other lines contain table rows with data. Each line in CSV file consists of text strings delimited by commas. Each text string can contain any symbols except of the comma. If comma needs to be used inside the string, then string should be enclosed in quotes.

Simplicity of CSV format makes it great for cross platform data exchange. CSV file is frequently used on the internet by websites and web applications to export the data. When CSV file needs to be downloaded from the internet it is typically compressed using ZIP or GZIP application resulting in CSV.GZ or ZIP file. CSV file compression ratio is very high because it contains repeatable textual information.

Due to its simplicity you can open CSV file on any platform. CSV file are not binary thus it is possible to open them using any textual editor including Notepad on Windows, TextEdit on Mac OS and VIM on Unix/Linux. But viewing CSV file in text editor is not very efficient because this makes table information hard to read. To open CSV file it is much better to use specialized spreadsheet software including:

• Microsoft Excel (available on Windows and Mac OS)
• Google Sheets (available on any platform connected to the internet and capable of running Google Chrome internet browser or another compatible browser)
• Microsoft Office Online (available on any platform connected to the internet and capable of running compatible browser)
• Apple Numbers CSV files are transferred over e-mail or internet using text/csv multipurpose internet mail extensions (MIME) type.

Open CSV File on Windows OS

• Try double clicking on a CSV file in Windows File Explorer.
• If the CSV file reading software is associated correctly then the file will open
• If the file did not open you should install Microsoft Excel software
• To open CSV file in Microsoft Excel you need to launch it and then select File->Open in the main menu
• In the File Open dialog select CSV file and click «Open»
• As a free alternative to Microsoft Excel you can use Google Docs or LibreOffice Calc

CSV files are frequently converted to the XLSX or XLS Microsoft Office file formats. XLSX format can contain significantly more spreadsheet related features but has limitation in software capability.

Источник

How to parse a CSV file in Bash?

I’m working on a long Bash script. I want to read cells from a CSV file into Bash variables. I can parse lines and the first column, but not any other column. Here’s my code so far:

It’s only printing the first column. As an additional test, I tried the following:

And $y is empty. So I tried:

And $y is b . Why?

6 Answers 6

You need to use IFS instead of -d :

Note that for general purpose CSV parsing you should use a specialized tool which can handle quoted fields with internal commas, among other issues that Bash can’t handle by itself. Examples of such tools are cvstool and csvkit .

From the man page:

-d delim The first character of delim is used to terminate the input line, rather than newline.

You are using -d, which will terminate the input line on the comma. It will not read the rest of the line. That’s why $y is empty.

We can parse csv files with quoted strings and delimited by say | with following code

awk parses the string fields to variables and tr removes the quote.

Slightly slower as awk is executed for each field.

If you want to read CSV file with some lines, so this the solution.

In addition to the answer from @Dennis Williamson, it may be helpful to skip the first line when it contains the header of the CSV:

Parsing CSV files under bash using loadable module

Under bash, you could use (and/or create) loadable modules. You may found more information at source tree.

There is an full working cvs parser ready to use in examples/loadables directory: csv.c.

Under Debian GNU/Linux, you may have to install bash-builtins package, then:

This way is clearly the quickest and strongest than using any other combination of bash builtins or fork to any binary.

Unfortunely, depending on your system implementation, if your version of bash was compiled without loadable , this may not work.

Источник

Unix command-Line CSV viewer [closed]

Want to improve this question? Update the question so it’s on-topic for Super User.

Closed 2 years ago .

Is there a convenient command-line csv viewer, possibly a unix tool or a mod of some tools (e.g. vim or python)?

I find it easy to simply edit CSV files by writing it manually (since all you need to do it comma delimit the columns), but is there a way to view it in a slightly nicer UI on the command-line?

6 Answers 6

sc is a command-line spreadsheet program that’s been around a long time, likely available in your package manager. Here’s a Linux Journal intro article to it:

There’s a tool, CSVfix, which helps with viewing CSV files.

CSVfix is a command-line stream editor specifically designed to deal with CSV data. With it you can, among other things:

• Convert fixed format, multi-line and DSV files to CSV
• Reorder, remove, split and merge fields
• Convert case, trim leading & trailing spaces
• Search for specific content using regular expressions
• Filter out duplicate data or data on exclusion lists
• Perform sed/perl style editing
• Enrich with data from other sources
• Add sequence numbers and file source information
• Split large CSV files into smaller files based on field contents
• Perform arithmetic calculations on individual fields
• Validate CSV data against a collection of validation rules
• Convert between CSV and fixed format, XML, SQL and DSV
• Summarise CSV data, calculating averages, modes, frequencies etc.

A simple way to view CSV files on the command-line is to pipe the .csv file into the column utility with the column delimiter set as a comma:

Источник