catkin

Outlook_CSV_to_OTRS_CSV.awk

Jun 26th, 2011
450
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #!/usr/bin/awk -f
  2.  
  3. #   Copyright (C) 2011 Charles
  4. #
  5. #   This program is free software; you can redistribute it and/or modify
  6. #   it under the terms of the GNU General Public License as published by
  7. #   the Free Software Foundation; either version 2 of the License, or
  8. #   (at your option) any later version.
  9. #
  10. #   This program is distributed in the hope that it will be useful,
  11. #   but WITHOUT ANY WARRANTY; without even the implied warranty of
  12. #   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13. #   GNU General Public License for more details.
  14. #
  15. #   You should have received a copy of the GNU General Public License
  16. #   along with this program; if not, write to the Free Software
  17. #   Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, US
  18.  
  19. # Name: Outlook_CSV_to_OTRS_CSV.awk
  20.  
  21. # Purpose:
  22. #   Filters an Outlook contacts format CSV into a CSV for import to OTRS
  23. #   database table customer_user
  24.  
  25. # Usage: -h option for help (runs the usage function)
  26.  
  27. # Environment:
  28. #   Developed and tested on Slackware64 13.1 using
  29. #    * Outlook CSV from Google Mail
  30. #    * GNU Awk 3.1.8
  31. #    * OTRS 3.0.8
  32.  
  33. # History:
  34. #  24jun11 Version 0.0. Charles
  35. #    * Initial version
  36.  
  37. # TODO: in approx descending priority
  38. #   * TBA
  39.  
  40. # Programmers' notes:
  41. #   * Global variables (distinguished by an initial upper-case letter):
  42. #     - Debug_level  0 for no debugging. Usage message gives further detail.
  43. #     - Email_field[ ]  Email field names
  44. #     - Email_field_maxidx
  45. #     - Fax_field[ ]  Fax field names
  46. #     - Fax_field_maxidx
  47. #     - Field[]  Field values from the current Outlook CSV record
  48. #     - Header_NF  Number of fields in the Outlook CSV header
  49. #     - My_name  Last component of path by which this script was called
  50. #     - Num_warings  Number of warning messages
  51. #     - Optarg  An option's argument
  52. #     - Opterr  Function get_opt error control; set to 0 to suppress error messages
  53. #     - Optind  Index of the first non-option argument in ARGV
  54. #     - Optopt  An option's letter
  55. #     - Phone_field[ ]  Phone field names
  56. #     - Phone_field_maxidx
  57. #     - Record_num  Input record number
  58. #     - Address_fields_prefix[ ]  Street field name prefixes
  59. #     - Address_fields_prefix_maxidx
  60. #     - Version  This script's version
  61. #   * Global variables for setter's use only (distinguished by an initial _):
  62. #     - _opti  Set by the get_opt() function
  63. #   * Function call tree:
  64. #
  65. #   BEGIN
  66. #   |
  67. #   +-- initialise
  68. #   |   |
  69. #   |   +-- get_opt
  70. #   |   |
  71. #   |   +-- usage
  72. #   |   |
  73. #   |   +-- ck_file
  74. #   |   |
  75. #   |   +-- analyse_header
  76. #   |
  77. #   +-- get_record
  78. #   |   |
  79. #   |   +-- get_field
  80. #   |
  81. #   +-- filter_record
  82. #   |
  83. #   +-- write_record
  84. #   |
  85. #   +-- finalise
  86. #
  87. # Utility functions not listed above
  88. #  camel_case, ck_email, ck_phone, msg, my_getline
  89.  
  90. # Function definitions in alphabetical order.  Execution begins after the
  91. # last function definition (search for BEGIN).
  92.  
  93. function analyse_header ( \
  94.     i, field_name )
  95. {
  96.     # Get number of fields from the header
  97.     # (assumed not to contain commas or newlines in the field names)
  98.     FS = ","
  99.     my_getline()
  100.     Header_NF = NF
  101.     msg( "D10", "Header_NF is " Header_NF )
  102.  
  103.     # Note the field numbers required
  104.     for ( i = 1; i < Header_NF; i++ )
  105.     {
  106.         field_name = gensub( /"/, "", "g", $(i) )
  107.        msg( "D50", "analyse_header: field_name: " field_name )
  108.        if ( field_name == "First Name" )
  109.        {
  110.            Outlook_CSV_field_idx[ "First Name" ] = i
  111.        }
  112.        else if ( field_name == "Middle Name" )
  113.        {
  114.            Outlook_CSV_field_idx[ "Middle Name" ] = i
  115.        }
  116.        else if ( field_name == "Last Name" )
  117.        {
  118.            Outlook_CSV_field_idx[ "Last Name" ] = i
  119.        }
  120.        else if ( field_name == "Title" )
  121.        {
  122.            Outlook_CSV_field_idx[ "Title" ] = i
  123.        }
  124.        else if ( field_name == "E-mail Address" )
  125.        {
  126.            msg( "D50", "analyse_header: E-mail Address is field " i )
  127.            Outlook_CSV_field_idx[ "E-mail Address" ] = i
  128.        }
  129.        else if ( field_name == "E-mail 2 Address" )
  130.        {
  131.            Outlook_CSV_field_idx[ "E-mail 2 Address" ] = i
  132.        }
  133.        else if ( field_name == "E-mail 3 Address" )
  134.        {
  135.            Outlook_CSV_field_idx[ "E-mail 3 Address" ] = i
  136.        }
  137.        else if ( field_name == "Primary Phone" )
  138.        {
  139.            Outlook_CSV_field_idx[ "Primary Phone" ] = i
  140.        }
  141.        else if ( field_name == "Home Phone" )
  142.        {
  143.            Outlook_CSV_field_idx[ "Home Phone" ] = i
  144.        }
  145.        else if ( field_name == "Home Phone 2" )
  146.        {
  147.            Outlook_CSV_field_idx[ "Home Phone 2" ] = i
  148.        }
  149.        else if ( field_name == "Mobile Phone" )
  150.        {
  151.            Outlook_CSV_field_idx[ "Mobile Phone" ] = i
  152.        }
  153.        else if ( field_name == "Home Fax" )
  154.        {
  155.            Outlook_CSV_field_idx[ "Home Fax" ] = i
  156.        }
  157.        else if ( field_name == "Home Address" )
  158.        {
  159.            Outlook_CSV_field_idx[ "Home Address" ] = i
  160.        }
  161.        else if ( field_name == "Home Street" )
  162.        {
  163.            Outlook_CSV_field_idx[ "Home Street" ] = i
  164.        }
  165.        else if ( field_name == "Home Street 2" )
  166.        {
  167.            Outlook_CSV_field_idx[ "Home Street 2" ] = i
  168.        }
  169.        else if ( field_name == "Home Street 3" )
  170.        {
  171.            Outlook_CSV_field_idx[ "Home Street 3" ] = i
  172.        }
  173.        else if ( field_name == "Home City" )
  174.        {
  175.            Outlook_CSV_field_idx[ "Home City" ] = i
  176.        }
  177.        else if ( field_name == "Home Postal Code" )
  178.        {
  179.            Outlook_CSV_field_idx[ "Home Postal Code" ] = i
  180.        }
  181.        else if ( field_name == "Home Country" )
  182.        {
  183.            Outlook_CSV_field_idx[ "Home Country" ] = i
  184.        }
  185.        else if ( field_name == "Company Main Phone" )
  186.        {
  187.            Outlook_CSV_field_idx[ "Company Main Phone" ] = i
  188.        }
  189.        else if ( field_name == "Business Phone" )
  190.        {
  191.            Outlook_CSV_field_idx[ "Business Phone" ] = i
  192.        }
  193.        else if ( field_name == "Business Phone 2" )
  194.        {
  195.            Outlook_CSV_field_idx[ "Business Phone 2" ] = i
  196.        }
  197.        else if ( field_name == "Business Fax" )
  198.        {
  199.            Outlook_CSV_field_idx[ "Business Fax" ] = i
  200.        }
  201.        else if ( field_name == "Company" )
  202.        {
  203.            Outlook_CSV_field_idx[ "Company" ] = i
  204.        }
  205.        else if ( field_name == "Organizational ID Number" )
  206.        {
  207.            Outlook_CSV_field_idx[ "Organizational ID Number" ] = i
  208.        }
  209.        else if ( field_name == "Business Address" )
  210.        {
  211.            Outlook_CSV_field_idx[ "Business Address" ] = i
  212.        }
  213.        else if ( field_name == "Business Street" )
  214.        {
  215.            Outlook_CSV_field_idx[ "Business Street" ] = i
  216.        }
  217.        else if ( field_name == "Business Street 2" )
  218.        {
  219.            Outlook_CSV_field_idx[ "Business Street 2" ] = i
  220.        }
  221.        else if ( field_name == "Business Street 3" )
  222.        {
  223.            Outlook_CSV_field_idx[ "Business Street 3" ] = i
  224.        }
  225.        else if ( field_name == "Business City" )
  226.        {
  227.            Outlook_CSV_field_idx[ "Business City" ] = i
  228.        }
  229.        else if ( field_name == "Business Postal Code" )
  230.        {
  231.            Outlook_CSV_field_idx[ "Business Postal Code" ] = i
  232.        }
  233.        else if ( field_name == "Business Country" )
  234.        {
  235.            Outlook_CSV_field_idx[ "Business Country" ] = i
  236.        }
  237.        else if ( field_name == "Other Phone" )
  238.        {
  239.            Outlook_CSV_field_idx[ "Other Phone" ] = i
  240.        }
  241.        else if ( field_name == "Other Fax" )
  242.        {
  243.            Outlook_CSV_field_idx[ "Other Fax" ] = i
  244.        }
  245.    }
  246.  
  247. }
  248.  
  249. function camel_case ( string, \
  250.    array, i, idxmax, my_return )
  251. {
  252.    if ( string ~ /[A-Z]/ ) return string
  253.    idxmax = split( string, array )
  254.    for ( i = 1; i <= idxmax; i++ ) {
  255.        my_return = my_return " " toupper( substr( array[ i ], 1, 1 ) ) tolower( substr( array[ i ], 2 ) )
  256.    }
  257.    return substr( my_return, 2 )
  258. }
  259.  
  260. function ck_email ( email, field )
  261. {
  262.    msg( "D90", "ck_email: checking: '" email "'" )
  263.    if ( email == "" ) return ""
  264.    # The regular exression range operator {n,m} cannot be used with gawk because
  265.    # it requires a comand line option which cannot be supplied on an awk shebang
  266.    # line; hence the use of two ? operators in the last component below.
  267.    if ( email !~ /^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z][A-Za-z][A-Za-z]?[A-Za-z]?$/ )
  268.    {
  269.        msg( "W", "Record " Record_num ", field " field " invalid; discarded: " email )
  270.        return ""
  271.    }
  272.    if ( length( email ) > DB_email_maxlen )
  273.    {
  274.        msg( "W", "Record " Record_num ", field " field " too long for OTRS; discarded: " email )
  275.        return ""
  276.    }
  277.    return email
  278. }
  279.  
  280. function ck_file ( file, use, \
  281.    dir, escaped_dir, escaped_file )
  282. {
  283.    escaped_file = gensub( /'/, "'\"'\"'", "g", file )
  284.     if ( use == "input" ) {
  285.         if ( system( "test -r '" escaped_file "'" ) == 0) return "OK"
  286.         if ( system( "test -e '" escaped_file "'" ) == 0) return "does not exist"
  287.         return "is not readable"
  288.     }
  289.     else
  290.     {
  291.         if ( system( "test -w '" escaped_file "'" ) == 0) return "OK"
  292.         if ( system( "test -e '" escaped_file "'" ) == 0) return "not writeble"
  293.         dir = gensub( /\/[^/]*$/, "", "", file )
  294.         escaped_dir = gensub( /'/, "'\"'\"'", "g", dir )
  295.         if ( system( "test -w '" escaped_dir "'" ) == 0) return "OK"
  296.         return "does not exist and cannot be created"
  297.     }
  298. }
  299.  
  300. function ck_phone ( phone, field, \
  301.     invalid_chars, len, s )
  302. {
  303.     # Validate characters
  304.     # TODO: make this configurable
  305.     invalid_chars = gensub( /[-+ ()0-9]\//, " ", "g", phone )
  306.     len = length( invalid_chars )
  307.     if ( len > 0 ) {
  308.         if ( len > 1 ) s = "s"
  309.         msg( "W", "Invalid character" s " in record " Record_num ", field " field ": " \
  310.              invalid_chars "; phone number discarded" )    
  311.         return ""
  312.     }
  313.  
  314.     # Normalise
  315.     # TODO: make this configurable
  316.     phone = gensub( /[^+ ()0-9]/, " ", "g", phone )
  317.     phone = gensub( /  /, " ", "g", phone )
  318.     phone = gensub( /^ /, "", "", phone )
  319.     phone = gensub( / $/, "", "", phone )
  320.  
  321.     # No number
  322.     if ( phone == "" ) return ""
  323.  
  324.     # Validate format
  325.     # TODO: make this configurable
  326.     if ( phone != /^(+[1-9][0-9]+ ?)?[0-9 ]*(\([0-9]*\))? ?[0-9 ]*$/ ) {
  327.         msg( "W", "Invalid phone number format in record " Record_num ", field " field ": " \
  328.              phone " (after normalisation); discarded" )    
  329.         return ""
  330.     }
  331.  
  332.     # Check length
  333.     if ( length( phone ) > DB_phone_maxlen ) {
  334.         msg( "W", "Phone number too long for OTRS in record " Record_num ", field " field ": " \
  335.              phone " (after normalisation); discarded" )    
  336.         return ""
  337.     }
  338.  
  339.     return phone
  340. }
  341.  
  342. function filter_record ( \
  343.     array, change_by, change_time, city, country, create_by, create_time, \
  344.     customer_id, data, email, fax, first_name, first_name_part, i, idxmax, \
  345.     last_name, last_name_part, login, prefix, mobile, phone, \
  346.     postal_address_found, state, street, title, valid_id, zip )
  347. {
  348.     # Address fields
  349.     # These are processed together because a consistent set is required
  350.  
  351.     # street
  352.     postal_address_found = ""
  353.     street = ""
  354.     for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
  355.         prefix = Address_fields_prefix[ i ]
  356.         street = Field[ Outlook_CSV_field_idx[ prefix " Street" ] ]
  357.         if ( street == "" ) continue
  358.         postal_address_found = prefix
  359.         data = Field[ Outlook_CSV_field_idx[ prefix " Street 2" ] ]
  360.         if ( data == "" ) break
  361.         street = street ", " data
  362.         data = Field[ Outlook_CSV_field_idx[ prefix " Street 3" ] ]
  363.         if ( data == "" ) break
  364.         street = street ", " data
  365.         break
  366.     }
  367.     if ( postal_address_found ) {
  368.         state = Field[ Outlook_CSV_field_idx[ prefix " State" ] ]
  369.         if ( state != "" ) street = street ", " state
  370.     }
  371.     if ( length( street ) > DB_street_maxlen ) {
  372.         street = substr( street, 1, DB_street_maxlen )
  373.         msg( "W", "Record " Record_num ": Street truncated for OTRS: '" street "'" )
  374.     }
  375.  
  376.     # zip
  377.     if ( postal_address_found ) {
  378.         zip = Field[ Outlook_CSV_field_idx[ prefix "Postal Code" ] ]
  379.     }
  380.     else {
  381.         for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
  382.             prefix = Address_fields_prefix[ i ]
  383.             zip = Field[ Outlook_CSV_field_idx[ prefix " Postal Code" ] ]
  384.             if ( zip == "" ) continue
  385.             postal_address_found = prefix
  386.             break
  387.         }
  388.     }
  389.     if ( length( zip ) > DB_zip_maxlen ) {
  390.         zip = substr( zip, 1, DB_zip_maxlen )
  391.         msg( "W", "Record " Record_num ": Postal Code truncated for OTRS: '" zip "'" )
  392.     }
  393.  
  394.     # city
  395.     if ( postal_address_found ) {
  396.         city = Field[ Outlook_CSV_field_idx[ prefix " City" ] ]
  397.     }
  398.     else {
  399.         for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
  400.             prefix = Address_fields_prefix[ i ]
  401.             city = Field[ Outlook_CSV_field_idx[ prefix " City" ] ]
  402.             if ( city == "" ) continue
  403.             postal_address_found = prefix
  404.             break
  405.         }
  406.     }
  407.     city = camel_case( city )
  408.     if ( length( city ) > DB_city_maxlen ) {
  409.         city = substr( city, 1, DB_city_maxlen )
  410.         msg( "W", "Record " Record_num ": City truncated for OTRS: '" city "'" )
  411.     }
  412.    
  413.     # country
  414.     if ( postal_address_found ) {
  415.         country = Field[ Outlook_CSV_field_idx[ prefix " Country" ] ]
  416.     }
  417.     else {
  418.         for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
  419.             prefix = Address_fields_prefix[ i ]
  420.             country = Field[ Outlook_CSV_field_idx[ prefix " Country" ] ]
  421.             if ( country == "" ) continue
  422.             postal_address_found = prefix
  423.             break
  424.         }
  425.     }
  426.     country = camel_case( country )
  427.     if ( length( country ) > DB_country_maxlen ) {
  428.         country = substr( country, 1, DB_country_maxlen )
  429.         msg( "W", "Record " Record_num ": Country truncated for OTRS: '" country "'" )
  430.     }
  431.    
  432.     # The remaining fields (OTRS names) in alpahbetical order
  433.  
  434.     # change_by (required)
  435.     change_by = 1
  436.  
  437.     # change_time (required)
  438.     change_time = OTRS_time
  439.  
  440.     # create_by (required)
  441.     create_by = 1
  442.  
  443.     # create_time (required)
  444.     create_time = OTRS_time
  445.  
  446.     # customer_id
  447.     # No eqivalent Outlook field; leave empty
  448.     customer_id = ""
  449.  
  450.     # email (required)
  451.     for ( i = 1; i <= Email_field_maxidx; i++ ) {
  452.         email = Field[ Outlook_CSV_field_idx[ Email_field[ i ] ] ]
  453.         email = ck_email( email, Email_field[ i ] )
  454.         if ( email != "" ) break
  455.     }
  456.     if ( email == "" ) {
  457.         msg( "W", "Record " Record_num ": no Email address; record discarded" )
  458.         return ""
  459.     }
  460.  
  461.     # fax
  462.     for ( i = 1; i <= Fax_field_maxidx; i++ ) {
  463.         fax = Field[ Outlook_CSV_field_idx[ Fax_field[ i ] ] ]
  464.         fax = ck_phone( fax, Fax_field[ i ] )
  465.         if ( fax != "" ) break
  466.     }
  467.    
  468.     # first_name (required)
  469.     first_name = Field[ Outlook_CSV_field_idx[ "First Name" ] ]
  470.     if ( first_name != "" ) first_name = camel_case( first_name )
  471.     else {
  472.         msg( "W", "Record " Record_num " (" email "): no First Name" )
  473.         # Fudge a First Name from any email address
  474.         if ( email == "" ) {
  475.             msg( "W", "... and no valid email address to fudge a First Name from; record discarded" )
  476.             return ""
  477.         }
  478.         split( email, array, /[^[:alpha:]]/ )        
  479.         first_name = camel_case( array[ 1 ] ) " (fudged from email address)"
  480.         msg( "W", "... set to " first_name )
  481.     }
  482.     if ( length( first_name ) > DB_first_name_maxlen ) {
  483.         first_name = substr( first_name, 1, DB_first_name_maxlen )
  484.         msg( "W", "Record " Record_num ": First Name truncated for OTRS: '" first_name "'" )
  485.     }
  486.  
  487.     # last_name (required )
  488.     last_name = Field[ Outlook_CSV_field_idx[ "Last Name" ] ]
  489.     if ( last_name != "" ) {
  490.         last_name = camel_case( last_name )
  491.         if ( length( last_name ) > DB_last_name_maxlen ) {
  492.             last_name = substr( last_name, 1, DB_last_name_maxlen )
  493.             msg( "W", "Record " Record_num ": Last Name truncated for OTRS: '" last_name "'" )
  494.         }
  495.     }
  496.     else {
  497.         last_name = "."
  498.         msg( "W", "Record " Record_num " (" email "): no Last Name; set to ." )
  499.     }
  500.  
  501.     # login (required, must be unique)
  502.     # TODO: make this configurable
  503.     first_name_part = tolower( gensub( / \(fudged from email address\)/, "", "", first_name ) )
  504.     if ( last_name != "." ) {
  505.         last_name_part = "_" tolower( gensub( / \(fudged from email address\)/, "", "", last_name ) )
  506.     }
  507.     else last_name_part = ""
  508.     login = first_name_part last_name_part Uniquing_no++
  509.  
  510.     # phone
  511.     for ( i = 1; i <= Phone_field_maxidx; i++ ) {
  512.         phone = Field[ Outlook_CSV_field_idx[ Phone_field[ i ] ] ]
  513.         phone = ck_phone( phone, Phone_field[ i ] )
  514.         if ( phone != "" ) break
  515.     }
  516.  
  517.     # mobile
  518.     mobile = Field[ Outlook_CSV_field_idx[ "Mobile Phone" ] ]
  519.     mobile = ck_phone( mobile, "Mobile Phone" )
  520.  
  521.     # title
  522.     title = Field[ Outlook_CSV_field_idx[ "Title" ] ]
  523.     title = camel_case( title )
  524.     if ( length( title ) > DB_title_maxlen ) {
  525.         title = substr( title, 1, DB_title_maxlen )
  526.         msg( "W", "Record " Record_num ": Title truncated for OTRS: '" title "'" )
  527.     }
  528.    
  529.     # valid_id (required)
  530.     valid_id = 1
  531.  
  532.     return change_by ",\"" change_time "\",\"" city "\",\"" country "\"," \
  533.         create_by ",\"" create_time "\",\"" customer_id "\",\"" \
  534.         email "\",\"" fax "\",\"" first_name "\",\"" last_name "\",\"" \
  535.         login "\",\"" phone "\",\"" mobile "\",\"" street "\",\"" \
  536.         title "\"," valid_id ",\"" zip "\""
  537. }
  538.  
  539. function get_field ( n_field )
  540. {
  541.     msg( "D100", "Started get_field()" )
  542.     Field[ n_field ] = ""
  543.     if ( length( $0 ) == 0 ) {
  544.         msg( "D100", "Setting EoR empty field" )
  545.         return
  546.     }
  547.     char = substr( $0, 1, 1 )
  548.     msg( "D100", "Character is " char )
  549.     if ( char == "," ) {
  550.         $0 = substr( $0, 2 )
  551.         return
  552.     }
  553.     if ( char != "\"" ) {
  554.         msg( "E", "Line " Record_num ": char is '" char "' not \" as expected" )
  555.         exit 1
  556.     }
  557.     for ( i = 2; ; i++ )
  558.     {
  559.         if ( length( $0 ) == i - 1 ) {
  560.             my_getline()
  561.             i = 1
  562.             Field[ n_field ] = Field[ n_field ] "\\n"
  563.             if ( length( $0 ) == 0 ) continue
  564.         }
  565.         char = substr( $0, i, 1 )
  566.         msg( "D100", "Character is " char )
  567.         if ( char == "\"" )
  568.         {
  569.             if ( length( $0 ) > i ) {
  570.                 next_char = substr( $0, i + 1, 1 )
  571.                 if ( next_char == "," ) {
  572.                     # End of field; discard following "," and return
  573.                     $0 = substr( $0, i + 2 )
  574.                     msg( "D100", "Field " n_field " is " Field[ n_field ] )
  575.                     return
  576.                 }
  577.                 else if (next_char == "\"" ) {
  578.                     # Is doubled " which is an embedded "
  579.                     i++
  580.                 }
  581.                 else {
  582.                     msg( "E", \
  583.                         "Line " Record_num ": char is '" char "' not \" as expected" )
  584.                 }
  585.             }
  586.             else {
  587.                 # End of field and end of record
  588.                 $0 = ""
  589.                 msg( "D100", "Field " n_field " is " Field[ n_field ] )
  590.                 return
  591.             }
  592.         }
  593.         Field[ n_field ] = Field[ n_field ] char
  594.     }
  595.    
  596. }
  597.  
  598. function finalise ( \
  599.     now )
  600. {
  601.     # TODO: why does the following line work in initialise() but not here?!
  602.     #"date '+%Y/%m/%d@%H:%M:%S'" | getline now
  603.     "date +%Y/%m/%d@%H:%M:%S" | getline now
  604.     msg( "I", "Finishing at " now )
  605.     if ( Num_warnings > 0 ) msg( "E", "There were " Num_warnings " warning messages" )
  606.     msg( "I", "There were " Num_warnings " warning messages" )
  607. }
  608.  
  609. function get_opt(argc, argv, options, \
  610.     thisopt, i)
  611. {
  612.     # From http://www.gnu.org/software/gawk/manual/gawk.html#Getopt-Function
  613.     # Modified to return option letter of any invalid option instead of ?
  614.     if (length(options) == 0)    # no options given
  615.         return -1
  616.      
  617.     if (argv[Optind] == "--") {  # all done
  618.         Optind++
  619.         _opti = 0
  620.         return -1
  621.     } else if (argv[Optind] !~ /^-[^: \t\n\f\r\v\b]/) {
  622.         _opti = 0
  623.         return -1
  624.     }
  625.     if (_opti == 0)
  626.         _opti = 2
  627.     thisopt = substr(argv[Optind], _opti, 1)
  628.     Optopt = thisopt
  629.     i = index(options, thisopt)
  630.     if (i == 0) {
  631.         if (Opterr)
  632.            printf("%c -- invalid option\n",
  633.                thisopt) > "/dev/stderr"
  634.         if (_opti >= length(argv[Optind])) {
  635.            Optind++
  636.            _opti = 0
  637.         } else
  638.             _opti++
  639.         # Local modification
  640.         #return "?"
  641.         return thisopt
  642.     }
  643.     if (substr(options, i + 1, 1) == ":") {
  644.         # get option argument
  645.         if (length(substr(argv[Optind], _opti + 1)) > 0)
  646.            Optarg = substr(argv[Optind], _opti + 1)
  647.         else
  648.            Optarg = argv[++Optind]
  649.         _opti = 0
  650.     } else
  651.         Optarg = ""
  652.     if (_opti == 0 || _opti >= length(argv[Optind])) {
  653.         Optind++
  654.         _opti = 0
  655.     } else
  656.         _opti++
  657.     return thisopt
  658. }
  659.  
  660. function get_record ( \
  661.     n_field )
  662. {
  663.     n_field = 1
  664.     while ( n_field < Header_NF ) {
  665.         get_field( n_field )
  666.         msg( "D90", "Record " Record_num ", field " n_field ": " Field[ n_field] )
  667.         n_field++
  668.     }
  669. }
  670.  
  671. function initialise ( \
  672.     array, cmdline, errmsg, i, now, msg, opt )
  673. {
  674.     Version = "0.0"
  675.  
  676.     # Get last component of path by which this script called
  677.     # Has to be done now in case needed for the -h help option
  678.     # /proc file system used because ARGV[0] value is system-dependent
  679.     getline cmdline < "/proc/self/cmdline"
  680.     split( cmdline, array, "\0" )
  681.     My_name = substr( array[3], match( array[3], /\/[^\/]*$/ ) + 1 )
  682.  
  683.     # Parse command line
  684.     Opterr = 0    # Suppress get_opt( ) error messages
  685.     Optind = 1    # Skip ARGV[0]
  686.     Debug_level = 0
  687.     Input_file = "/dev/stdin"
  688.     Log_file = "/dev/stderr"
  689.     Output_file = "/dev/stdout"
  690.     while ( ( opt = get_opt(ARGC, ARGV, "d:hi:l:o:u:V")) != -1)
  691.     {
  692.         msg( "D70", "initialise: opt: " opt )
  693.         if ( opt == "d" ) {
  694.             Debug_level = strtonum( Optarg )
  695.         }
  696.         else if ( opt == "h" ) {
  697.             usage( "verbose" )
  698.             exit 0
  699.         }
  700.         else if ( opt == "i" ) {
  701.             Input_file = Optarg
  702.         }
  703.         else if ( opt == "l" ) {
  704.             Log_file = Optarg
  705.         }
  706.         else if ( opt == "o" ) {
  707.             Output_file = Optarg
  708.         }
  709.         else if ( opt == "u" ) {
  710.             Uniquing_no = strtonum( Optarg )
  711.         }
  712.         else if ( opt == "V" ) {
  713.             msg( "I", My_name " version " Version )
  714.             exit 0
  715.         }
  716.         else {
  717.             errmsg = "\n    Invalid command line option: " opt
  718.             show_usage = "true"
  719.         }
  720.     }
  721.     for ( i = 1; i < Optind; i++ ) ARGV[i] = ""
  722.  
  723.     # Initialise logging
  724.     Num_warnings = 0
  725.     if ( Log_file != "/dev/stderr" ) {
  726.         msg = ck_file( Log_file, "output" )
  727.         if ( msg == "OK" ) printf "" > Log_file
  728.         else {
  729.             errmsg = "\n    Log file " msg ": " Log_file
  730.             Log_file = "/dev/stderr"
  731.         }
  732.     }
  733.     "date '+%Y/%m/%d@%H:%M:%S'" | getline now
  734.     cmdline = substr( cmdline, length( array[ 1 ] ) + length( array[ 2 ] ) + 3 )
  735.     gsub( "\0", " ", cmdline )
  736.     msg( "I", "Started version " Version " at " now " with command line " cmdline )
  737.  
  738.     # Check input and output files
  739.     if ( Input_file != "/dev/stdin" ) {
  740.         msg = ck_file( Input_file, "input" )
  741.         if ( msg != "OK" ) errmsg = "\n    Input file " msg ": " Input_file
  742.     }
  743.     if ( Output_file != "/dev/stdout" ) {
  744.         msg = ck_file( Output_file, "output" )
  745.         if ( msg != "OK" ) errmsg = "\n    Output file " msg ": " Output_file
  746.     }
  747.  
  748.     # Trap mandatory argument not given
  749.     if ( Uniquing_no == "" ) {
  750.         errmsg = "\n    Mandatory option -u not given or had an empty argument"
  751.     }
  752.    
  753.     # Report any errors
  754.     if ( errmsg != "" ) {
  755.         if ( show_usage ) usage( "quiet" )
  756.         msg( "E", substr( errmsg, 2 ) )
  757.     }
  758.  
  759.     # OTRS DB table customer_user
  760.     # (output of MySQL show columns from customer_user; )
  761.     # +-------------+--------------+------+-----+---------+----------------+
  762.     # | Field       | Type         | Null | Key | Default | Extra          |
  763.     # +-------------+--------------+------+-----+---------+----------------+
  764.     # | id          | int(11)      | NO   | PRI | NULL    | auto_increment |
  765.     # | login       | varchar(200) | NO   | UNI | NULL    |                |
  766.     # | email       | varchar(150) | NO   |     | NULL    |                |
  767.     # | customer_id | varchar(150) | NO   |     | NULL    |                |
  768.     # | pw          | varchar(64)  | YES  |     | NULL    |                |
  769.     # | title       | varchar(50)  | YES  |     | NULL    |                |
  770.     # | first_name  | varchar(100) | NO   |     | NULL    |                |
  771.     # | last_name   | varchar(100) | NO   |     | NULL    |                |
  772.     # | phone       | varchar(150) | YES  |     | NULL    |                |
  773.     # | fax         | varchar(150) | YES  |     | NULL    |                |
  774.     # | mobile      | varchar(150) | YES  |     | NULL    |                |
  775.     # | street      | varchar(150) | YES  |     | NULL    |                |
  776.     # | zip         | varchar(200) | YES  |     | NULL    |                |
  777.     # | city        | varchar(200) | YES  |     | NULL    |                |
  778.     # | country     | varchar(200) | YES  |     | NULL    |                |
  779.     # | comments    | varchar(250) | YES  |     | NULL    |                |
  780.     # | valid_id    | smallint(6)  | NO   | MUL | NULL    |                |
  781.     # | create_time | datetime     | NO   |     | NULL    |                |
  782.     # | create_by   | int(11)      | NO   | MUL | NULL    |                |
  783.     # | change_time | datetime     | NO   |     | NULL    |                |
  784.     # | change_by   | int(11)      | NO   | MUL | NULL    |                |
  785.     # +-------------+--------------+------+-----+---------+----------------+
  786.  
  787.     # Set max lengths for columns that may be populated from Outlook CSV
  788.     DB_email_maxlen=150
  789.     DB_title_maxlen=50
  790.     DB_first_name_maxlen=100
  791.     DB_last_name_maxlen=100
  792.     DB_phone_maxlen=150
  793.     DB_fax_maxlen=150
  794.     DB_mobile_maxlen=150
  795.     DB_street_maxlen=150
  796.     DB_zip_maxlen=200
  797.     DB_city_maxlen=200
  798.     DB_country_maxlen=200
  799.     DB_comments_maxlen=100
  800.  
  801.     # Note numbers of Outlook CSV fields that may contain required data
  802.     analyse_header( )
  803.  
  804.     # Email fields
  805.     # The first listed which has a value in the input CSV is the one used.
  806.     i = 0
  807.     Email_field[ ++i ] = "E-mail Address"
  808.     Email_field[ ++i ] = "E-mail 2 Address"
  809.     Email_field[ ++i ] = "E-mail 3 Address"
  810.     Email_field_maxidx = i
  811.  
  812.     # Fax number fields
  813.     # The first listed which has a value in the input CSV is the one used.
  814.     i = 0
  815.     Fax_field[ ++i ] = "Home Fax"
  816.     Fax_field[ ++i ] = "Business Fax"
  817.     Fax_field[ ++i ] = "Other Fax"
  818.     Fax_field_maxidx = i
  819.  
  820.     # Phone number fields
  821.     # The first listed which has a value in the input CSV is the one used.
  822.     # Mobile Phone should be last because it is itself an OTRS field.
  823.     # Car Phone should probably be second last because few people now have a
  824.     # car phone that is not their mobile number
  825.     i = 0
  826.     Phone_field[ ++i ] = "Primary Phone"
  827.     Phone_field[ ++i ] = "Home Phone"
  828.     Phone_field[ ++i ] = "Business Phone"
  829.     Phone_field[ ++i ] = "Other Phone"
  830.     Phone_field[ ++i ] = "Home Phone 2"
  831.     Phone_field[ ++i ] = "Business Phone 2"
  832.     Phone_field[ ++i ] = "Assistant's Phone"
  833.     Phone_field[ ++i ] = "Company Main Phone"
  834.     Phone_field[ ++i ] = "Radio Phone"
  835.     Phone_field[ ++i ] = "TTY/TDD Phone"
  836.     Phone_field[ ++i ] = "Car Phone"
  837.     Phone_field[ ++i ] = "Mobile Phone"
  838.     Phone_field_maxidx = i
  839.  
  840.     # Address fields prefixes
  841.     # Outlook CSVs have three sets of fields for postal address data; OTRS has one
  842.     i = 0
  843.     Address_fields_prefix[ ++i ] = "Home"
  844.     Address_fields_prefix[ ++i ] = "Business"
  845.     Address_fields_prefix[ ++i ] = "Other"
  846.     Street_field_maxidx = i
  847.  
  848.     # Generate OTRS change_time and modify_time string
  849.     # Format is as displayed by MySQL for datetimes
  850.     "date '+%Y-%m-%d %H:%M:%S'" | getline OTRS_time
  851. }
  852.  
  853. function msg ( msg_class, msg_text,
  854.     exit_flag, msg_debug_level )
  855. {
  856.     exit_flag = 0
  857.     if ( msg_class ~ /^D/ )
  858.     {
  859.         if ( ! Debug_level ) return
  860.         if ( length( msg_class ) > 1 )
  861.             msg_debug_level = strtonum( substr( msg_class, 2 ) )
  862.         else msg_debug_level = 10 # Sane default
  863.         if ( msg_debug_level > Debug_level ) return
  864.         msg_text = "DEBUG: " msg_text
  865.     }
  866.     else if ( msg_class == "I" )
  867.     {
  868.     }
  869.     else if ( msg_class == "W" )
  870.     {
  871.         msg_text = "WARNING: " msg_text
  872.         Num_warnings++
  873.     }
  874.     else if ( msg_class == "E" )
  875.     {
  876.         msg_text = "ERROR: " msg_text
  877.         exit_flag = 1
  878.     }
  879.     else
  880.     {
  881.         msg_text =  "ERROR: msg: called with invalid message class " msg_class \
  882.             "(and message text: " msg_text " )"
  883.         exit_flag = 1
  884.     }
  885.     print msg_text >> Log_file
  886.     if ( exit_flag ) exit 1
  887. }
  888.  
  889. function my_getline ()
  890. {
  891.     if ( ( getline < Input_file ) <= 0 ) {
  892.         msg( "E", "Unexpected end of file or error: " ERRNO )
  893.     }
  894.     Record_num++
  895. }
  896.  
  897. function usage ( verbosity )
  898. {
  899.     msg( "I", "Usage:" \
  900.         "\n  " My_name " [-d debug_level] [-h] [-i file] [-l file] [-o file] [-V] [-v] u number" \
  901.     )
  902.     if ( verbosity == "verbose" )
  903.         msg( "I", "  Where:" \
  904.             "\n    -d sets the debug level" \
  905.             "\n       1 to 100 gives progressively more detail" \
  906.             "\n    -h prints this help message and exits" \
  907.             "\n    -i specifies the input file (default stdin)" \
  908.             "\n    -l specifies the log file (default stderr)" \
  909.             "\n    -o specifies the output file (default stdout)" \
  910.             "\n    -u specifies the uniquing number to be used for the login field" \
  911.             "\n       To enable loading data to OTRS, must result in a unique login value." \
  912.             "\n       The generated login value is First Name + underscore + Last Name + incremented uniquing number." \
  913.             "\n    -v sets all alarms to visual" \
  914.             "\n    -V prints the version number and exits" \
  915.         )
  916.     else msg( "I", "(-h for help)" )
  917. }
  918.  
  919. function write_record ( output )
  920. {
  921.     print output >> Output_file
  922. }
  923.  
  924. BEGIN {
  925.     initialise()
  926.  
  927.     # Write output header
  928.     print "\"change_by\",\"change_time\",\"city\",\"country\",\"create_by\"" \
  929.         ",\"create_time\",\"customer_id\",\"email\",\"fax\",\"first_name\"" \
  930.         ",\"last_name\",\"login\",\"phone\",\"mobile\",\"street\",\"title\"" \
  931.         ",\"valid_id\",\"zip\"" > Output_file
  932.  
  933.     # Loop over all lines in Outlook CSV
  934.     while ( ( getline < Input_file ) > 0 ) {
  935.         Record_num++
  936.         #if ( Record_num > 5 ) exit 0
  937.         msg( "D10", "Processing record " Record_num )
  938.         get_record()
  939.         Output = filter_record()
  940.         if ( Output ) write_record( Output )
  941.     }
  942.  
  943.     # Clean up and get out of here
  944.     finalise()
  945. }
RAW Paste Data