Professional Web Applications Themes

read text file - Coldfusion - Advanced Techniques

I am going to read the text file and insert these values into table. The code was working fine but if any missing data from the field then it grabs the value from the next column and put it into the column that has the missing data. Below is part of my text file. Suppose, If Lanier, Ronnie under Name is missing (empty) then it grabs MG under Code and place it under Name S Date Name Code E Type S Type E Date D Posted P By 1/3/2006 "Lanier, Ronnie" MG P R 1/3/2006 0:00 10/28/2005 tfurr 1/3/2006 "Knight, ...

Sponsored Links
  1. #1

    Default read text file

    I am going to read the text file and insert these values into table. The code
    was working fine but if any missing data from the field then it grabs the value
    from the next column and put it into the column that has the missing data.
    Below is part of my text file. Suppose, If Lanier, Ronnie under Name is
    missing (empty) then it grabs MG under Code and place it under Name

    S Date Name Code E Type S Type E Date D Posted P By
    1/3/2006 "Lanier, Ronnie" MG P R 1/3/2006 0:00 10/28/2005 tfurr
    1/3/2006 "Knight, Jack" DV111 P R 1/3/2006 0:00 10/20/2005 staylor
    11/30/2005 "Capps, Tommy" DX14 P R 11/30/2005 0:00 10/19/2005 staylor
    11/2/2005 "Amy, Grady" ER711 P R 11/2/2005 0:00 10/19/2005 staylor


    <cffile action="READ" file="#filedirectory#script.txt" variable="myField">
    <cfset myField = mid(myField, (findNoCase("Posted By", myField, 1) + 10),
    len(myField) ) >

    <cfset RecordList = MyField>

    <cfoutput>

    <cftransaction>
    <cfset DataArray = ArrayNew(1)>
    <cfset id=1>
    <cfquery name="EmptyTable" datasource="#db#">
    delete from losses_dts
    </cfquery>

    <cfloop list="#RecordList#" index="listItem" delimiters="#chr(10)#">
    <cfset DataArray = ListToArray(#ListItem#, "#Chr(9)#")>

    <cfquery name="insertlosses" datasource="#db#" >
    Insert into losses_dts
    (ID, Name, Separation_Type, Effective_Date)
    values
    (#id#,
    '#DataArray[2]#',
    '#DataArray[5]#',
    '#DataArray[6]#')
    </cfquery>

    <cfset id = id + 1>
    <cfset temp = ArrayClear(DataArray)>
    </cfloop>
    </cftransaction>
    </cfoutput>

    Sponsored Links
    kt03 Guest

  2. #2

    Default Re: read text file

    The problem is that for all List functions, CF ignores/skips/leaves a gap for
    Nulls. Per doentation:
    "ListLen Returns the number of elements in the list.
    Note ColdFusion ignores empty list elements; thus, a list that is defined as
    "a,b,c,,,d" is treated as a four element list not as a six element list. "

    It looks like you using the Tab character. Thus you'll need something like:
    <cfset ListItem=Replace(ListItem,"#chr(9)##chr(9)","#chr( 9) #chr(9)#","ALL")>
    just in from of your ListToArray function to first change the Nulls to blanks.
    Also be aware that the replace will only do pairs not
    triples, etc.. You may need to repeat it a maximum number of times depending
    on your data or create a "While delim pairs still present" loop. You may also
    have to cover first/last cell empty conditions.

    JMGibson3 Guest

  3. #3

    Default Re: read text file

    JMGibson3 ,

    It worked, thanks a lot
    kt03 Guest

Similar Threads

  1. read the text file
    By newcf in forum Coldfusion - Getting Started
    Replies: 13
    Last Post: September 2nd, 02:54 PM
  2. Read a text file
    By Mas in forum Macromedia Director Basics
    Replies: 3
    Last Post: September 13th, 08:09 AM
  3. HELP!! read text file in sql
    By Robert in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 12th, 11:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139