<

Learn how to a build a cloud-first strategyRegister Now

x

Quick and Dirty Unix CSV File Browser

Published on
8,218 Points
2,218 Views
Last Modified:
Background

Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc...

As a "Unix" shop, our only readily available tool was the "vi" editor, which is of little help when the records are too long or the size of the file is too big or the number of records is too numerous.

Unfortunately, we are not allowed to install any of 3rd party (free or not) utilities out there.

What could we do?

Solution: csvb - a Quick and Dirty CSV File Browser

The program is written in ksh shell and awk and will take the following parameters :

Option	Description			Default
 -d	Field delimiter			Comma ','
 -h	The file has header row 		1 = Yes
 -p	Starting record to display      1 = Default
 -n	Number of records to display    1 = Default, 0 = All
 -q	Quoted fieds			" = Default
 -c	Number of columns in display    2 = Default, 3 = Max
 -?	Display the help message

Open in new window


Program code is attached.

Sample output
==> csvb -h1 -n 3 -p 100 SAM_Exclusions_Public_Extract_14014t.csv
-e #------------------------------------------------------
#
# /usr/local/bin/csvb Arguments:
#
#  -d ,         <= delimiter (',' =default)
#  -q "         <= quote's ("=default)
#  -c 2         <= #output cols to display (2=default,max=3)
#  -p 100               <= starting record (1=default)
#  -n 3         <= #recs to display of 121872 (0=all,1=default)
#  -h 1         <= file has header row (1=Yes)
#
# Source file: SAM_Exclusions_Public_Extract_14014t.csv
#
#------------------------------------------------------
#


#*** File: SAM_Exclusions_Public_Extract_14014t.csv[,] - Starting rec# 100, display 3 recs.
#*** Header (26 columns): Classification,Name,Prefix,First,Middle,Last,Suffix,Address 1,Address 2,Address 3,Address 4,City,State / Province,Country,Zip Code,DUNS,Exclusion Program,Excluding Agency,CT Code,Exclusion Type,Additional Comments,Active Date,Termination Date,Record Status,Cross-Reference,SAM Number

*** Rec#100 - 1/3 (NF38): SAM_Exclusions_Public_Extract_14014t.csv
      Classification 01: Individual                    Country 14: USA
                Name 02:                              Zip Code 15: 33179
              Prefix 03:                                  DUNS 16:
               First 04: A.                  Exclusion Program 17: Reciprocal
              Middle 05: JUMBO                Excluding Agency 18: EPA
                Last 06: MCDONALD                      CT Code 19:
              Suffix 07:                        Exclusion Type 20: Ineligible (Proceedings *
           Address 1 08:                   Additional Comments 21:
           Address 2 09:                           Active Date 22: 8/12/2009
           Address 3 10:                      Termination Date 23: 8/11/2014
           Address 4 11:                         Record Status 24:
                City 12: MIAMI                 Cross-Reference 25: "(also MCDONALD JUMBO, M*
    State / Province 13: FL                         SAM Number 26: S4MR3QH7L

*** Rec#101 - 2/3 (NF26): SAM_Exclusions_Public_Extract_14014t.csv
      Classification 01: Individual                    Country 14: GBR
                Name 02:                              Zip Code 15: EC2V 8EY
              Prefix 03:                                  DUNS 16:
               First 04: A.                  Exclusion Program 17: Reciprocal
              Middle 05: LOPEZ                Excluding Agency 18: TREAS-OFAC
                Last 06: MIGUEL                        CT Code 19: 03-SDN-01
              Suffix 07:                        Exclusion Type 20: Prohibition/Restriction
           Address 1 08:                   Additional Comments 21: PII data has been masked*
           Address 2 09:                           Active Date 22:
           Address 3 10:                      Termination Date 23: Indefinite
           Address 4 11:                         Record Status 24:
                City 12: LONDON                Cross-Reference 25:
    State / Province 13:                            SAM Number 26: S4MR3QS26

*** Rec#102 - 3/3 (NF27): SAM_Exclusions_Public_Extract_14014t.csv
      Classification 01: Individual                    Country 14: USA
                Name 02:                              Zip Code 15: 105460413
              Prefix 03:                                  DUNS 16:
               First 04: A.                  Exclusion Program 17: Reciprocal
              Middle 05: STEPHEN              Excluding Agency 18: HHS
                Last 06: PACK                          CT Code 19: Z1
              Suffix 07:                        Exclusion Type 20: Prohibition/Restriction
           Address 1 08:                   Additional Comments 21: "Excluded by the Departm*
           Address 2 09:                           Active Date 22: 9/20/2000
           Address 3 10:                      Termination Date 23: Indefinite
           Address 4 11:                         Record Status 24:
                City 12: MILLWOOD              Cross-Reference 25:
    State / Province 13: NY                         SAM Number 26: S4MR3QVGD

Open in new window


CODE is here:
csvb.sh


NO WARRANTY

THE PROGRAM IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.

IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW THE AUTHOR WILL BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE PROGRAM (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER PROGRAMS), EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
0
Comment
Author:MikeOM_DBA
0 Comments

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month