Skip to main content
T
trojnfn
Inspiring
June 14, 2006
Question

CFFILE Read

  • June 14, 2006
  • 6 replies
  • 1323 views
I am using cffile to read a text file and this is what I have so far and it works :

<cffile action="Read"
file="d:\webserve\esscm\datasource\source_code.txt"
variable="Message">
<cfoutput>#message#</cfoutput>

This just gives me one line per record but each record contains different fields and that is what I really need.

How do I break the variables up to be mulitples so that each one will be inserted into the proper column ? For example, col 1-5 is one column/field, col 6-20, is another column/field, etc. If I can break it up individually, then I can insert each into the table.
    This topic has been closed for replies.

    6 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    June 20, 2006
    !
    T
    trojnfnAuthor
    Inspiring
    June 19, 2006
    Thanks for your help BKBK, works like a charm.
    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    June 16, 2006
    <cfloop list="#message#" delimiters="#CHR(10)##chr(13)#" index="listItem">
    <!--- Loops through message(filecontent), line by line. ListItem represents the current line --->
    <!--- Your code to get the 8 fields then becomes #mid(listItem,1,3)#, etc. --->
    <!--- Insert the 8 fields from the current line(listItem) to the database --->
    </cfloop>


    T
    trojnfnAuthor
    Inspiring
    June 19, 2006
    Hello BKBK,

    I finally got it to work with your code. Here is my code below. The original text file contains about 12,000 records but I only want specific filtered ones. I tried to use where substring(source_code,1,1) ='1'; in the insert part, but it keeps coming back saying the semi colon is missing, and I have it. Any other suggestions on how to filter ?

    Thanks

    <cffile action="Read"
    file="d:\webserve\esscm\datasource\source_code.txt"
    variable="Message">

    <cfquery datasource="Transportation_Finance_db">
    Delete from tblsource_code
    </cfquery>

    <cfloop list="#message#" delimiters="#chr(10)##chr(13)#" index="listitem">

    <cfset source_code = "#mid(listItem,1,6)#">
    <cfset gla_account= "#mid(listItem,7,4)#">
    <cfset filler_1 = "#mid(listItem,11,40)#">
    <cfset source_code_descr = "#mid(listItem,51,21)#">
    <cfset filler_2 = "#mid(listItem,72,9)#">
    <cfquery name="qryInsert_Source_Code" datasource="Transportation_Finance_db">
    insert into tblSource_Code
    (source_code,gla_account,source_code_descr)
    values ('#source_code#','#gla_account#','#source_code_descr#')
    where substring(source_code, 1,1) = '1';
    </cfquery>
    </cfloop>
    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    June 19, 2006
    In this case, the insert-statement actually creates a new row in the table. The where-clause is therefore not applicable. You could use

    <cfif Left(source_code,1) IS '1'>
    <cfquery name="qryInsert_Source_Code" datasource="Transportation_Finance_db">
    insert into tblSource_Code
    (source_code,gla_account,source_code_descr)
    values ('#source_code#','#gla_account#','#source_code_descr#')
    </cfquery>
    </cfif>

    if the intention is to add new rows, or

    <cfquery name="qryUpdate_Source_Code" datasource="Transportation_Finance_db">
    UPDATE tblSource_Code
    SET source_code = '#source_code#', gla_account = '#gla_account#', source_code_descr = '#source_code_descr#'
    WHERE source_code LIKE '1%';
    </cfquery>

    if the intention is to update an existing row.






    S
    SafariTECH
    Inspiring
    June 14, 2006
    In the text file, is there 1 line per record?

    If yes, then use a loop over a list

    <cfloop list="#message#" delimiters="#CHR(10)#" index="i">
    #i#<BR>
    </cfloop>

    inside the loop you can manipulate each piece of the record as required.

    If the text file is not showing one record per line, then you will have to use a different approach.




    and no, a standard line return (from the OS) does not appear in a web page
    T
    trojnfnAuthor
    Inspiring
    June 16, 2006
    I still cannot get this to work. There is one line per record with multiple fields in each record (8 fields per line).

    I used the cfloop method and inside the loop, I used the #mid(message,1,3)#, etc., to get all 8 fields. I then need to insert each of the eight fields into an access table. Once that is done, I need to go back and read the second line/record, break it up, and then insert it into the table, go get the third record, etc.

    I think I am lost on the cfloop because what is happening is that the first record only is being read and outputed, I used cfoutput to display each field and they are all the same.

    How can I do this, read, then write, read again, then write again etc ?

    Thanks
    S
    SafariTECH
    Inspiring
    June 14, 2006
    If I understand your need, treat the text from the file as list using the line break as the delimiter.

    Depending on your OS, what program created the text file, and how the file was input, the Line Break could be:

    CHR(10) = linefeed
    CHR(13) = carriage return
    or
    CHR(13)CHR(10) - carriage return plus linefeed

    T
    trojnfnAuthor
    Inspiring
    June 14, 2006
    If the text file is 80 chars, then the 81st char/position would be the line feed ?

    How is that defined and how does CF display it as individual lines/records ?
    A
    Anonymous
    June 14, 2006
    if your records contain fixed-length fields, the code below should get you started.
    T
    trojnfnAuthor
    Inspiring
    June 14, 2006
    Thanks for the response.

    I did figure it out and tried this method and it does work.

    However, when I tried to display, it is only showing one line. I dont think that I can use a cfoutput query because there is none (normaily I would use that with a table). So how can I display each record, with a cfloop ? I need to use this method when I insert each record into the table.

    Thanks
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices