Max Chung
2014-05-02
Maps the columns of an imported CSV file into the correct order.
Function List
string formatAddress(string address) Formats address to convert to abbreviate avenue, street, and drive.
string formatPostalCode(string postalCode) Formats postal codes to convert to upper case and add spaces.
string phone_letters_conv(string data) Converts letters in phone numbers to numbers (case insensitive).
string formatPhoneNum(string phone) Formats phone numbers to convert letters in phone numbers to
numbers and add dashes. Returns -1 if unknown phone number format.
boolean checkURL(string URL) Checks if the supplied URL is in the correct format. Returns
false if invalid URL format.
boolean checkEmail(string email) Checks if the supplied email address is in the correct format.
Returns false if invalid email format.
string state_abbr(string data) Converts state/province names to their abbreviations
(case insensitive).
array csv_to_array(string filename, string delimiter) Opens a CSV file for reading and parses it to create an array.
void array_to_csv(string filename, array data) Saves the array to a CSV file. Overwrites existing file.
array csv_mapping(string data, array mapping_params) Parses an array and maps the values in a new array order.
*/
function formatAddress($address='') {
// Declare an array of words to match
// https://gullele.wordpress.com/tag/preg_match-for-specific-words/
// http://www.canadapost.ca/tools/pg/manual/PGaddress-e.asp
// \s = whitespace, \b = space or end of line, \w+ one or more words, \w* zero or more words
$patterns = array();
$patterns[0] = '/\savenue\.|\savenue\b|\save\.|\save\b/i';
$patterns[1] = '/\sstreet\.|\sstreet\b|\sst\.|\sst\b/i';
$patterns[2] = '/\sroad\.|\sroad\b|\srd\.|\srd\b/i';
$patterns[3] = '/\slane\.|\slane\b|\sln\.|\sln\b/i';
$patterns[4] = '/\sdrive\.|\sdrive\b|\sdr\.|\sdr\b/i';
$patterns[5] = '/\splace\.|\splace\b|\spl\.|\spl\b/i';
$patterns[6] = '/\sboulevard\.|\sboulevard\b|\sblvd\.|\sblvd\b/i';
$patterns[7] = '/\sterrace\.|\sterrace\b|\sterr\.|\sterr\b/i';
$patterns[8] = '/\scircle\.|\scircle\b|\scir\.|\scir\b/i';
$patterns[9] = '/\sapartment\.|\sapartment\b|\sapt\.|\sapt\b/i';
$patterns[10] = '/\scourt\.|\scourt\b|\sct\.|\sct\b/i';
$patterns[11] = '/\shighway\.|\shighway\b|\shwy\.|\shwy\b/i';
$patterns[12] = '/\sroute\.|\sroute\b|\srt\.|\srt\b/i';
$patterns[13] = '/\sturnpike\.|\sturnpike\b|\stpk\.|\stpk\b/i';
$patterns[14] = '/\sparkway\.|\sparkway\b|\spky\.|\spky\b/i';
$patterns[15] = '/\screscent\.|\screscent\b|\screc\.|\screc\b/i';
$patterns[16] = '/\sway\b/i';
$patterns[17] = '/\snorth|\sn\.|\sn/i';
$patterns[18] = '/\seast|\se\.|\se/i';
$patterns[19] = '/\ssouth|\ss\.|\ss/i';
$patterns[20] = '/\swest|\sw\.|\sw/i';
// Declare an array of words to replace the matches with
$replacements = array();
$replacements[0] = ' Ave.';
$replacements[1] = ' St.';
$replacements[2] = ' Rd.';
$replacements[3] = ' Ln.';
$replacements[4] = ' Dr.';
$replacements[5] = ' Pl.';
$replacements[6] = ' Blvd.';
$replacements[7] = ' Terr.';
$replacements[8] = ' Cir.';
$replacements[9] = ' Apt.';
$replacements[10] = ' Ct.';
$replacements[11] = ' Hwy.';
$replacements[12] = ' Rt.';
$replacements[13] = ' Tpk.';
$replacements[14] = ' Pky.';
$replacements[15] = ' Crec.';
$replacements[16] = ' Way';
$replacements[17] = ' N';
$replacements[18] = ' E';
$replacements[19] = ' S';
$replacements[20] = ' W';
$address = preg_replace($patterns, $replacements, $address);
// Extract the suite number out of the address
$suite = preg_match('/\ssuite\\s\w+/i', $address, $matches);
if ( isset($matches[0]) ) {
$suite = explode(' ', $matches[0])[2];
//echo $suite;
}
// Remove the suite number out of the address string
$address = preg_replace('/\ssuite\\s\w+/i', ' ', $address);
// Remove leftover characters from the end of the address string
$address = preg_replace('/\.\,/i', '', $address);
if ( isset($matches[0]) ) {
// Append the suite number to the start of the address
$address = $suite . '-' . $address;
}
// Enclose directions with asterisks for easy selection
$directions = array();
$directions[0] = '/\sNE\s|\sNE\b/';
$directions[1] = '/\sNW\s|\sNW\b/';
$directions[2] = '/\sSE\s|\sSE\b/';
$directions[3] = '/\sSW\s|\sSW\b/';
$directions[4] = '/\sN\s|\sN\b/';
$directions[5] = '/\sE\s|\sE\b/';
$directions[6] = '/\sS\s|\sS\b/';
$directions[7] = '/\sW\s|\sW\b/';
$replacements = array();
$replacements[0] = '*NE*';
$replacements[1] = '*NW*';
$replacements[2] = '*SE*';
$replacements[3] = '*SW*';
$replacements[4] = '*N*';
$replacements[5] = '*E*';
$replacements[6] = '*S*';
$replacements[7] = '*W*';
$address = preg_replace($directions, $replacements, $address);
// Extract the direction from the address
unset($matches);
if (
preg_match('/\*NE\*/i', $address, $matches) or
preg_match('/\*NW\*/i', $address, $matches) or
preg_match('/\*SE\*/i', $address, $matches) or
preg_match('/\*SW\*/i', $address, $matches) or
preg_match('/\*N\*/i', $address, $matches) or
preg_match('/\*E\*/i', $address, $matches) or
preg_match('/\*S\*/i', $address, $matches) or
preg_match('/\*W\*/i', $address, $matches)
) {
if ( preg_match('/\*NE\*/i', $address, $matches) ) {
// Remove the direction from the address
$address = preg_replace('/\*NE\*/i', ' ', $address);
} else if ( preg_match('/\*NW\*/i', $address, $matches) ) {
// Remove the direction from the address
$address = preg_replace('/\*NW\*/i', ' ', $address);
} else if ( preg_match('/\*SE\*/i', $address, $matches) ) {
// Remove the direction from the address
$address = preg_replace('/\*SE\*/i', ' ', $address);
} else if ( preg_match('/\*SW\*/i', $address, $matches) ) {
// Remove the direction from the address
$address = preg_replace('/\*SW\*/i', ' ', $address);
} else if ( preg_match('/\*N\*/i', $address, $matches) ) {
// Remove the direction from the address
$address = preg_replace('/\*N\*/i', ' ', $address);
} else if ( preg_match('/\*E\*/i', $address, $matches) ) {
// Remove the direction from the address
$address = preg_replace('/\*E\*/i', ' ', $address);
} else if ( preg_match('/\*S\*/i', $address, $matches) ) {
// Remove the direction from the address
$address = preg_replace('/\*S\*/i', ' ', $address);
} else if ( preg_match('/\*W\*/i', $address, $matches) ) {
// Remove the direction from the address
$address = preg_replace('/\*W\*/i', ' ', $address);
}
// Remove leftover characters from the end of the address string
$address = rtrim($address, " ,");
// Remove the asterisks from the direction
$matches[0] = str_replace('*', '', $matches[0]);
// Append the direction to the end of the address
$address = $address . ' ' . $matches[0];
}
// Remove space before and after a hypen
$address = preg_replace('/\s-\s/', '-', $address);
// Remove 'P.O. Box' and 'Post Office Box' from address
$address = preg_replace('/p\.o\.\sbox\s\w+/i', '', $address);
$address = preg_replace('/post\soffice\sbox\s\w+/i', '', $address);
return $address;
}
function formatPostalCode($postalCode='') {
// Remove all spaces from the postal code
$postalCode = str_replace(' ', '', $postalCode);
// Convert all postal codes to upper case
$postalCode = strtoupper($postalCode);
// http://www.sitepoint.com/forums/showthread.php?511404-add-space-in-middle-of-string
// Adds a space after the first three characters of a postal code
if ( strlen($postalCode) == 6 and ctype_alnum($postalCode) ) {
$postalCode = substr($postalCode, 0, 3) . " " . substr($postalCode, 3);
}
return $postalCode;
}
function phone_letters_conv($data='') {
// Declare an array of letters to match
// http://www.dialabc.com/words/translate/index.html
$letters = array();
$letters[0] = '/a/i';
$letters[1] = '/b/i';
$letters[2] = '/c/i';
$letters[3] = '/d/i';
$letters[4] = '/e/i';
$letters[5] = '/f/i';
$letters[6] = '/g/i';
$letters[7] = '/h/i';
$letters[8] = '/i/i';
$letters[9] = '/j/i';
$letters[10] = '/k/i';
$letters[11] = '/l/i';
$letters[12] = '/m/i';
$letters[13] = '/n/i';
$letters[14] = '/o/i';
$letters[15] = '/p/i';
$letters[16] = '/q/i';
$letters[17] = '/r/i';
$letters[18] = '/s/i';
$letters[19] = '/t/i';
$letters[20] = '/u/i';
$letters[21] = '/v/i';
$letters[22] = '/w/i';
$letters[23] = '/x/i';
$letters[24] = '/y/i';
$letters[25] = '/z/i';
// Declare an array of numbers to replace the letters with
$numbers = array();
$numbers[0] = '2';
$numbers[1] = '2';
$numbers[2] = '2';
$numbers[3] = '3';
$numbers[4] = '3';
$numbers[5] = '3';
$numbers[6] = '4';
$numbers[7] = '4';
$numbers[8] = '4';
$numbers[9] = '5';
$numbers[10] = '5';
$numbers[11] = '5';
$numbers[12] = '6';
$numbers[13] = '6';
$numbers[14] = '6';
$numbers[15] = '7';
$numbers[16] = '7';
$numbers[17] = '7';
$numbers[18] = '7';
$numbers[19] = '8';
$numbers[20] = '8';
$numbers[21] = '8';
$numbers[22] = '9';
$numbers[23] = '9';
$numbers[24] = '9';
$numbers[25] = '9';
$data = preg_replace($letters, $numbers, $data);
return $data;
}
function formatPhoneNum($phone='') {
// Remove all characters leaving just the numbers
$phone = str_replace(array('(', ')', '-', '.', ' '), '', $phone);
// Convert all the letters in the phone numbers to numbers
$phone = phone_letters_conv($phone);
// http://stackoverflow.com/a/4708314
// Matches 123-456-7890
if ( preg_match( '/^(\d{3})(\d{3})(\d{4})$/', $phone, $matches ) ) {
$result = $matches[1] . '-' .$matches[2] . '-' . $matches[3];
return $result;
// Matches 123-4567
} else if ( preg_match( '/^(\d{3})(\d{4})$/', $phone, $matches ) ) {
$result = $matches[1] . '-' . $matches[2];
return $result;
// Matches 1-123-456-7890
} else if ( preg_match( '/^(\d{1})(\d{3})(\d{3})(\d{4})$/', $phone, $matches ) ) {
$result = $matches[1] . '-' . $matches[2] . '-' .$matches[3] . '-' . $matches[4];
return $result;
} else {
// Unknown phone number format
return -1;
}
}
function checkURL($URL='') {
// http://stackoverflow.com/a/6427654
$URLFormat = "((https?|ftp)\:\/\/)?"; // SCHEME
$URLFormat .= "([a-z0-9+!*(),;?&=\$_.-]+(\:[a-z0-9+!*(),;?&=\$_.-]+)?@)?"; // User and Pass
$URLFormat .= "([a-z0-9-.]*)\.([a-z]{2,4})"; // Host or IP
$URLFormat .= "(\:[0-9]{2,5})?"; // Port
$URLFormat .= "(\/([a-z0-9+\$_-]\.?)+)*\/?"; // Path
$URLFormat .= "(\?[a-z+&\$_.-][a-z0-9;:@&%=+\/\$_.-]*)?"; // GET Query
$URLFormat .= "(#[a-z_.-][a-z0-9+\$_.-]*)?"; // Anchor
if ( preg_match("/^$URLFormat$/", $URL) ) {
return true;
} else {
// Invalid URL
return false;
}
}
function checkEmail($email='') {
// http://www.jessenicola.com/blog/rfc2822-regex-js-regex-email-validation
$pattern = '/^([^\x00-\x20\x22\x28\x29\x2c\x2e\x3a-\x3c\x3e\x40\x5b-\x5d\x7f-\xff]+|\x22([^\x0d\x22\x5c\x80-\xff]|\x5c[\x00-\x7f])*\x22)(\x2e([^\x00-\x20\x22\x28\x29\x2c\x2e\x3a-\x3c\x3e\x40\x5b-\x5d\x7f-\xff]+|\x22([^\x0d\x22\x5c\x80-\xff]|\x5c[\x00-\x7f])*\x22))*\x40([^\x00-\x20\x22\x28\x29\x2c\x2e\x3a-\x3c\x3e\x40\x5b-\x5d\x7f-\xff]+|\x5b([^\x0d\x5b-\x5d\x80-\xff]|\x5c[\x00-\x7f])*\x5d)(\x2e([^\x00-\x20\x22\x28\x29\x2c\x2e\x3a-\x3c\x3e\x40\x5b-\x5d\x7f-\xff]+|\x5b([^\x0d\x5b-\x5d\x80-\xff]|\x5c[\x00-\x7f])*\x5d))*$/';
if ( preg_match($pattern, $email) ) {
return true;
} else {
// Invalid email address
return false;
}
}
function state_abbr($data='') {
// Declare an array of state names to match
// http://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations
// http://en.wikipedia.org/wiki/Provinces_and_territories_of_Canada
$states = array();
$states[0] = '/Alabama/i';
$states[1] = '/Alaska/i';
$states[2] = '/Arizona/i';
$states[3] = '/Arkansas/i';
$states[4] = '/California/i';
$states[5] = '/Colorado/i';
$states[6] = '/Connecticut/i';
$states[7] = '/Delaware/i';
$states[8] = '/District of Columbia/i';
$states[9] = '/Florida/i';
$states[10] = '/Georgia/i';
$states[11] = '/Hawaii/i';
$states[12] = '/Idaho/i';
$states[13] = '/Illinois/i';
$states[14] = '/Indiana/i';
$states[15] = '/Iowa/i';
$states[16] = '/Kansas/i';
$states[17] = '/Kentucky/i';
$states[18] = '/Louisiana/i';
$states[19] = '/Maine/i';
$states[20] = '/Maryland/i';
$states[21] = '/Massachusetts/i';
$states[22] = '/Michigan/i';
$states[23] = '/Minnesota/i';
$states[24] = '/Mississippi/i';
$states[25] = '/Missouri/i';
$states[26] = '/Montana/i';
$states[27] = '/Nebraska/i';
$states[28] = '/Nevada/i';
$states[29] = '/New Hampshire/i';
$states[30] = '/New Jersey/i';
$states[31] = '/New Mexico/i';
$states[32] = '/New York/i';
$states[33] = '/North Carolina/i';
$states[34] = '/North Dakota/i';
$states[35] = '/Ohio/i';
$states[36] = '/Oklahoma/i';
$states[37] = '/Oregon/i';
$states[38] = '/Pennsylvania/i';
$states[39] = '/Rhode Island/i';
$states[40] = '/South Carolina/i';
$states[41] = '/South Dakota/i';
$states[42] = '/Tennessee/i';
$states[43] = '/Texas/i';
$states[44] = '/Utah/i';
$states[45] = '/Vermont/i';
$states[46] = '/Virginia/i';
$states[47] = '/Washington/i';
$states[48] = '/West Virginia/i';
$states[49] = '/Wisconsin/i';
$states[50] = '/Wyoming/i';
$states[51] = '/Ontario/i';
$states[52] = '/Quebec/i';
$states[53] = '/Nova Scotia/i';
$states[54] = '/New Brunswick/i';
$states[55] = '/Manitoba/i';
$states[56] = '/British Columbia/i';
$states[57] = '/Prince Edward Island/i';
$states[58] = '/Saskatchewan/i';
$states[59] = '/Alberta/i';
$states[60] = '/Newfoundland/i';
$states[61] = '/Labrador/i';
// Declare an array of abbreviations to replace the state names with
$abbrs = array();
$abbrs[0] = 'AL';
$abbrs[1] = 'AK';
$abbrs[2] = 'AZ';
$abbrs[3] = 'AR';
$abbrs[4] = 'CA';
$abbrs[5] = 'CO';
$abbrs[6] = 'CT';
$abbrs[7] = 'DE';
$abbrs[8] = 'DC';
$abbrs[9] = 'FL';
$abbrs[10] = 'GA';
$abbrs[11] = 'HI';
$abbrs[12] = 'ID';
$abbrs[13] = 'IL';
$abbrs[14] = 'IN';
$abbrs[15] = 'IA';
$abbrs[16] = 'KS';
$abbrs[17] = 'KY';
$abbrs[18] = 'LA';
$abbrs[19] = 'ME';
$abbrs[20] = 'MD';
$abbrs[21] = 'MA';
$abbrs[22] = 'MI';
$abbrs[23] = 'MN';
$abbrs[24] = 'MS';
$abbrs[25] = 'MO';
$abbrs[26] = 'MT';
$abbrs[27] = 'NE';
$abbrs[28] = 'NV';
$abbrs[29] = 'NH';
$abbrs[30] = 'NJ';
$abbrs[31] = 'NM';
$abbrs[32] = 'NY';
$abbrs[33] = 'NC';
$abbrs[34] = 'ND';
$abbrs[35] = 'OH';
$abbrs[36] = 'OK';
$abbrs[37] = 'OR';
$abbrs[38] = 'PA';
$abbrs[39] = 'RI';
$abbrs[40] = 'SC';
$abbrs[41] = 'SD';
$abbrs[42] = 'TN';
$abbrs[43] = 'TX';
$abbrs[44] = 'UT';
$abbrs[45] = 'VT';
$abbrs[46] = 'VA';
$abbrs[47] = 'WA';
$abbrs[48] = 'WV';
$abbrs[49] = 'WI';
$abbrs[50] = 'WY';
$abbrs[50] = 'ON';
$abbrs[50] = 'QC';
$abbrs[50] = 'NS';
$abbrs[50] = 'NB';
$abbrs[50] = 'MB';
$abbrs[50] = 'BC';
$abbrs[50] = 'PE';
$abbrs[50] = 'SK';
$abbrs[50] = 'AB';
$abbrs[50] = 'NL';
$abbrs[50] = 'NL';
$data = preg_replace($states, $abbrs, $data);
return $data;
}
function csv_to_array($filename='', $delimiter=',') {
if(!file_exists($filename) || !is_readable($filename))
return FALSE;
$header = NULL;
$data = array();
// Try to open the CSV file for reading
if (($handle = fopen($filename, 'r')) !== FALSE) {
$data = array();
while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
// Parse each row of the CSV file and add it to the end of an array called data
array_push($data, $row);
}
fclose($handle);
//var_dump($result[53]);
}
return $data;
}
function array_to_csv($filename='', $data='') {
// http://php.net/manual/en/function.fputcsv.php
// Open the file for writing. Overwrites the existing file.
$fp = fopen($filename, 'w');
foreach ($data as $fields) {
// Writes the contents of the array, data into the file row by row
fputcsv($fp, $fields);
}
fclose($fp);
}
function csv_mapping($data='', $mapping_params) {
$i = 0;
$new_mapping = array();
// Loop through each row (record) of the imported CSV file
foreach ($data as $row_key => $row) {
// Loop through each column value (field) of the imported CSV file
foreach ($row as $col_key => &$col) {
// Company
if ($col_key == $mapping_params['company'][0]) {
$new_mapping[$row_key][ $mapping_params['company'][1] ] = $col;
}
// Full Name
if ($col_key == $mapping_params['full_name'][0]) {
// Ignore if the name contains inc., ltd., co., /, or \ (case-insensitive)
if ( stripos($col, 'Inc.') == false and stripos($col, 'ltd.') == false and stripos($col, 'co.') == false and stripos($col, '/') == false and stripos($col, '\\') == false ) {
// If the name has two spaces split by two spaces
if ( stripos($col, ' ') == true ) {
$name = explode(' ', $col);
} else {
// Split the full name into first and last names
$name = explode(' ', $col);
}
// A name only has a first and a last name
if ( count($name) == 2 or stripos($col, 'mary') == true or stripos($col, 'von') == true ) {
// First Name
$new_mapping[$row_key][ $mapping_params['full_name'][1] ] = $name[0];
// Last Name
if ( stripos($col, 'mary') == true or stripos($col, 'von') == true ) {
$new_mapping[$row_key][ $mapping_params['full_name'][1]+1 ] = $name[2];
} else {
$new_mapping[$row_key][ $mapping_params['full_name'][1]+1 ] = $name[1];
}
} else {
$new_mapping[$row_key][ $mapping_params['full_name'][1] ] = '';
$new_mapping[$row_key][ $mapping_params['full_name'][1]+1 ] = '';
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the name column because the name contains more than three parts or less than two parts.
';
}
} else {
$new_mapping[$row_key][ $mapping_params['full_name'][1] ] = '';
$new_mapping[$row_key][ $mapping_params['full_name'][1]+1 ] = '';
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the name column because the name contains inc, ltd., co., /, or \.
';
}
}
// Primary Phone
if ($col_key == $mapping_params['primary_phone'][0]) {
$phone = $col;
$phone = formatPhoneNum($phone);
if ( formatPhoneNum($phone) != -1 ) {
$new_mapping[$row_key][ $mapping_params['primary_phone'][1] ] = formatPhoneNum($phone);
} else {
$new_mapping[$row_key][ $mapping_params['primary_phone'][1] ] = '';
if ($col != '') {
// Do not log warnings for blank phone numbers
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the primary phone column because the phone number is invalid.
';
}
}
}
// Business Fax
if ($col_key == $mapping_params['business_fax'][0]) {
$phone = $col;
$phone = formatPhoneNum($phone);
if ( formatPhoneNum($phone) != -1 ) {
$new_mapping[$row_key][ $mapping_params['business_fax'][1] ] = formatPhoneNum($phone);
} else {
$new_mapping[$row_key][ $mapping_params['business_fax'][1] ] = '';
if ($col != '') {
// Do not log warnings for blank phone numbers
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the business fax column because the phone number is invalid.
';
}
}
}
// Other Phone
if ($col_key == $mapping_params['other_phone'][0]) {
$phone = $col;
$phone = formatPhoneNum($phone);
if ( formatPhoneNum($phone) != -1 ) {
$new_mapping[$row_key][ $mapping_params['other_phone'][1] ] = formatPhoneNum($phone);
} else {
$new_mapping[$row_key][ $mapping_params['other_phone'][1] ] = '';
if ($col != '') {
// Do not log warnings for blank phone numbers
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the other phone column because the phone number is invalid.
';
}
}
}
// Job Title
if ($col_key == $mapping_params['job_title'][0]) {
$new_mapping[$row_key][ $mapping_params['job_title'][1] ] = $col;
}
// Business Street
if ($col_key == $mapping_params['business_street'][0]) {
$street = $col;
$street = formatAddress($street);
$new_mapping[$row_key][ $mapping_params['business_street'][1] ] = $street;
}
// Business City
if ($col_key == $mapping_params['business_city'][0]) {
$new_mapping[$row_key][ $mapping_params['business_city'][1] ] = $col;
}
// Business State
if ($col_key == $mapping_params['business_state'][0]) {
$state = $col;
$state = state_abbr($state);
$state = strtoupper($state);
if ( strlen($state) <= 2 ) {
$new_mapping[$row_key][ $mapping_params['business_state'][1] ] = $state;
} else {
$new_mapping[$row_key][ $mapping_params['business_state'][1] ] = '';
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the business state column because the state is invalid.
';
}
}
// Business Postal Code
if ($col_key == $mapping_params['business_postal_code'][0]) {
$postal_code = $col;
$postal_code = formatPostalCode($postal_code);
$new_mapping[$row_key][ $mapping_params['business_postal_code'][1] ] = $postal_code;
}
// Mobile Phone
if ($col_key == $mapping_params['mobile_phone'][0]) {
$phone = $col;
$phone = formatPhoneNum($phone);
if ( formatPhoneNum($phone) != -1 ) {
$new_mapping[$row_key][ $mapping_params['mobile_phone'][1] ] = formatPhoneNum($phone);
} else {
$new_mapping[$row_key][ $mapping_params['mobile_phone'][1] ] = '';
if ($col != '') {
// Do not log warnings for blank phone numbers
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the mobile phone column because the phone number is invalid.
';
}
}
}
// Pager
if ($col_key == $mapping_params['pager'][0]) {
$phone = $col;
$phone = formatPhoneNum($phone);
if ( formatPhoneNum($phone) != -1 ) {
$new_mapping[$row_key][ $mapping_params['pager'][1] ] = formatPhoneNum($phone);
} else {
$new_mapping[$row_key][ $mapping_params['pager'][1] ] = '';
if ($col != '') {
// Do not log warnings for blank phone numbers
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the pager column because the phone number is invalid.
';
}
}
}
// Email Address
if ($col_key == $mapping_params['email_address'][0]) {
$email = $col;
if ( checkEmail($email) == true ) {
$new_mapping[$row_key][ $mapping_params['email_address'][1] ] = $email;
} else {
// Invalid email address.
$new_mapping[$row_key][ $mapping_params['email_address'][1] ] = '';
if ($col != '') {
// Do not log warnings for blank email addresses
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the email address column because the email address is invalid.
';
}
}
}
// Web Page
if ($col_key == $mapping_params['web_page'][0]) {
$URL = $col;
if ( checkURL($URL) == true ) {
$new_mapping[$row_key][ $mapping_params['web_page'][1] ] = $col;
// Check if the email value for the previous field is a valid URL and is not an email address
} else if ( checkURL($email) == true and !stripos($email, '@') ) {
// Is a valid URL and not a valid email so map to the web page field
$new_mapping[$row_key][ $mapping_params['web_page'][1] ] = $email;
echo 'Row ', $i+1 ,': \'' , $email , '\' has been moved from the email address column to the web page column.
';
unset($email);
} else {
// Invalid URl
$new_mapping[$row_key][ $mapping_params['web_page'][1] ] = '';
if ($col != '') {
// Do not log warnings for blank URLs
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the web page column because the URL is invalid.
';
}
}
}
/*
// Web Page
if ($col_key == $mapping_params['web_page'][0]) {
$URL = $col;
if ( checkURL($URL) == true ) {
$new_mapping[$row_key][ $mapping_params['web_page'][1] ] = $col;
} else {
// Invalid URl
$new_mapping[$row_key][ $mapping_params['web_page'][1] ] = '';
if ($col != '') {
// Do not log warnings for blank URLs
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the web page column because the URL is invalid.
';
}
}
}
*/
// Notes
if ($col_key == $mapping_params['notes'][0]) {
$new_mapping[$row_key][ $mapping_params['notes'][1] ] = $col;
}
// Home Street
if ($col_key == $mapping_params['home_street'][0]) {
$street = $col;
$street = formatAddress($street);
$new_mapping[$row_key][ $mapping_params['home_street'][1] ] = $street;
}
// Home State
if ($col_key == $mapping_params['home_state'][0]) {
$state = $col;
$state = state_abbr($state);
$state = strtoupper($state);
if ( strlen($state) <= 2 ) {
$new_mapping[$row_key][ $mapping_params['home_state'][1] ] = $state;
} else {
$new_mapping[$row_key][ $mapping_params['home_state'][1] ] = '';
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the home state column because the state is invalid.
';
}
}
// Home Postal Code
if ($col_key == $mapping_params['home_postal_code'][0]) {
$postal_code = $col;
$postal_code = formatPostalCode($postal_code);
$new_mapping[$row_key][ $mapping_params['home_postal_code'][1] ] = $postal_code;
}
// Home Phone
if ($col_key == $mapping_params['home_phone'][0]) {
$phone = $col;
$phone = formatPhoneNum($phone);
if ( formatPhoneNum($phone) != -1 ) {
$new_mapping[$row_key][ $mapping_params['home_phone'][1] ] = formatPhoneNum($phone);
} else {
$new_mapping[$row_key][ $mapping_params['home_phone'][1] ] = '';
if ($col != '') {
// Do not log warnings for blank phone numbers
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the home phone column because the phone number is invalid.
';
}
}
}
// Home Fax
if ($col_key == $mapping_params['home_fax'][0]) {
$phone = $col;
$phone = formatPhoneNum($phone);
if ( formatPhoneNum($phone) != -1 ) {
$new_mapping[$row_key][ $mapping_params['home_fax'][1] ] = formatPhoneNum($phone);
} else {
$new_mapping[$row_key][ $mapping_params['home_fax'][1] ] = '';
if ($col != '') {
// Do not log warnings for blank phone numbers
echo 'Row ', $i+1 ,': \'' , $col , '\' has been omitted from the home fax column because the phone number is invalid.
';
}
}
}
//var_dump($col);
}
// Order the row array entries by numerical order (by the index value)
ksort($new_mapping[$i]);
$i++;
}
return $new_mapping;
}
$result = csv_to_array('Database.csv', ',');
//var_dump($result);
// The mapping params array is in the following format:
// Field type, imported CSV array location, new mapping array location
// Array locations start from zero!
$mapping_params = array( 'company' => array(0 , 0),
'full_name' => array(57, 1),
'primary_phone' => array(29, 3),
'business_fax' => array(30, 4),
'other_phone' => array(28, 5),
'job_title' => array(1 , 6),
'business_street' => array(6 , 7),
'business_city' => array(9 , 8),
'business_state' => array(10, 9),
'business_postal_code' => array(11, 10),
'mobile_phone' => array(38, 11),
'pager' => array(39, 12),
'email_address' => array(55, 13),
'web_page' => array(89, 14),
'notes' => array(56, 15),
'home_street' => array(13, 16),
'home_state' => array(17, 17),
'home_postal_code' => array(18, 18),
'home_phone' => array(35, 19),
'home_fax' => array(36, 20)
);
$result2 = csv_mapping($result, $mapping_params);
//var_dump($result2);
array_to_csv('database edited.csv', $result2);
?>