Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/awk -f
- # Copyright (C) 2011 Charles
- #
- # This program is free software; you can redistribute it and/or modify
- # it under the terms of the GNU General Public License as published by
- # the Free Software Foundation; either version 2 of the License, or
- # (at your option) any later version.
- #
- # This program is distributed in the hope that it will be useful,
- # but WITHOUT ANY WARRANTY; without even the implied warranty of
- # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
- # GNU General Public License for more details.
- #
- # You should have received a copy of the GNU General Public License
- # along with this program; if not, write to the Free Software
- # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, US
- # Name: Outlook_CSV_to_OTRS_CSV.awk
- # Purpose:
- # Filters an Outlook contacts format CSV into a CSV for import to OTRS
- # database table customer_user
- # Usage: -h option for help (runs the usage function)
- # Environment:
- # Developed and tested on Slackware64 13.1 using
- # * Outlook CSV from Google Mail
- # * GNU Awk 3.1.8
- # * OTRS 3.0.8
- # History:
- # 24jun11 Version 0.0. Charles
- # * Initial version
- # TODO: in approx descending priority
- # * TBA
- # Programmers' notes:
- # * Global variables (distinguished by an initial upper-case letter):
- # - Debug_level 0 for no debugging. Usage message gives further detail.
- # - Email_field[ ] Email field names
- # - Email_field_maxidx
- # - Fax_field[ ] Fax field names
- # - Fax_field_maxidx
- # - Field[] Field values from the current Outlook CSV record
- # - Header_NF Number of fields in the Outlook CSV header
- # - My_name Last component of path by which this script was called
- # - Num_warings Number of warning messages
- # - Optarg An option's argument
- # - Opterr Function get_opt error control; set to 0 to suppress error messages
- # - Optind Index of the first non-option argument in ARGV
- # - Optopt An option's letter
- # - Phone_field[ ] Phone field names
- # - Phone_field_maxidx
- # - Record_num Input record number
- # - Address_fields_prefix[ ] Street field name prefixes
- # - Address_fields_prefix_maxidx
- # - Version This script's version
- # * Global variables for setter's use only (distinguished by an initial _):
- # - _opti Set by the get_opt() function
- # * Function call tree:
- #
- # BEGIN
- # |
- # +-- initialise
- # | |
- # | +-- get_opt
- # | |
- # | +-- usage
- # | |
- # | +-- ck_file
- # | |
- # | +-- analyse_header
- # |
- # +-- get_record
- # | |
- # | +-- get_field
- # |
- # +-- filter_record
- # |
- # +-- write_record
- # |
- # +-- finalise
- #
- # Utility functions not listed above
- # camel_case, ck_email, ck_phone, msg, my_getline
- # Function definitions in alphabetical order. Execution begins after the
- # last function definition (search for BEGIN).
- function analyse_header ( \
- i, field_name )
- {
- # Get number of fields from the header
- # (assumed not to contain commas or newlines in the field names)
- FS = ","
- my_getline()
- Header_NF = NF
- msg( "D10", "Header_NF is " Header_NF )
- # Note the field numbers required
- for ( i = 1; i < Header_NF; i++ )
- {
- field_name = gensub( /"/, "", "g", $(i) )
- msg( "D50", "analyse_header: field_name: " field_name )
- if ( field_name == "First Name" )
- {
- Outlook_CSV_field_idx[ "First Name" ] = i
- }
- else if ( field_name == "Middle Name" )
- {
- Outlook_CSV_field_idx[ "Middle Name" ] = i
- }
- else if ( field_name == "Last Name" )
- {
- Outlook_CSV_field_idx[ "Last Name" ] = i
- }
- else if ( field_name == "Title" )
- {
- Outlook_CSV_field_idx[ "Title" ] = i
- }
- else if ( field_name == "E-mail Address" )
- {
- msg( "D50", "analyse_header: E-mail Address is field " i )
- Outlook_CSV_field_idx[ "E-mail Address" ] = i
- }
- else if ( field_name == "E-mail 2 Address" )
- {
- Outlook_CSV_field_idx[ "E-mail 2 Address" ] = i
- }
- else if ( field_name == "E-mail 3 Address" )
- {
- Outlook_CSV_field_idx[ "E-mail 3 Address" ] = i
- }
- else if ( field_name == "Primary Phone" )
- {
- Outlook_CSV_field_idx[ "Primary Phone" ] = i
- }
- else if ( field_name == "Home Phone" )
- {
- Outlook_CSV_field_idx[ "Home Phone" ] = i
- }
- else if ( field_name == "Home Phone 2" )
- {
- Outlook_CSV_field_idx[ "Home Phone 2" ] = i
- }
- else if ( field_name == "Mobile Phone" )
- {
- Outlook_CSV_field_idx[ "Mobile Phone" ] = i
- }
- else if ( field_name == "Home Fax" )
- {
- Outlook_CSV_field_idx[ "Home Fax" ] = i
- }
- else if ( field_name == "Home Address" )
- {
- Outlook_CSV_field_idx[ "Home Address" ] = i
- }
- else if ( field_name == "Home Street" )
- {
- Outlook_CSV_field_idx[ "Home Street" ] = i
- }
- else if ( field_name == "Home Street 2" )
- {
- Outlook_CSV_field_idx[ "Home Street 2" ] = i
- }
- else if ( field_name == "Home Street 3" )
- {
- Outlook_CSV_field_idx[ "Home Street 3" ] = i
- }
- else if ( field_name == "Home City" )
- {
- Outlook_CSV_field_idx[ "Home City" ] = i
- }
- else if ( field_name == "Home Postal Code" )
- {
- Outlook_CSV_field_idx[ "Home Postal Code" ] = i
- }
- else if ( field_name == "Home Country" )
- {
- Outlook_CSV_field_idx[ "Home Country" ] = i
- }
- else if ( field_name == "Company Main Phone" )
- {
- Outlook_CSV_field_idx[ "Company Main Phone" ] = i
- }
- else if ( field_name == "Business Phone" )
- {
- Outlook_CSV_field_idx[ "Business Phone" ] = i
- }
- else if ( field_name == "Business Phone 2" )
- {
- Outlook_CSV_field_idx[ "Business Phone 2" ] = i
- }
- else if ( field_name == "Business Fax" )
- {
- Outlook_CSV_field_idx[ "Business Fax" ] = i
- }
- else if ( field_name == "Company" )
- {
- Outlook_CSV_field_idx[ "Company" ] = i
- }
- else if ( field_name == "Organizational ID Number" )
- {
- Outlook_CSV_field_idx[ "Organizational ID Number" ] = i
- }
- else if ( field_name == "Business Address" )
- {
- Outlook_CSV_field_idx[ "Business Address" ] = i
- }
- else if ( field_name == "Business Street" )
- {
- Outlook_CSV_field_idx[ "Business Street" ] = i
- }
- else if ( field_name == "Business Street 2" )
- {
- Outlook_CSV_field_idx[ "Business Street 2" ] = i
- }
- else if ( field_name == "Business Street 3" )
- {
- Outlook_CSV_field_idx[ "Business Street 3" ] = i
- }
- else if ( field_name == "Business City" )
- {
- Outlook_CSV_field_idx[ "Business City" ] = i
- }
- else if ( field_name == "Business Postal Code" )
- {
- Outlook_CSV_field_idx[ "Business Postal Code" ] = i
- }
- else if ( field_name == "Business Country" )
- {
- Outlook_CSV_field_idx[ "Business Country" ] = i
- }
- else if ( field_name == "Other Phone" )
- {
- Outlook_CSV_field_idx[ "Other Phone" ] = i
- }
- else if ( field_name == "Other Fax" )
- {
- Outlook_CSV_field_idx[ "Other Fax" ] = i
- }
- }
- }
- function camel_case ( string, \
- array, i, idxmax, my_return )
- {
- if ( string ~ /[A-Z]/ ) return string
- idxmax = split( string, array )
- for ( i = 1; i <= idxmax; i++ ) {
- my_return = my_return " " toupper( substr( array[ i ], 1, 1 ) ) tolower( substr( array[ i ], 2 ) )
- }
- return substr( my_return, 2 )
- }
- function ck_email ( email, field )
- {
- msg( "D90", "ck_email: checking: '" email "'" )
- if ( email == "" ) return ""
- # The regular exression range operator {n,m} cannot be used with gawk because
- # it requires a comand line option which cannot be supplied on an awk shebang
- # line; hence the use of two ? operators in the last component below.
- if ( email !~ /^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z][A-Za-z][A-Za-z]?[A-Za-z]?$/ )
- {
- msg( "W", "Record " Record_num ", field " field " invalid; discarded: " email )
- return ""
- }
- if ( length( email ) > DB_email_maxlen )
- {
- msg( "W", "Record " Record_num ", field " field " too long for OTRS; discarded: " email )
- return ""
- }
- return email
- }
- function ck_file ( file, use, \
- dir, escaped_dir, escaped_file )
- {
- escaped_file = gensub( /'/, "'\"'\"'", "g", file )
- if ( use == "input" ) {
- if ( system( "test -r '" escaped_file "'" ) == 0) return "OK"
- if ( system( "test -e '" escaped_file "'" ) == 0) return "does not exist"
- return "is not readable"
- }
- else
- {
- if ( system( "test -w '" escaped_file "'" ) == 0) return "OK"
- if ( system( "test -e '" escaped_file "'" ) == 0) return "not writeble"
- dir = gensub( /\/[^/]*$/, "", "", file )
- escaped_dir = gensub( /'/, "'\"'\"'", "g", dir )
- if ( system( "test -w '" escaped_dir "'" ) == 0) return "OK"
- return "does not exist and cannot be created"
- }
- }
- function ck_phone ( phone, field, \
- invalid_chars, len, s )
- {
- # Validate characters
- # TODO: make this configurable
- invalid_chars = gensub( /[-+ ()0-9]\//, " ", "g", phone )
- len = length( invalid_chars )
- if ( len > 0 ) {
- if ( len > 1 ) s = "s"
- msg( "W", "Invalid character" s " in record " Record_num ", field " field ": " \
- invalid_chars "; phone number discarded" )
- return ""
- }
- # Normalise
- # TODO: make this configurable
- phone = gensub( /[^+ ()0-9]/, " ", "g", phone )
- phone = gensub( / /, " ", "g", phone )
- phone = gensub( /^ /, "", "", phone )
- phone = gensub( / $/, "", "", phone )
- # No number
- if ( phone == "" ) return ""
- # Validate format
- # TODO: make this configurable
- if ( phone != /^(+[1-9][0-9]+ ?)?[0-9 ]*(\([0-9]*\))? ?[0-9 ]*$/ ) {
- msg( "W", "Invalid phone number format in record " Record_num ", field " field ": " \
- phone " (after normalisation); discarded" )
- return ""
- }
- # Check length
- if ( length( phone ) > DB_phone_maxlen ) {
- msg( "W", "Phone number too long for OTRS in record " Record_num ", field " field ": " \
- phone " (after normalisation); discarded" )
- return ""
- }
- return phone
- }
- function filter_record ( \
- array, change_by, change_time, city, country, create_by, create_time, \
- customer_id, data, email, fax, first_name, first_name_part, i, idxmax, \
- last_name, last_name_part, login, prefix, mobile, phone, \
- postal_address_found, state, street, title, valid_id, zip )
- {
- # Address fields
- # These are processed together because a consistent set is required
- # street
- postal_address_found = ""
- street = ""
- for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
- prefix = Address_fields_prefix[ i ]
- street = Field[ Outlook_CSV_field_idx[ prefix " Street" ] ]
- if ( street == "" ) continue
- postal_address_found = prefix
- data = Field[ Outlook_CSV_field_idx[ prefix " Street 2" ] ]
- if ( data == "" ) break
- street = street ", " data
- data = Field[ Outlook_CSV_field_idx[ prefix " Street 3" ] ]
- if ( data == "" ) break
- street = street ", " data
- break
- }
- if ( postal_address_found ) {
- state = Field[ Outlook_CSV_field_idx[ prefix " State" ] ]
- if ( state != "" ) street = street ", " state
- }
- if ( length( street ) > DB_street_maxlen ) {
- street = substr( street, 1, DB_street_maxlen )
- msg( "W", "Record " Record_num ": Street truncated for OTRS: '" street "'" )
- }
- # zip
- if ( postal_address_found ) {
- zip = Field[ Outlook_CSV_field_idx[ prefix "Postal Code" ] ]
- }
- else {
- for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
- prefix = Address_fields_prefix[ i ]
- zip = Field[ Outlook_CSV_field_idx[ prefix " Postal Code" ] ]
- if ( zip == "" ) continue
- postal_address_found = prefix
- break
- }
- }
- if ( length( zip ) > DB_zip_maxlen ) {
- zip = substr( zip, 1, DB_zip_maxlen )
- msg( "W", "Record " Record_num ": Postal Code truncated for OTRS: '" zip "'" )
- }
- # city
- if ( postal_address_found ) {
- city = Field[ Outlook_CSV_field_idx[ prefix " City" ] ]
- }
- else {
- for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
- prefix = Address_fields_prefix[ i ]
- city = Field[ Outlook_CSV_field_idx[ prefix " City" ] ]
- if ( city == "" ) continue
- postal_address_found = prefix
- break
- }
- }
- city = camel_case( city )
- if ( length( city ) > DB_city_maxlen ) {
- city = substr( city, 1, DB_city_maxlen )
- msg( "W", "Record " Record_num ": City truncated for OTRS: '" city "'" )
- }
- # country
- if ( postal_address_found ) {
- country = Field[ Outlook_CSV_field_idx[ prefix " Country" ] ]
- }
- else {
- for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
- prefix = Address_fields_prefix[ i ]
- country = Field[ Outlook_CSV_field_idx[ prefix " Country" ] ]
- if ( country == "" ) continue
- postal_address_found = prefix
- break
- }
- }
- country = camel_case( country )
- if ( length( country ) > DB_country_maxlen ) {
- country = substr( country, 1, DB_country_maxlen )
- msg( "W", "Record " Record_num ": Country truncated for OTRS: '" country "'" )
- }
- # The remaining fields (OTRS names) in alpahbetical order
- # change_by (required)
- change_by = 1
- # change_time (required)
- change_time = OTRS_time
- # create_by (required)
- create_by = 1
- # create_time (required)
- create_time = OTRS_time
- # customer_id
- # No eqivalent Outlook field; leave empty
- customer_id = ""
- # email (required)
- for ( i = 1; i <= Email_field_maxidx; i++ ) {
- email = Field[ Outlook_CSV_field_idx[ Email_field[ i ] ] ]
- email = ck_email( email, Email_field[ i ] )
- if ( email != "" ) break
- }
- if ( email == "" ) {
- msg( "W", "Record " Record_num ": no Email address; record discarded" )
- return ""
- }
- # fax
- for ( i = 1; i <= Fax_field_maxidx; i++ ) {
- fax = Field[ Outlook_CSV_field_idx[ Fax_field[ i ] ] ]
- fax = ck_phone( fax, Fax_field[ i ] )
- if ( fax != "" ) break
- }
- # first_name (required)
- first_name = Field[ Outlook_CSV_field_idx[ "First Name" ] ]
- if ( first_name != "" ) first_name = camel_case( first_name )
- else {
- msg( "W", "Record " Record_num " (" email "): no First Name" )
- # Fudge a First Name from any email address
- if ( email == "" ) {
- msg( "W", "... and no valid email address to fudge a First Name from; record discarded" )
- return ""
- }
- split( email, array, /[^[:alpha:]]/ )
- first_name = camel_case( array[ 1 ] ) " (fudged from email address)"
- msg( "W", "... set to " first_name )
- }
- if ( length( first_name ) > DB_first_name_maxlen ) {
- first_name = substr( first_name, 1, DB_first_name_maxlen )
- msg( "W", "Record " Record_num ": First Name truncated for OTRS: '" first_name "'" )
- }
- # last_name (required )
- last_name = Field[ Outlook_CSV_field_idx[ "Last Name" ] ]
- if ( last_name != "" ) {
- last_name = camel_case( last_name )
- if ( length( last_name ) > DB_last_name_maxlen ) {
- last_name = substr( last_name, 1, DB_last_name_maxlen )
- msg( "W", "Record " Record_num ": Last Name truncated for OTRS: '" last_name "'" )
- }
- }
- else {
- last_name = "."
- msg( "W", "Record " Record_num " (" email "): no Last Name; set to ." )
- }
- # login (required, must be unique)
- # TODO: make this configurable
- first_name_part = tolower( gensub( / \(fudged from email address\)/, "", "", first_name ) )
- if ( last_name != "." ) {
- last_name_part = "_" tolower( gensub( / \(fudged from email address\)/, "", "", last_name ) )
- }
- else last_name_part = ""
- login = first_name_part last_name_part Uniquing_no++
- # phone
- for ( i = 1; i <= Phone_field_maxidx; i++ ) {
- phone = Field[ Outlook_CSV_field_idx[ Phone_field[ i ] ] ]
- phone = ck_phone( phone, Phone_field[ i ] )
- if ( phone != "" ) break
- }
- # mobile
- mobile = Field[ Outlook_CSV_field_idx[ "Mobile Phone" ] ]
- mobile = ck_phone( mobile, "Mobile Phone" )
- # title
- title = Field[ Outlook_CSV_field_idx[ "Title" ] ]
- title = camel_case( title )
- if ( length( title ) > DB_title_maxlen ) {
- title = substr( title, 1, DB_title_maxlen )
- msg( "W", "Record " Record_num ": Title truncated for OTRS: '" title "'" )
- }
- # valid_id (required)
- valid_id = 1
- return change_by ",\"" change_time "\",\"" city "\",\"" country "\"," \
- create_by ",\"" create_time "\",\"" customer_id "\",\"" \
- email "\",\"" fax "\",\"" first_name "\",\"" last_name "\",\"" \
- login "\",\"" phone "\",\"" mobile "\",\"" street "\",\"" \
- title "\"," valid_id ",\"" zip "\""
- }
- function get_field ( n_field )
- {
- msg( "D100", "Started get_field()" )
- Field[ n_field ] = ""
- if ( length( $0 ) == 0 ) {
- msg( "D100", "Setting EoR empty field" )
- return
- }
- char = substr( $0, 1, 1 )
- msg( "D100", "Character is " char )
- if ( char == "," ) {
- $0 = substr( $0, 2 )
- return
- }
- if ( char != "\"" ) {
- msg( "E", "Line " Record_num ": char is '" char "' not \" as expected" )
- exit 1
- }
- for ( i = 2; ; i++ )
- {
- if ( length( $0 ) == i - 1 ) {
- my_getline()
- i = 1
- Field[ n_field ] = Field[ n_field ] "\\n"
- if ( length( $0 ) == 0 ) continue
- }
- char = substr( $0, i, 1 )
- msg( "D100", "Character is " char )
- if ( char == "\"" )
- {
- if ( length( $0 ) > i ) {
- next_char = substr( $0, i + 1, 1 )
- if ( next_char == "," ) {
- # End of field; discard following "," and return
- $0 = substr( $0, i + 2 )
- msg( "D100", "Field " n_field " is " Field[ n_field ] )
- return
- }
- else if (next_char == "\"" ) {
- # Is doubled " which is an embedded "
- i++
- }
- else {
- msg( "E", \
- "Line " Record_num ": char is '" char "' not \" as expected" )
- }
- }
- else {
- # End of field and end of record
- $0 = ""
- msg( "D100", "Field " n_field " is " Field[ n_field ] )
- return
- }
- }
- Field[ n_field ] = Field[ n_field ] char
- }
- }
- function finalise ( \
- now )
- {
- # TODO: why does the following line work in initialise() but not here?!
- #"date '+%Y/%m/%d@%H:%M:%S'" | getline now
- "date +%Y/%m/%d@%H:%M:%S" | getline now
- msg( "I", "Finishing at " now )
- if ( Num_warnings > 0 ) msg( "E", "There were " Num_warnings " warning messages" )
- msg( "I", "There were " Num_warnings " warning messages" )
- }
- function get_opt(argc, argv, options, \
- thisopt, i)
- {
- # From http://www.gnu.org/software/gawk/manual/gawk.html#Getopt-Function
- # Modified to return option letter of any invalid option instead of ?
- if (length(options) == 0) # no options given
- return -1
- if (argv[Optind] == "--") { # all done
- Optind++
- _opti = 0
- return -1
- } else if (argv[Optind] !~ /^-[^: \t\n\f\r\v\b]/) {
- _opti = 0
- return -1
- }
- if (_opti == 0)
- _opti = 2
- thisopt = substr(argv[Optind], _opti, 1)
- Optopt = thisopt
- i = index(options, thisopt)
- if (i == 0) {
- if (Opterr)
- printf("%c -- invalid option\n",
- thisopt) > "/dev/stderr"
- if (_opti >= length(argv[Optind])) {
- Optind++
- _opti = 0
- } else
- _opti++
- # Local modification
- #return "?"
- return thisopt
- }
- if (substr(options, i + 1, 1) == ":") {
- # get option argument
- if (length(substr(argv[Optind], _opti + 1)) > 0)
- Optarg = substr(argv[Optind], _opti + 1)
- else
- Optarg = argv[++Optind]
- _opti = 0
- } else
- Optarg = ""
- if (_opti == 0 || _opti >= length(argv[Optind])) {
- Optind++
- _opti = 0
- } else
- _opti++
- return thisopt
- }
- function get_record ( \
- n_field )
- {
- n_field = 1
- while ( n_field < Header_NF ) {
- get_field( n_field )
- msg( "D90", "Record " Record_num ", field " n_field ": " Field[ n_field] )
- n_field++
- }
- }
- function initialise ( \
- array, cmdline, errmsg, i, now, msg, opt )
- {
- Version = "0.0"
- # Get last component of path by which this script called
- # Has to be done now in case needed for the -h help option
- # /proc file system used because ARGV[0] value is system-dependent
- getline cmdline < "/proc/self/cmdline"
- split( cmdline, array, "\0" )
- My_name = substr( array[3], match( array[3], /\/[^\/]*$/ ) + 1 )
- # Parse command line
- Opterr = 0 # Suppress get_opt( ) error messages
- Optind = 1 # Skip ARGV[0]
- Debug_level = 0
- Input_file = "/dev/stdin"
- Log_file = "/dev/stderr"
- Output_file = "/dev/stdout"
- while ( ( opt = get_opt(ARGC, ARGV, "d:hi:l:o:u:V")) != -1)
- {
- msg( "D70", "initialise: opt: " opt )
- if ( opt == "d" ) {
- Debug_level = strtonum( Optarg )
- }
- else if ( opt == "h" ) {
- usage( "verbose" )
- exit 0
- }
- else if ( opt == "i" ) {
- Input_file = Optarg
- }
- else if ( opt == "l" ) {
- Log_file = Optarg
- }
- else if ( opt == "o" ) {
- Output_file = Optarg
- }
- else if ( opt == "u" ) {
- Uniquing_no = strtonum( Optarg )
- }
- else if ( opt == "V" ) {
- msg( "I", My_name " version " Version )
- exit 0
- }
- else {
- errmsg = "\n Invalid command line option: " opt
- show_usage = "true"
- }
- }
- for ( i = 1; i < Optind; i++ ) ARGV[i] = ""
- # Initialise logging
- Num_warnings = 0
- if ( Log_file != "/dev/stderr" ) {
- msg = ck_file( Log_file, "output" )
- if ( msg == "OK" ) printf "" > Log_file
- else {
- errmsg = "\n Log file " msg ": " Log_file
- Log_file = "/dev/stderr"
- }
- }
- "date '+%Y/%m/%d@%H:%M:%S'" | getline now
- cmdline = substr( cmdline, length( array[ 1 ] ) + length( array[ 2 ] ) + 3 )
- gsub( "\0", " ", cmdline )
- msg( "I", "Started version " Version " at " now " with command line " cmdline )
- # Check input and output files
- if ( Input_file != "/dev/stdin" ) {
- msg = ck_file( Input_file, "input" )
- if ( msg != "OK" ) errmsg = "\n Input file " msg ": " Input_file
- }
- if ( Output_file != "/dev/stdout" ) {
- msg = ck_file( Output_file, "output" )
- if ( msg != "OK" ) errmsg = "\n Output file " msg ": " Output_file
- }
- # Trap mandatory argument not given
- if ( Uniquing_no == "" ) {
- errmsg = "\n Mandatory option -u not given or had an empty argument"
- }
- # Report any errors
- if ( errmsg != "" ) {
- if ( show_usage ) usage( "quiet" )
- msg( "E", substr( errmsg, 2 ) )
- }
- # OTRS DB table customer_user
- # (output of MySQL show columns from customer_user; )
- # +-------------+--------------+------+-----+---------+----------------+
- # | Field | Type | Null | Key | Default | Extra |
- # +-------------+--------------+------+-----+---------+----------------+
- # | id | int(11) | NO | PRI | NULL | auto_increment |
- # | login | varchar(200) | NO | UNI | NULL | |
- # | email | varchar(150) | NO | | NULL | |
- # | customer_id | varchar(150) | NO | | NULL | |
- # | pw | varchar(64) | YES | | NULL | |
- # | title | varchar(50) | YES | | NULL | |
- # | first_name | varchar(100) | NO | | NULL | |
- # | last_name | varchar(100) | NO | | NULL | |
- # | phone | varchar(150) | YES | | NULL | |
- # | fax | varchar(150) | YES | | NULL | |
- # | mobile | varchar(150) | YES | | NULL | |
- # | street | varchar(150) | YES | | NULL | |
- # | zip | varchar(200) | YES | | NULL | |
- # | city | varchar(200) | YES | | NULL | |
- # | country | varchar(200) | YES | | NULL | |
- # | comments | varchar(250) | YES | | NULL | |
- # | valid_id | smallint(6) | NO | MUL | NULL | |
- # | create_time | datetime | NO | | NULL | |
- # | create_by | int(11) | NO | MUL | NULL | |
- # | change_time | datetime | NO | | NULL | |
- # | change_by | int(11) | NO | MUL | NULL | |
- # +-------------+--------------+------+-----+---------+----------------+
- # Set max lengths for columns that may be populated from Outlook CSV
- DB_email_maxlen=150
- DB_title_maxlen=50
- DB_first_name_maxlen=100
- DB_last_name_maxlen=100
- DB_phone_maxlen=150
- DB_fax_maxlen=150
- DB_mobile_maxlen=150
- DB_street_maxlen=150
- DB_zip_maxlen=200
- DB_city_maxlen=200
- DB_country_maxlen=200
- DB_comments_maxlen=100
- # Note numbers of Outlook CSV fields that may contain required data
- analyse_header( )
- # Email fields
- # The first listed which has a value in the input CSV is the one used.
- i = 0
- Email_field[ ++i ] = "E-mail Address"
- Email_field[ ++i ] = "E-mail 2 Address"
- Email_field[ ++i ] = "E-mail 3 Address"
- Email_field_maxidx = i
- # Fax number fields
- # The first listed which has a value in the input CSV is the one used.
- i = 0
- Fax_field[ ++i ] = "Home Fax"
- Fax_field[ ++i ] = "Business Fax"
- Fax_field[ ++i ] = "Other Fax"
- Fax_field_maxidx = i
- # Phone number fields
- # The first listed which has a value in the input CSV is the one used.
- # Mobile Phone should be last because it is itself an OTRS field.
- # Car Phone should probably be second last because few people now have a
- # car phone that is not their mobile number
- i = 0
- Phone_field[ ++i ] = "Primary Phone"
- Phone_field[ ++i ] = "Home Phone"
- Phone_field[ ++i ] = "Business Phone"
- Phone_field[ ++i ] = "Other Phone"
- Phone_field[ ++i ] = "Home Phone 2"
- Phone_field[ ++i ] = "Business Phone 2"
- Phone_field[ ++i ] = "Assistant's Phone"
- Phone_field[ ++i ] = "Company Main Phone"
- Phone_field[ ++i ] = "Radio Phone"
- Phone_field[ ++i ] = "TTY/TDD Phone"
- Phone_field[ ++i ] = "Car Phone"
- Phone_field[ ++i ] = "Mobile Phone"
- Phone_field_maxidx = i
- # Address fields prefixes
- # Outlook CSVs have three sets of fields for postal address data; OTRS has one
- i = 0
- Address_fields_prefix[ ++i ] = "Home"
- Address_fields_prefix[ ++i ] = "Business"
- Address_fields_prefix[ ++i ] = "Other"
- Street_field_maxidx = i
- # Generate OTRS change_time and modify_time string
- # Format is as displayed by MySQL for datetimes
- "date '+%Y-%m-%d %H:%M:%S'" | getline OTRS_time
- }
- function msg ( msg_class, msg_text,
- exit_flag, msg_debug_level )
- {
- exit_flag = 0
- if ( msg_class ~ /^D/ )
- {
- if ( ! Debug_level ) return
- if ( length( msg_class ) > 1 )
- msg_debug_level = strtonum( substr( msg_class, 2 ) )
- else msg_debug_level = 10 # Sane default
- if ( msg_debug_level > Debug_level ) return
- msg_text = "DEBUG: " msg_text
- }
- else if ( msg_class == "I" )
- {
- }
- else if ( msg_class == "W" )
- {
- msg_text = "WARNING: " msg_text
- Num_warnings++
- }
- else if ( msg_class == "E" )
- {
- msg_text = "ERROR: " msg_text
- exit_flag = 1
- }
- else
- {
- msg_text = "ERROR: msg: called with invalid message class " msg_class \
- "(and message text: " msg_text " )"
- exit_flag = 1
- }
- print msg_text >> Log_file
- if ( exit_flag ) exit 1
- }
- function my_getline ()
- {
- if ( ( getline < Input_file ) <= 0 ) {
- msg( "E", "Unexpected end of file or error: " ERRNO )
- }
- Record_num++
- }
- function usage ( verbosity )
- {
- msg( "I", "Usage:" \
- "\n " My_name " [-d debug_level] [-h] [-i file] [-l file] [-o file] [-V] [-v] u number" \
- )
- if ( verbosity == "verbose" )
- msg( "I", " Where:" \
- "\n -d sets the debug level" \
- "\n 1 to 100 gives progressively more detail" \
- "\n -h prints this help message and exits" \
- "\n -i specifies the input file (default stdin)" \
- "\n -l specifies the log file (default stderr)" \
- "\n -o specifies the output file (default stdout)" \
- "\n -u specifies the uniquing number to be used for the login field" \
- "\n To enable loading data to OTRS, must result in a unique login value." \
- "\n The generated login value is First Name + underscore + Last Name + incremented uniquing number." \
- "\n -v sets all alarms to visual" \
- "\n -V prints the version number and exits" \
- )
- else msg( "I", "(-h for help)" )
- }
- function write_record ( output )
- {
- print output >> Output_file
- }
- BEGIN {
- initialise()
- # Write output header
- print "\"change_by\",\"change_time\",\"city\",\"country\",\"create_by\"" \
- ",\"create_time\",\"customer_id\",\"email\",\"fax\",\"first_name\"" \
- ",\"last_name\",\"login\",\"phone\",\"mobile\",\"street\",\"title\"" \
- ",\"valid_id\",\"zip\"" > Output_file
- # Loop over all lines in Outlook CSV
- while ( ( getline < Input_file ) > 0 ) {
- Record_num++
- #if ( Record_num > 5 ) exit 0
- msg( "D10", "Processing record " Record_num )
- get_record()
- Output = filter_record()
- if ( Output ) write_record( Output )
- }
- # Clean up and get out of here
- finalise()
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement