I think that the problem lies in this code:
<cfset DataFile = Replace(DataFile, '#chr(9)##chr(9)#','#chr(9)#
#chr(9)#','all')>
<cfset DataFile = Replace(DataFile, '#chr(9)##chr(9)#','#chr(9)#
#chr(9)#','all')>
When I do the insert, I get this error:
ODBC Error Code = 00000 ()
Error while executing the query (non-fatal); ERROR: pg_atoi: error in " ":
can't parse " "
If I use this code it works:
<cfset DataFile = Replace(DataFile,
'#chr(9)##chr(9)#','#chr(9)#0#chr(9)#','all')>
<cfset DataFile = Replace(DataFile,
'#chr(9)##chr(9)#','#chr(9)#0#chr(9)#','all')>
But I don't want my fields to be populated with 0's, I would rather they
were blank. I assume the problem is that my database is trying to insert a
space into a field that isn't varchar or text... but I'm not sure of an
elegant solution.
*** *** ***
CODE BELOW:
*** ** ***
<cfscript>
function makeArray(dList,delim)
{
aRec = arrayNew(1);
temp = dList;
counter = 1;
idx = find(delim,temp,1);
while (len(temp))
{
aRec[counter] = spanExcluding(temp,delim);
if ((idx EQ len(temp)) OR (NOT idx))
{
temp = removeChars(temp,1,idx);
aRec[counter + 1] = mid(temp,1,len(temp));
temp = '';
}
else
{
temp = removeChars(temp,1,idx);
}
idx = find(delim,temp,1);
counter = counter + 1;
}
return aRec;
}
</cfscript>
<cfset new_line = chr(10) />
<cfset tab = chr(9) />
<cffile
action=""> file="/data/ftp/lungintr/pages/tools/data.txt"
variable="DataFile" />
<cfset DataFile = Replace(DataFile,
'#chr(9)##chr(9)#','#chr(9)#0#chr(9)#','all')>
<cfset DataFile = Replace(DataFile,
'#chr(9)##chr(9)#','#chr(9)#0#chr(9)#','all')>
<cfset aDataFile = listToArray(DataFile, new_line)>
<cfoutput>
<cftry>
<cfloop from="1" to="#arrayLen(aDataFile)#" index="Record">
<cfset aRecord = makeArray(aDataFile[record], tab)>
<cfquery name="insert" datasource="#DSN#">
INSERT INTO person_temp (
salutation_id,
first_name,
last_name,
title,
qualification,
organization_id,
department_id,
office_id,
status,
role_id,
org_position,
national_board,
userid,
password,
work_phone,
work_phone_ext,
fax_phone,
cell_phone,
pager_phone,
lung_family,
)
VALUES (
#aRecord[1]#,
'#aRecord[2]#',
'#aRecord[3]#',
'#aRecord[4]#',
'#aRecord[5]#',
#aRecord[6]#,
#aRecord[7]#,
#aRecord[8]#,
'#aRecord[9]#',
#aRecord[10]#,
'#aRecord[11]#',
'#aRecord[12]#',
'#aRecord[13]#',
'#aRecord[14]#',
'#aRecord[15]#',
'#aRecord[16]#',
'#aRecord[17]#',
'#aRecord[18]#',
'#aRecord[19]#',
'1',
'#aRecord[20]#'
)
</cfquery>
</cfloop>
Your records have been inserted.
<cfcatch type="all">
Your records were not inserted.
<br />Please check to ensure your data is properly delimited and does not
contain any empty records.
</cfcatch>
</cftry>
</cfoutput>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
