Issue importing pipe delimited file. 2

[redoakhg]

Hello,

This worked quite well for me until this morning when I added 2 additional fields to the database to separate first, last and mi.

Here's a sample of the text file:
JOHN|G|SMITH|JOHNGSMITH@ATT.NET|MARCOS GONZALEZ|NEW ACCOUNT

Here's my code:

<cffile action="read" file="C:\Inetpub\[URL unfurl="true"]wwwroot\hg\clup\files\#dirlist.name#"[/URL] variable="txtfile">

<!--- loop through and insert into database --->

<cfloop index="index" list="#txtfile#" delimiters="#chr(10)##chr(13)#">
    <cfquery name="importtxt" datasource="#korn#">
         INSERT INTO com_custs (gencustfname,gencustmi,gencustlname,gencusemail,genagent)
         VALUES
                  ('#listgetAt('#index#',1, '|')#',
                   '#listgetAt('#index#',2, '|')#',
                   '#listgetAt('#index#',3, '|')#',
		   '#listgetAt('#index#',4, '|')#',
				   '#listgetAt('#index#',5, '|')#'
                  )

   </cfquery>

And finally my error:


Invalid list index 2.
In function ListGetAt(list, index [, delimiters]), the value of index, 2, is not a valid as the first argument (this list has 1 elements). Valid indexes are in the range 1 through the number of elements in the list.

The error occurred in C:\Inetpub\

http://wwwroot\hg\clup\files\filetransfer2.cfm:

line 75

73 : VALUES
74 : ('#listgetAt('#index#',1, '|')#',
75 : '#listgetAt('#index#',2, '|')#',
76 : '#listgetAt('#index#',3, '|')#',
77 : '#listgetAt('#index#',4, '|')#',

 

[Bammy217]

Try the following:

<cfloop index="index" list="#txtfile#" delimiters="#chr(10)##chr(13)#">
	<cfset index = ReplaceNoCase(index,"||","| |","ALL")>
	<cfif Right(index,1) EQ "|">
		<cfset index = index & " " />
	</cfif>
	<cfif Left(index,1) EQ "|">
		<cfset index = " " & index />
	</cfif>
    <cfquery name="importtxt" datasource="#korn#">
         INSERT INTO com_custs (gencustfname,gencustmi,gencustlname,gencusemail,genagent)
         VALUES
                  ('#Replace(listgetAt('#index#',1, '|')," ","")#',
                   '#Replace(listgetAt('#index#',2, '|')," ","")#',
                   '#Replace(listgetAt('#index#',3, '|')," ","")#',
           	   '#Replace(listgetAt('#index#',4, '|')," ","")#',
                   '#Replace(listgetAt('#index#',5, '|')," ","")#'
                  )

   </cfquery>

When ever I've had this issue when looping over pre-delimited files it tends to be empty fields

An alternative is of course to just check if it's valid... e.g.:

<cfloop index="index" list="#txtfile#" delimiters="#chr(10)##chr(13)#">
    <cfif ListLen(index,"|") EQ 5>
    <cfquery name="importtxt" datasource="#korn#">
         INSERT INTO com_custs (gencustfname,gencustmi,gencustlname,gencusemail,genagent)
         VALUES
                  ('#listgetAt('#index#',1, '|')#',
                   '#listgetAt('#index#',2, '|')#',
                   '#listgetAt('#index#',3, '|')#',
           '#listgetAt('#index#',4, '|')#',
                   '#listgetAt('#index#',5, '|')#'
                  )

   </cfquery>
   <cfelse>
   	<cfmail to="myself@mydomain.com" from="my@myself.com" subject="uh oh...file import error" type="html">
   		The import has a glitch, the line : "#index#" is not a valid Pipe-delimited list of 5!!
   	</cfmail>
   </cfif>

let me know if it helps

We never fail, we just find that the path to succes is never quite what we thought...

 

[redoakhg]

Thanks! Meetings this morning, will try as soon as I break free and will let you know.

 

[cfSearching]

When ever I've had this issue when looping over pre-delimited files it tends to be empty fields

Yes, most CF functions ignores empty elements. (Not always the behavior you want..) But if you are using CF8+, you can also use an array instead. The third parameter allows you to control whether empty elements are ignored or preserved.

ListToArray(list [, delimiters, includeEmptyFields])

http://livedocs.adobe.com/coldfusion/8/htmldocs/functions_l_21.html

Also, most database also have tools for importing text files, which are generally faster than looping. At least for moderate to large text files.


----------------------------------

http://cfsearching.blogspot.com/

 

[redoakhg]

Thanks guys, it's working great with one exception. The name MARCOS GONZALEZ in the file becomes MARCOSGONZALEZ in the import. How can this be avoided.

Thanks again for your help.

 

[cfSearching]

I cannot think of any CF-specific reason it should. Are you doing some sort of replace elsewhere in your code?

'#listgetAt('#index#',1, '|')#'

BTW: You do not need the extra single quotes or # signs there. It should just be

'#listgetAt( index, 1, '|')#'

----------------------------------

http://cfsearching.blogspot.com/

 

[redoakhg]

I used Bammy's code:

<cfloop index="index" list="#txtfile#" delimiters="#chr(10)##chr(13)#">
    <cfset index = ReplaceNoCase(index,"||","| |","ALL")>
    <cfif Right(index,1) EQ "|">
        <cfset index = index & " " />
    </cfif>
    <cfif Left(index,1) EQ "|">
        <cfset index = " " & index />
    </cfif>
    <cfquery name="importtxt" datasource="#korn#">
         INSERT INTO com_custs (gencustfname,gencustmi,gencustlname,gencusemail,genagent)
         VALUES
                  ('#Replace(listgetAt('#index#',1, '|')," ","")#',
                   '#Replace(listgetAt('#index#',2, '|')," ","")#',
                   '#Replace(listgetAt('#index#',3, '|')," ","")#',
                  '#Replace(listgetAt('#index#',4, '|')," ","")#',
                   '#Replace(listgetAt('#index#',5, '|')," ","")#'
                  )

   </cfquery>

It imports fine with that one exception. No, this is the only replace. I will keep tweaking with it.

 

[cfSearching]

Looks like their sample is doing replace of all spaces with an empty string ;-) It is probably working code from one of their own applications. Since you say you do not need the replace, just remove it

ie Change this

#Replace(listgetAt('#index#',1, '|')," ","")#',
...etcetera...

... to

#listgetAt( index, 1, '|'),
...etcetera...


----------------------------------

http://cfsearching.blogspot.com/

 

[redoakhg]

Fantastic. Thank you very much.

 

[Bammy217]

Hi folks,

Sorry it was an & there but it got HTML rendered

b.

We never fail, we just find that the path to succes is never quite what we thought...