include blank space

[Alan_Koenig_920]

The error:

Error Occurred While Processing Request

Invalid list index 16. In function ListGetAt(list, index [, delimiters]), the value of index, 16, is not a valid as the first argument (this list has 15 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 52 50 :           '#replace(listGetAt(i,14,chr(9)),'"','','all')#', 51 :           '#replace(listGetAt(i,15,chr(9)),'"','','all')#', 52 : '#replace(listGetAt(i,16,chr(9)),'"','','all')#', 53 :           '#replace(listGetAt(i,17,chr(9)),'"','','all')#', 54 :           '#replace(listGetAt(i,18,chr(9)),'"','','all')#',

The code:

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

<cfoutput >

#data#<br>

</cfoutput>

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

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

INSERT INTO P2

   ( MLSID,

    BathFull,

    BathHalf,

    Beds,

    BuildName,

    City,

    LandTenure,

    ListPrice,

    MatrixUID, MLSArea,

    Neighbourhood,

    Parking,

    AddressOK,

    PhotoCount,

    PropertyType,

    Public,

    Status,

    StreetName,

    StreetNumber,

    StreetSuf,

    Maint,

    Postal,

    SQFT)

VALUES

     (

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    )

</cfquery>

</cfloop>

This is a single row example of the information importing.  I included headers to show the spacing.

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

My Question:

I have no control over the data entry.  Anyone of these can be blank.  I need to enter the information and preserve the integrity of the information.  How do I include the empty spaces, not knowing which will be blank, and have the information insert corrwectly into my database?

Thank you in advance.

[Carl Von Stetten]

The text file is space-delimited? Any way you can get it comma-delimited or delimited by some other character?  If not, I'm not sure what you can do to deal with "blank" values.

[Alan_Koenig_920]

The file is tab delimited.  some of the fields might have more that one element in it and they would be separated by commas.  An example could be "ASAP,DELTA,SEP".   I can do a CSV file however the example just shown concerns me.

[BKBK · Accepted solution]

You could give this a try:

<cfset dataModified = "">

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

    <!--- Take each row in turn and convert it into a comma-delimited list. --->

    <cfset line=replaceNocase(line,chr(9),",","all")>

    <!---Replace with 'NULL' each blank item in the list--->

    <cfset rowWithNulls = "">   

    <cfloop index="idx" from="1" to="23">

        <cfset rowItem = listgetat(line,idx,',','yes')>

        <cfif trim(rowItem) is "">   

             <cfset rowWithNulls = listAppend(rowWithNulls,'NULL')>

         <cfelse>

             <cfset rowWithNulls = listAppend(rowWithNulls,rowItem)>

         </cfif>

    </cfloop>

    <!---Build up a new version of the original data, with comma replacing chr(9) and NULL replacing blank entries--->

    <cfset dataModified = listAppend(dataModified,rowWithNulls,"#chr(10)##chr(13)#")>

</cfloop>

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

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

INSERT INTO P2

   ( MLSID,

    BathFull,

    BathHalf,

    Beds,

    BuildName,

    City,

    LandTenure,

    ListPrice,

    MatrixUID, MLSArea,

    Neighbourhood,

    Parking,

    AddressOK,

    PhotoCount,

    PropertyType,

    Public,

    Status,

    StreetName,

    StreetNumber,

    StreetSuf,

    Maint,

    Postal,

    SQFT)

VALUES

     (

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

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

...

...

...

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

    )

</cfquery>

</cfloop>

[Alan_Koenig_920]

Thank you.

I hope your answers help other people as much as they helped me.