Removing commas and quotes while inserting into DB from flat file

Report · Feb 25, 2012

Any help would be much appreciated...

I have a script that is reading the contents of comma deliminated file where the each piece of data is surrounding by double quotes. So, for example, data exists like ("John","Smith","Acme Corporation"...). I have hobbled together a script that will read the flat file, remove the quotes and insert the data in my database. The other thing that the script is doing too is reading the country and making some swaps in the names for other purposes down stream.

Here's my dilema: If someone enters a comma in any of the form fields, for instance in the address1 field ("100 Main Street, Suite 102"), the Insert statement is treating this as a new column and it throws off all the data. I've tried making various modifications using regular expressions, but nothing I am doing works right now, only further beaking it. I have no control over the form that is collecting the data... it's got to be resolved in this code here.

Please forgive any rudimentary mistakes you migth see

Here's the code:

/**

* Fixes a list by replacing null entries.

* This is a modified version of the ListFix UDF

* written by Raymond Camden. It is significantly

* faster when parsing larger strings with nulls.

* Version 2 was by Patrick McElhaney (pmcelhaney@amcity.com)

*

* @Param list The list to parse. (Required)

* @Param delimiter The delimiter to use. Defaults to a comma. (Optional)

* @Param null Null string to insert. Defaults to "NULL". (Optional)

* @Return Returns a list.

* @author Steven Van Gemert (pmcelhaney@@amcity.comsvg2@placs.net)

* @version 3, July 31, 2004

*/

function listFix(list) {

var delim = ",";

var null = " ";

var special_char_list = "\,+,*,?,.,[,],^,$,(,),{,},|,-";

var esc_special_char_list = "\\,\+,\*,\?,\.,\[,\],\^,\$,$,$,\{,\},\|,\-";

var i = "";

if(arrayLen(arguments) gt 1) delim = arguments[2];

if(arrayLen(arguments) gt 2) null = arguments[3];

if(findnocase(left(list, 1),delim)) list = null & list;

if(findnocase(right(list,1),delim)) list = list & null;

i = len(delim) - 1;

while(i GTE 1){

delim = mid(delim,1,i) & "_Separator_" & mid(delim,i+1,len(delim) - (i));

i = i - 1;

}

delim = ReplaceList(delim, special_char_list, esc_special_char_list);

delim = Replace(delim, "_Separator_", "|", "ALL");

list = rereplace(list, "(" & delim & ")(" & delim & ")", "\1" & null & "\2", "ALL");

return list;

}

</cfscript>

<cfif "#listgetAt('#index#',11, ',')#" contains "Afghanistan"

OR "#listgetAt('#index#',11, ',')#" contains "Aland Islands"

OR "#listgetAt('#index#',11, ',')#" contains "Albania"

OR "#listgetAt('#index#',11, ',')#" contains "Algeria"

OR "#listgetAt('#index#',11, ',')#" contains "Andorra"

OR "#listgetAt('#index#',11, ',')#" contains "Angola"

OR "#listgetAt('#index#',11, ',')#" contains "Argentina"

OR "#listgetAt('#index#',11, ',')#" contains "Armenia"

OR "#listgetAt('#index#',11, ',')#" contains "Australia"

OR "#listgetAt('#index#',11, ',')#" contains "Austria"

OR "#listgetAt('#index#',11, ',')#" contains "Azerbaijan"

OR "#listgetAt('#index#',11, ',')#" contains "Bahrain"

OR "#listgetAt('#index#',11, ',')#" contains "Belarus"

OR "#listgetAt('#index#',11, ',')#" contains "Belgium"

OR "#listgetAt('#index#',11, ',')#" contains "Benin"

OR "#listgetAt('#index#',11, ',')#" contains "Bosnia & Herzegovina"

OR "#listgetAt('#index#',11, ',')#" contains "Botswana"

OR "#listgetAt('#index#',11, ',')#" contains "Bouvet Island"

OR "#listgetAt('#index#',11, ',')#" contains "British Indian Ocean Territory"

OR "#listgetAt('#index#',11, ',')#" contains "Bulgaria"

OR "#listgetAt('#index#',11, ',')#" contains "Burkina Faso"

OR "#listgetAt('#index#',11, ',')#" contains "Burundi"

OR "#listgetAt('#index#',11, ',')#" contains "Cameroon"

OR "#listgetAt('#index#',11, ',')#" contains "Canada"

OR "#listgetAt('#index#',11, ',')#" contains "Cape Verdi"

OR "#listgetAt('#index#',11, ',')#" contains "Central African Republic"

OR "#listgetAt('#index#',11, ',')#" contains "Chad"

OR "#listgetAt('#index#',11, ',')#" contains "Comoros and Mayotte"

OR "#listgetAt('#index#',11, ',')#" contains "Congo"

OR "#listgetAt('#index#',11, ',')#" contains "Congo, The Democratic Republic of the"

OR "#listgetAt('#index#',11, ',')#" contains "Croatia"

OR "#listgetAt('#index#',11, ',')#" contains "Cyprus"

OR "#listgetAt('#index#',11, ',')#" contains "Czech Republic"

OR "#listgetAt('#index#',11, ',')#" contains "Denmark"

OR "#listgetAt('#index#',11, ',')#" contains "Djibouti"

OR "#listgetAt('#index#',11, ',')#" contains "Egypt"

OR "#listgetAt('#index#',11, ',')#" contains "Equatorial Guinea"

OR "#listgetAt('#index#',11, ',')#" contains "Eritrea"

OR "#listgetAt('#index#',11, ',')#" contains "Estonia"

OR "#listgetAt('#index#',11, ',')#" contains "Ethiopia"

OR "#listgetAt('#index#',11, ',')#" contains "Falkland Islands"

OR "#listgetAt('#index#',11, ',')#" contains "Faroe Islands"

OR "#listgetAt('#index#',11, ',')#" contains "Finland"

OR "#listgetAt('#index#',11, ',')#" contains "French Polynesia"

OR "#listgetAt('#index#',11, ',')#" contains "Gabon"

OR "#listgetAt('#index#',11, ',')#" contains "Gambia"

OR "#listgetAt('#index#',11, ',')#" contains "Georgia"

OR "#listgetAt('#index#',11, ',')#" contains "Germany"

OR "#listgetAt('#index#',11, ',')#" contains "Ghana"

OR "#listgetAt('#index#',11, ',')#" contains "Gibraltar"

OR "#listgetAt('#index#',11, ',')#" contains "Greece"

OR "#listgetAt('#index#',11, ',')#" contains "Greenland"

OR "#listgetAt('#index#',11, ',')#" contains "Guernsey"

OR "#listgetAt('#index#',11, ',')#" contains "Guinea"

OR "#listgetAt('#index#',11, ',')#" contains "Guinea-Bissau"

OR "#listgetAt('#index#',11, ',')#" contains "Hungary"

OR "#listgetAt('#index#',11, ',')#" contains "Iceland"

OR "#listgetAt('#index#',11, ',')#" contains "Ireland"

OR "#listgetAt('#index#',11, ',')#" contains "Isle of Man"

OR "#listgetAt('#index#',11, ',')#" contains "Israel"

OR "#listgetAt('#index#',11, ',')#" contains "Italy"

OR "#listgetAt('#index#',11, ',')#" contains "Ivory Coast"

OR "#listgetAt('#index#',11, ',')#" contains "Japan"

OR "#listgetAt('#index#',11, ',')#" contains "Jersey"

OR "#listgetAt('#index#',11, ',')#" contains "Latvia"

OR "#listgetAt('#index#',11, ',')#" contains "Lebanon"

OR "#listgetAt('#index#',11, ',')#" contains "Lesotho"

OR "#listgetAt('#index#',11, ',')#" contains "Liberia"

OR "#listgetAt('#index#',11, ',')#" contains "Libya"

OR "#listgetAt('#index#',11, ',')#" contains "Liechtenstein"

OR "#listgetAt('#index#',11, ',')#" contains "Lithuania"

OR "#listgetAt('#index#',11, ',')#" contains "Luxembourg"

OR "#listgetAt('#index#',11, ',')#" contains "Macedonia"

OR "#listgetAt('#index#',11, ',')#" contains "Madagascar"

OR "#listgetAt('#index#',11, ',')#" contains "Malawi"

OR "#listgetAt('#index#',11, ',')#" contains "Mali"

OR "#listgetAt('#index#',11, ',')#" contains "Malta"

OR "#listgetAt('#index#',11, ',')#" contains "Mauritania"

OR "#listgetAt('#index#',11, ',')#" contains "Mauritius"

OR "#listgetAt('#index#',11, ',')#" contains "Mayotte"

OR "#listgetAt('#index#',11, ',')#" contains "Moldova"

OR "#listgetAt('#index#',11, ',')#" contains "Montenegro"

OR "#listgetAt('#index#',11, ',')#" contains "Morocco"

OR "#listgetAt('#index#',11, ',')#" contains "Mozambique"

OR "#listgetAt('#index#',11, ',')#" contains "Namibia"

OR "#listgetAt('#index#',11, ',')#" contains "Netherlands"

OR "#listgetAt('#index#',11, ',')#" contains "New Caledonia"

OR "#listgetAt('#index#',11, ',')#" contains "New Zealand"

OR "#listgetAt('#index#',11, ',')#" contains "Niger"

OR "#listgetAt('#index#',11, ',')#" contains "Nigeria"

OR "#listgetAt('#index#',11, ',')#" contains "Norway"

OR "#listgetAt('#index#',11, ',')#" contains "Oman"

OR "#listgetAt('#index#',11, ',')#" contains "Pakistan"

OR "#listgetAt('#index#',11, ',')#" contains "Palestinian Territory"

OR "#listgetAt('#index#',11, ',')#" contains "Pitcairn"

OR "#listgetAt('#index#',11, ',')#" contains "Poland"

OR "#listgetAt('#index#',11, ',')#" contains "Portugal"

OR "#listgetAt('#index#',11, ',')#" contains "Qatar"

OR "#listgetAt('#index#',11, ',')#" contains "Romania"

OR "#listgetAt('#index#',11, ',')#" contains "Russian Federation"

OR "#listgetAt('#index#',11, ',')#" contains "Rwanda"

OR "#listgetAt('#index#',11, ',')#" contains "San Marino"

OR "#listgetAt('#index#',11, ',')#" contains "Sao Tome & Principe"

OR "#listgetAt('#index#',11, ',')#" contains "Saudi Arabia"

OR "#listgetAt('#index#',11, ',')#" contains "Senegal"

OR "#listgetAt('#index#',11, ',')#" contains "Serbia & Montenegro"

OR "#listgetAt('#index#',11, ',')#" contains "Seychelles"

OR "#listgetAt('#index#',11, ',')#" contains "Sierra Leone"

OR "#listgetAt('#index#',11, ',')#" contains "Singapore"

OR "#listgetAt('#index#',11, ',')#" contains "Slovakia"

OR "#listgetAt('#index#',11, ',')#" contains "Slovenia"

OR "#listgetAt('#index#',11, ',')#" contains "Somalia"

OR "#listgetAt('#index#',11, ',')#" contains "South Africa"

OR "#listgetAt('#index#',11, ',')#" contains "South Georgia and the South Sandwich Islands"

OR "#listgetAt('#index#',11, ',')#" contains "Spain"

OR "#listgetAt('#index#',11, ',')#" contains "St. Helena"

OR "#listgetAt('#index#',11, ',')#" contains "Sudan"

OR "#listgetAt('#index#',11, ',')#" contains "Svalbard and Jan Mayen"

OR "#listgetAt('#index#',11, ',')#" contains "Swaziland"

OR "#listgetAt('#index#',11, ',')#" contains "Switzerland"

OR "#listgetAt('#index#',11, ',')#" contains "Syrian Arab Republic"

OR "#listgetAt('#index#',11, ',')#" contains "Taiwan"

OR "#listgetAt('#index#',11, ',')#" contains "Tajikistan"

OR "#listgetAt('#index#',11, ',')#" contains "Tanzania"

OR "#listgetAt('#index#',11, ',')#" contains "Togo"

OR "#listgetAt('#index#',11, ',')#" contains "Tunisia"

OR "#listgetAt('#index#',11, ',')#" contains "Turkey"

OR "#listgetAt('#index#',11, ',')#" contains "Turkmenistan"

OR "#listgetAt('#index#',11, ',')#" contains "Uganda"

OR "#listgetAt('#index#',11, ',')#" contains "Ukraine"

OR "#listgetAt('#index#',11, ',')#" contains "Uzbekistan"

OR "#listgetAt('#index#',11, ',')#" contains "Vanuatu"

OR "#listgetAt('#index#',11, ',')#" contains "Vatican City"

OR "#listgetAt('#index#',11, ',')#" contains "Wallis & Futana"

OR "#listgetAt('#index#',11, ',')#" contains "Western Samoa"

OR "#listgetAt('#index#',11, ',')#" contains "Yemen"

OR "#listgetAt('#index#',11, ',')#" contains "Zambia"

OR "#listgetAt('#index#',11, ',')#" contains "Zimbabwe"

>

INSERT INTO registrants2 (programname,program_id,firstname,lastname,company,address1,address2,city,state,zip,country,tel,email,opt_in_email_ind,opt_in_phone_ind,opt_in_mail_ind,privacy_set)

VALUES

('Inbound Web','3745',

<cfqueryParam value="#trim(listgetAt('#REReplace(index,"[""]"," ","ALL")#',3, ','))#" cfsqltype="cf_sql_longvarchar">,

<cfqueryParam value="#trim(listgetAt('#REReplace(index,"[""]"," ","ALL")#',4, ','))#" cfsqltype="cf_sql_longvarchar">,

<cfqueryParam value="#trim(listgetAt('#REReplace(index,"[""]"," ","ALL")#',5, ','))#" cfsqltype="cf_sql_longvarchar">,

'Russia',

<cfqueryParam value="#trim(listgetAt('#REReplace(index,"[""]"," ","ALL")#',6, ','))#" cfsqltype="cf_sql_longvarchar">,

</cfif>

<cfqueryParam value="#trim(listgetAt('#REReplace(index,"[""]"," ","ALL")#',7, ','))#" cfsqltype="cf_sql_longvarchar">,

<cfqueryParam value="#trim(listgetAt('#REReplace(index,"[""]"," ","ALL")#',8, ','))#" cfsqltype="cf_sql_longvarchar">,

<cfqueryParam value="#trim(listgetAt('#REReplace(index,"[""]"," ","ALL")#',9, ','))#" cfsqltype="cf_sql_longvarchar">,

<cfqueryParam value="#trim(listgetAt('#REReplace(index,"[""]"," ","ALL")#',10, ','))#" cfsqltype="cf_sql_longvarchar">,

<cfqueryParam value="#trim(listgetAt('#REReplace(index,"[""]"," ","ALL")#',11, ','))#" cfsqltype="cf_sql_longvarchar">,

<cfqueryParam value="#trim(listgetAt('#REReplace(index,"[""]"," ","ALL")#',12, ','))#" cfsqltype="cf_sql_longvarchar">,

<cfqueryParam value="#trim(listgetAt('#REReplace(index,"[""]"," ","ALL")#',14, ','))#" cfsqltype="cf_sql_longvarchar">,

'TRUE','TRUE','TRUE','OptIn'

' ',' ',' ','OptIn'

</cfif>

)

INSERT INTO registrants2 (programname,program_id,firstname,lastname,company,address1,address2,city,state,zip,country,tel,email,opt_out_email_ind,opt_out_phone_ind,opt_out_mail_ind,privacy_set)