Skip to main content
Emily Diem
Emily Diem
Participating Frequently
July 4, 2023
Answered

cfloop comma delimited file list to sql table

  • July 4, 2023
  • 2 replies
  • 445 views

<CFFILE ACTION="READ" FILE="C:\inetpub\wwwroot\synonyms\Words.txt" VARIABLE="myList">


Each line of mylist contains Title with 5 related synonyms:
Abandon: Desert,Forsake,Leave,Quit,Abdicate
Abate: Diminish,Decrease,Reduce,Lessen,Subside
Abhor: Hate,Detest,Loathe,Despise,Execrate
Ability: Capacity,Skill,Talent,Expertise,Proficiency

 

Okay, here's my code that  does not work:

<cfloop list="#myList#" delimiters=":,">

<cfquery name="qinsert" datasource="LESSONS">
Insert Into English_Words
(Title, SYN_01,SYN_02,SYN_03,SYN_04,SYN_05)
values('#Title#', '#SYN_01#', '#SYN_02#', '#SYN_03#', '#SYN_04#','#SYN_05#')
</cfquery>
</cfloop>

The result should actually ook like the table screenshot above.

I manually added the screenshot columns in the table just to demonstrate what I am trying to do. The text file with the synonums will contain about 1000 TITLE entries, obviously too long to enter manually, and I will need to add more later.

But no matter what I try there's always an error.
I am obviously a novice at this. I am using this to teach language skills.
Please correct my code so the result will look like my photo. Please help, i really need this. 

This topic has been closed for replies.
Correct answer BKBK

@Emily Diem , you were almost there with your first attempt. You got an error because you omitted one of the required cfloop attributes, namely, index or item.

 

A correct version of the code is:

<cffile action="read" file="C:\inetpub\wwwroot\synonyms\Words.txt" variable="myList">
<cfloop list="#myList#" delimiters="#chr(10) & chr(13)#" item="line">
	<cfset Title=listGetAt(line,1,":,")>
	<cfset SYN_01=listGetAt(line,2,":,")>
	<cfset SYN_02=listGetAt(line,3,":,")>
	<cfset SYN_03=listGetAt(line,4,":,")>
	<cfset SYN_04=listGetAt(line,5,":,")>
	<cfset SYN_05=listGetAt(line,6,":,")>
	
	<cfquery name="qinsert" datasource="LESSONS">
		Insert Into English_Words
		(Title, SYN_01,SYN_02,SYN_03,SYN_04,SYN_05)
		values('#Title#', '#SYN_01#', '#SYN_02#', '#SYN_03#', '#SYN_04#','#SYN_05#')
	</cfquery>
</cfloop>

 

2 replies

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
July 5, 2023

@Emily Diem , you were almost there with your first attempt. You got an error because you omitted one of the required cfloop attributes, namely, index or item.

 

A correct version of the code is:

<cffile action="read" file="C:\inetpub\wwwroot\synonyms\Words.txt" variable="myList">
<cfloop list="#myList#" delimiters="#chr(10) & chr(13)#" item="line">
	<cfset Title=listGetAt(line,1,":,")>
	<cfset SYN_01=listGetAt(line,2,":,")>
	<cfset SYN_02=listGetAt(line,3,":,")>
	<cfset SYN_03=listGetAt(line,4,":,")>
	<cfset SYN_04=listGetAt(line,5,":,")>
	<cfset SYN_05=listGetAt(line,6,":,")>
	
	<cfquery name="qinsert" datasource="LESSONS">
		Insert Into English_Words
		(Title, SYN_01,SYN_02,SYN_03,SYN_04,SYN_05)
		values('#Title#', '#SYN_01#', '#SYN_02#', '#SYN_03#', '#SYN_04#','#SYN_05#')
	</cfquery>
</cfloop>

 

    Emily Diem
    Emily DiemAuthor
    Participating Frequently
    July 6, 2023

    I replied via email but don't see it here. Hope this will not be a duplicte reply:

    Thanks so much for your help. But maybe it's me in the testing but the code you gave me, at least on my system gave me this error:

     

    dgrafx from Experts Exchange gave me this code that works well and I share it now:

    I removed the colon from the text file and replaced with a comma.

    <cfquery name="qinsert" datasource="LESSONS">

    <cfloop list="#mylist#" index="ii" delimiters="#chr(10)#">


    INSERT INTO english_words
    (Title,SYN_01,SYN_02,SYN_03,SYN_04,SYN_05)
    VALUES
    (
    '<cftry>#listgetat(trim(ii),1)#<cfcatch></cfcatch></cftry>',
    '<cftry>#listgetat(trim(ii),2)#<cfcatch></cfcatch></cftry>',
    '<cftry>#listgetat(trim(ii),3)#<cfcatch></cfcatch></cftry>',
    '<cftry>#listgetat(trim(ii),4)#<cfcatch></cfcatch></cftry>',
    '<cftry>#listgetat(trim(ii),5)#<cfcatch></cfcatch></cftry>',
    '<cftry>#listgetat(trim(ii),6)#<cfcatch></cfcatch></cftry>'

    )
    </cfloop>


    </cfquery>

    With the cfcatch, if the total synonyms are less than 5 then the entry is blank and what works well for me.


    Thank you.

    Emily Diem
    Emily DiemAuthor
    Participating Frequently
    July 6, 2023

    Hmm. Guess one cannot edit a sent message:

    There is the error i got with the code you sent me:

    Attribute validation error for tag CFLOOP.
    It has an invalid attribute combination: delimiters,item,list. Possible combinations are:
    Required attributes: 'file,index'. Optional attributes: 'charset,from,to'.
    Required attributes: 'index,list'. Optional attributes: 'delimiters'.

    But I have code that works so I am not at all sure how to handle this question now.

     

    J
    Jim.F.
    Participating Frequently
    July 4, 2023

    You have a colon as your first separator.  Change that to a comma and your separators value in the loop to just a "," and it should work.

    Emily Diem
    Emily DiemAuthor
    Participating Frequently
    July 4, 2023

    Thank you but those words are difficult to understand when I use Google Translate. 
    This would work much better if you copy my cf code and make the modification you suggest.
    I speak Vietnamese and not much English. And page translation does not work well. But cf code will.

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