4 Replies Latest reply on Jan 12, 2016 2:10 PM by Alan Koenig_920

    include blank space

    Alan Koenig_920 Level 1

      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 NumberBaths FullBaths HalfBeds TotalBuilding NameCityLand TenureList PriceMatrix Unique IDMLS Area MajorNeighbourhoodParking TotalPermit Address Internet YNPhoto CountProperty TypePublic RemarksStatusStreet NameStreet NumberStreet SuffixMaintenance ExpensePostal CodeSqft Total
      28067821032275.001337510METROKALIHI VALLEY200RNTSERENE 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.A1088

      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.

        • 1. Re: include blank space
          Carl Von Stetten Adobe Community Professional & MVP

          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.

          • 2. Re: include blank space
            Alan Koenig_920 Level 1

            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.

            • 3. Re: include blank space
              BKBK Adobe Community Professional & MVP

              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>

              • 4. Re: include blank space
                Alan Koenig_920 Level 1

                Thank you.

                 

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