Subjectively

dd if=/dev/random | kirk > blog

Subjectively header image 1

Format a US postal address in MySQL using null propagation

February 10th, 2012 · PHP

I recently had the problem that I needed to format addresses for mailing envelopes. The address data was pretty badly bunged up, so I (re)invented this little gem.

If any field is missing, the field as well as any surrounding formatting will disappear. This behavior works for NULL and empty (”) fields.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

delimiter //
CREATE FUNCTION KE_IC.format_us_address(prefix text,firstname text,mi text,lastname text,suffix text, 
                                  company text, address1 text,address2 text,city text,state text,zip text)
RETURNS text CHARACTER SET utf8
COMMENT 'uses null propagation to format us addresses'
BEGIN
 
    # Elements AND separators are ONLY displayed WHEN present
    #  treat empty FIELDS AS NULLS
    RETURN CONCAT_WS('\r\n',CONCAT_WS(', ',CONCAT_WS('. ', IF (prefix='',NULL,prefix),CONCAT_WS(' ', IF(firstname='',NULL,firstname),CONCAT_WS('. ', IF(mi='',NULL,mi), IF(lastname='',NULL,lastname)))),IF(suffix='',NULL,suffix)),
        IF(company='',NULL,company),
        IF(address1='',NULL,address1),
        IF(address2='',NULL,address2),
        CONCAT_WS(' ', IF(city='',NULL,city),CONCAT_WS(', ',IF(state='',NULL,state),IF(zip='',NULL,zip))),
        'USA'
    );
    #format_us_address('Mr','George','M','Cohen',NULL,NULL,'123 Main St.',NULL,'Anytown','CA','89754');
    #results:
    #Mr. George M. Cohen
    #123 Main St.
    #Anytown CA, 89754
    #notice the lack OF company, address2 line
    # AND the lack OF sep FOR non-existent suffix
END
//
 
delimiter ;
 
 
SELECT format_us_address('Mr','George','M','Cohen',NULL,NULL,'123 Main St.',NULL,'Anytown','CA','45647');

→ No CommentsTags:

Split a text file into chunks by line count

January 4th, 2012 · Linux

This is a simple splitter for text files by line count.

Input File with 4 lines: input.txt
this is line 1
this is line 2
this is line 3
this is line 4

./chunkfile -f input.txt -c 4 -j 2 -p output
or
cat input.txt | ./chunkfile.sh -c 4 -j 2 -p output -e txt

Output will be:
Output:
output_01.txt
output_02.txt
output_03.txt
output_04.txt

With a single line of text in each file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131

#!/bin/bash
#===============================================================================
#
#          FILE:  chunkfile.sh
# 
#         USAGE:  ./chunkfile.sh 
# 
#   DESCRIPTION:  split a text file by lines
# 
#       OPTIONS:  ---
#  REQUIREMENTS:  ---
#          BUGS:  ---
#         NOTES:  ---
#        AUTHOR:  Kirk Roybal (DBA), kroybal@kalkomey.com
#       COMPANY:  Kalkomey
#       VERSION:  1.0
#       CREATED:  12/30/2011 09:45:42 AM CST
#      REVISION:  ---
#===============================================================================
 
 
ScriptVersion="1.0"
 
#===  FUNCTION  ================================================================
#         NAME:  usage
#  DESCRIPTION:  Display usage information.
#===============================================================================
function usage ()
{
	cat <<- EOT
 
  Usage :  ${0##/*/} [options] [--] 
 
  Options: 
  -c|chunks #   Number of chunks
  -d|debug      Display bash debugging info
  -e|ext  exten Output file extension
  -f|file name  File to chunk
  -h|help       Display this message
  -j|just pad   Padding zeros for output files
  -l|log        Display logging information
  -p|prefix pre Prefix of new file names
  -v|version    Display script version
 
  Note:  using multiple logging options increases the log level
  (i.e.) ${0##/*/} -lll -f input.txt -c 3 -j 2 -p output
  Log level 3.
 
  Output:
  output_01.txt
  output_02.txt
  output_03.txt
 
 
	EOT
}    # ----------  end of function usage  ----------
 
#-----------------------------------------------------------------------
#  Handle command line arguments
#-----------------------------------------------------------------------
 
padding=2
 
while getopts ":c:de:f:hj:lp:v" opt
do
  case $opt in
 
    c|chunks   )  chunks=$OPTARG;;
 
    d|debug    )  set -x;;
 
    e|ext      )  ext=".$OPTARG";;
 
    f|file     )  filename=$OPTARG
                  ext=".${filename#*.}"
                  [[ -z $prefix ]] && prefix="$(basename ${filename} $ext)_"
                  ;;
 
    h|help     )  usage; exit 0   ;;
 
    j|just     ) padding=$OPTARG;;
 
    l|log      ) (( logging++ ));;
 
    p|prefix   ) prefix=$OPTARG;;
 
    v|version  )  echo "$0 -- Version $ScriptVersion"; exit 0   ;;
 
    \? )  echo -e "\n  Option does not exist : $OPTARG\n"
          usage; exit 1   ;;
 
  esac    # --- end of case ---
done
shift $(($OPTIND-1))
 
[[ -z $filename ]] && [[ -z $prefix ]] && {
	cat <<- EOT
 
  You must specify a filename or a prefix for output.
  (i.e.) ${0##/*/} -f input.txt -c 3 -j 2 
  or
  (i.e.) cat input.txt | ${0##/*/} -c 3 -j 2 -p output
 
	EOT
  usage
  exit 1
}
 
#take input from stdin
[[ -z $filename ]] && filename="-"
 
[[ -z $chunks ]] && {
  usage
  exit 1
}
 
guts=$(tempfile)
cat "$filename" > "$guts"
 
filesize=$(cat "$guts" | wc -l)
chunksize=$((filesize/chunks))
[[ $((filesize%chunks)) -gt 0 ]] && (( chunksize++))
 
for ((i=0;i<$chunks;i++))
do
  dest="${prefix}$(printf "%0${padding}i" $(($i + 1)))$ext"
  cat "$guts" | tail -n +$((($i*$chunksize)+1)) | 
    head -n $chunksize > "$dest"
done
 
[[ -f "$guts" ]] && rm "$guts"

→ No CommentsTags:

Bulk load csv data from bash into mysql with LOAD DATA LOCAL INFILE

December 13th, 2011 · Uncategorized 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

#!/bin/bash
#===============================================================================
#
#          FILE:  csv2mysql.sh
# 
#         USAGE:  ./csv2mysql.sh 
# 
#   DESCRIPTION:  Import data into mysql from csv
#                     ignore flag fields along the way.
#       OPTIONS:  ---
#  REQUIREMENTS:  ---
#          BUGS:  ---
#         NOTES:  ---
#        AUTHOR:  Kirk Roybal (Operations Manager), kroybal@kalkomey.com
#       COMPANY:  Kalkomey
#       VERSION:  1.0
#       CREATED:  12/13/2011 15:31:50 CST
#      REVISION:  ---
#===============================================================================
 
host=192.168.101.125
user=root
inputfile=product1.csv
sep=,
table=kris_inventory_sims
database=
 
 
ScriptVersion="1.0"
 
#===  FUNCTION  ================================================================
#         NAME:  usage
#  DESCRIPTION:  Display usage information.
#===============================================================================
function usage ()
{
	cat <<- EOT
 
  Usage :  ${0##/*/} [options] [--] 
 
  Options: 
  -d|debug      Display bash debugging info
  -h|host name  Server Name or IP
  -i|input file Filename to load
  -l|log        Display logging information
  -s|sep char   1 Character field sep
  -t|table name Data Table to load
  -u|user name  Database username
  -v|version    Display script version
 
  Note:  using multiple logging options increases the log level
  (i.e.) ${0##/*/} -lll
  Log level 3.
 
  ${0##/*/} -h 192.168.101.125 -u root -d KE_IC -t kris_inventory_sims -i product1.csv
  ${0##/*/} -h 192.168.101.125 -u root -d KE_IC -t ocr_header -i ocr.csv
 
	EOT
}    # ----------  end of function usage  ----------
 
#-----------------------------------------------------------------------
#  Handle command line arguments
#-----------------------------------------------------------------------
 
while getopts ":dh:i:ls:t:u:v" opt
do
  case $opt in
 
    d|db       )  database="$OPTARG";;
 
    h|host     )  host="$OPTARG";;
 
    i|input    )  inputfile="$OPTARG";;
 
    l|log      )  (( logging++ ));  [[ $logging -gt 2 ]] && set -x;;
 
    s|sep      )  sep="$OPTARG{0:1}";;
 
    t|table    )  table="$OPTARG";;
 
    u|user     )  user="$OPTARG";;
 
    v|version  )  echo "$0 -- Version $ScriptVersion"; exit 0   ;;
 
    \? )  echo -e "\n  Option does not exist : $OPTARG\n"
          usage; exit 1   ;;
 
  esac    # --- end of case ---
done
shift $(($OPTIND-1))
 
#CREATE TABLE `ocr_header` (
#  `companyid` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
#  `id` int(11) NOT NULL AUTO_INCREMENT,
#  `table_id` int(11) NOT NULL DEFAULT '0',
#  `table_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
#  `doc_type` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
#  `keywords` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
#  PRIMARY KEY (`id`)
#) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
 
#get a field list
fields=$(mysql -h $host -u $user $database -e "SHOW CREATE TABLE $table;" | 
  gsed 's/\\n/\n/g' | grep '^[[:space:]]' | 
  sed -e 's/^[^[:alnum:]]*//' -e 's/`.*,/,/g' |
  sed -e '/^date_stamp,$/d' -e '/^id,$/d' -e '/ KEY /d' -e '/^user_id,$/d' -e '/^loaded_from,$/d' |
  sed -e '$s/,//g')
 
#note that id is intentionally missing
# and lack of comma on last line
#companyid,
#table_id,
#table_name,
#doc_type,
#keywords
 
#LOAD DATA LOCAL INFILE 'product1.csv'
#INTO TABLE ocr_header
#FIELDS TERMINATED BY ','
#OPTIONALLY ENCLOSED BY """"
#LINES TERMINATED BY '\n' 
#(
#companyid,
#table_id,
#table_name,
#doc_type,
#keywords
#)
 
nl='\\n'
sql="
LOAD DATA LOCAL INFILE '$inputfile'
INTO TABLE $table
FIELDS TERMINATED BY '$sep'
OPTIONALLY ENCLOSED BY \"\"\"\"
LINES TERMINATED BY '$nl'"
 
echo -e "$sql 
(
$fields
)" #| mysql -h $host -u $user $database --local-infile
 
sql="UPDATE $table
SET loaded_from = 'product1.csv',
user_id = 'klr'
WHERE loaded_from is null;"
echo -e "$sql" #|  mysql -h $host -u $user $database

→ No CommentsTags: