getlistat error

[Alan_Koenig_920]

my code:

<cffile action="read" file="http://website.com/test1.txt" variable="data"  attributes="readonly" >

<cfoutput >

#data#

</cfoutput>

<cfloop index="i" list="#data#" delimiters= "#chr(9)#" >

<cfquery datasource="X" username="Y" password="Z" timeout="90">

INSERT INTO property

   (

   PublicRemarks,

   MLSID)

VALUES

     (

    '#replace(listGetAt(i,1),'"','','all')#',

    '#replace(listGetAt(i,2),'"','','all')#'

    )

</cfquery>

</cfloop>

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 D:/home/manpcs.com/wwwroot/Untitled_cfm.cfm: line 19

17 :      ( 18 :     '#replace(listGetAt(i,1),'"','','all')#', 19 : '#replace(listGetAt(i,2),'"','','all')#' 20 : 21 :     )

My Question:

This is a tab delimited txt file.  I am attempting to break the data up so that I can store it in my database.   The data is being read as I can see from the output however I am unable to properly separate the data for my database.  How do I properly seperate the data so that it will go into my database?

Thank you in advance for any and all help.

[WolfShade]

Without being able to see the format of the contents of test.txt, it's difficult to speculate.

But the error you reference indicates that either A) there is no tab in the data (it _could_ be disguised as four or five spaces), or possibly B) there is a tab, but nothing after it.

Could you, please, post the data that resides in the variable #data#?  (If you need to redact certain bits, that's understood.)

Also, I do believe that the attribute "attributes" is not a valid attribute for the cffile tag if the action is "read".  Adobe ColdFusion 9 * cffile action = "read"

V/r,

^_^

[Alan_Koenig_920]

Text file has the following information.

AHRI	2806782
HLRE	201501158
VERN	201502452

There will eventually be more than 20 fields of information going into the database.

Once again.  Tank you for your help.

Alan

[BKBK · Accepted solution]

Use the delimiters line-feed/carriage-return for the loop. You are treating each loop index i as a sublist, so the listGetAt function should take the tab as delimiter.

<cffile action="read" file="http://website.com/test1.txt" variable="data">

<cfloop index="i" list="#data#" delimiters= "#chr(10)##chr(13)#" >

<cfquery datasource="X" username="Y" password="Z" timeout="90">

    INSERT INTO property

        (

        PublicRemarks,

        MLSID

        )

    VALUES

        (

        '#replace(listGetAt(i,1,chr(9)),'"','','all')#',

        '#replace(listGetAt(i,2,chr(9)),'"','','all')#'

        )

</cfquery>

</cfloop>

[Alan_Koenig_920]

Thank you.  This works and yet I am finding another problem. 

I have no control of how the information goes into the database.  I can only pull the information out of the database through retsconnector.  My problem is that some of the fields will be blank. 

MLS Number

Baths Full

Baths Half

Beds Total

Building Name

City

Land Tenure

List Price

Matrix Unique ID

MLS Area Major

Neighbourhood

Parking Total

Permit Address Internet YN

Photo Count

Property Type

Public Remarks

Status

Street Name

Street Number

Street Suffix

Maintenance Expense

Postal Code

Sqft Total

2806782

1

0

3

2275.00

1337510

METRO

KALIHI VALLEY

2

0

0

RNT

SERENE AND COOL KALIHI VALLEY.  LOCATED ON A PRIVATE ROAD.  DOWNSTAIR AVAILABLE 6/1/2008.  RENOVATED.  FURNISHED WITH REFRIGERATOR, MICROWAVE, WASHER & DRYER.  WATER, BASIC CABLE INCLUDED.  TENANT HAVE SEPARATE ELECTRIC METER.  START SHOWING BY 1ST WEEK OF MAY.  BY APPT ONLY.

A

1088

Above is a single row of what I am attempting to inport.  Some of the fields are blank and CF seems to only read the filled in spaces.   How do I include the blank spaces?

Thank you in advance.

[BKBK]

Please kindly mark your original problem as solved and then create a new thread for the new problem. You will then be helping to improve the forum as a source of information.

[Alan_Koenig_920]

Thank you.  I have marked as answered and also started a new string.  I am including a to the new string.

include blank space