-
1. Re: Importing a text file
ilssac Jan 13, 2010 3:05 PM (in response to bjoz)bjoz wrote:
It looks like what is happening is when my process hits the first line in the txt file where price(column5) has a null value, it's shifting the string discount(column6) value into column 5.
That is the documented and expected, if mightily frustrating for many developers, behavior for empty list values in ColdFusion.
The usual work around is some pre-processing using string functions of your choice to place some type of 'null' character or string into those places. There are at least a few functions already built to do this for you on the http://www.cflib.org site.
If you have access to ColdFusion 9, I understand they have finaly heard our pleas, and provided a paramter to tell the list functions to NOT ignore empty list elements. But if you don't have access to 9, that is probably not much help. :-)
-
2. Re: Importing a text file
-==cfSearching==- Jan 13, 2010 3:21 PM (in response to ilssac)In addition, since you are using MS SQL you might want to try using a simple BULK INSERT instead of looping.
...I understand they have
finaly heard our pleas, and provided a paramters to tell the
list functions to NOT ignore empty list elements.
Wohoo! I did not know that. 'Bout darned time too.
-
3. Re: Importing a text file
Dan Bracuk Jan 13, 2010 4:29 PM (in response to bjoz)Here are some snippets where we process a pipe delimited file.
//make sure every list element has a value
thisRow=REreplace(thisRow, '\r\n', ' ','ALL');//remove crlfs
thisRow = Trim(REReplace(ThisRow, "\|(?=\|)","|null","all")); // this line replaces all || s with |null|
if (right(ThisRow, 1) is "|")
ThisRow = ThisRow & "null";dot dot dot
<!--- possible nulls --->
<cfloop list="2,3,4,5,6,11,12,13,14,18" index="element" delimiters=",">
<cfscript>
if (ListGetAt(ThisRow, element, "|") is "null") {
variables[RTrim(ListGetAt(ThisColumnList, element, ",")) & "_null"] = true;
}
else {
variables[Rtrim(ListGetAt(ThisColumnList, element, ",")) & "_null"] = false;
}dot dot dot
insert into micro_temp
(#ThisColumnList#)
values
(<cfqueryparam cfsqltype="cf_sql_varchar" value="#ptnumber#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#admitdate#" null="#admitdate_null#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#regno#" null="#regno_null#">
, <cfqueryparam cfsqltype="cf_sql_char" value="#admitphys1#" null="#admitphys1_null#">
, <cfqueryparam cfsqltype="cf_sql_char" value="#orderphys#" null="#orderphys_null#"> -
4. Re: Importing a text file
BKBK Jan 15, 2010 10:33 AM (in response to Dan Bracuk)Could it be simpler to follow Ian's tip and replace the empty string with NULL, like this
<cfset token = arrayNew(1)>
<cfloop index="index" list="#txtfile#" delimiters="#chr(10)##chr(13)#">
<cfquery name="importtxt" datasource="#client.DSN#" username="#client.UserName#" passWord="#client.PassWord#">
INSERT INTO products (product, description, unitofmeas, SOH, Price, discount)
VALUES
(
<cfloop index="i" from="1" to="6">
<cfset token[i] = getToken(index,i, '|')>
<cfif trim(token[i]) is "">NULL<cfelse>'#token[i]#'</cfif>
<cfif i LT 6>,</cfif>
</cfloop>
)
</cfquery>
</cfloop>
-
5. Re: Importing a text file
Dan Bracuk Jan 15, 2010 1:33 PM (in response to BKBK)Try that with date and integer fields and get back to us on how simple it is.
-
6. Re: Importing a text file
BKBK Jan 15, 2010 1:50 PM (in response to Dan Bracuk)Try that with date and integer fields and get back to us on how simple it is.
I think there is no need to try that, as Bjoz has apparently settled for text fields.
-
7. Re: Importing a text file
Dan Bracuk Jan 15, 2010 4:29 PM (in response to BKBK)Regarding,
I think there is no need to try that, as Bjoz has apparently settled for text fields.
Fair enough. Now try it where the last field in any line of text does not have a value. See if your array has the correct number of elements.
-
8. Re: Importing a text file
bjoz Jan 15, 2010 5:25 PM (in response to Dan Bracuk)Thanks for all the help guys. Still working through it but your posts have helped