Inserting List Values in Table

[DDhillon]

Hi There,

I have a list that looks similar to this:

"Ideas Afresh Inc
* WZ-100/4, Titarpur, Tagore Garden, New Delhi - 110 015, India
* +(91)-(11)-25464865/25195943/25161408/25915573/25448188 *
+(91)-(11)-25457924
* [url]http://www.ideasafresh.com/brass-handicrafts.html[/url]
%
Sankh Care Craft
* 207, Gagan Vihar, Vikas Marg, New Delhi - 110 051, India
* +(91)-(11)-22525019/22015137 * +(91)-(11)-22015137
* [url]http://www.sankhindia.com[/url]
%
Lovson Exports Ltd.
* 98, Bajaj Bhavan, Nariman Point, Mumbai - 400 021, India
* +(91)-(22)-22024071 * +(91)-(22)-22045488/56360830
* http://www.lovson.com/ethnicgifts/index.html"

I want to insert these values into a table with fields :
Company, Address, Phone, Fax, Website

The elements of each company is separated by * and the companies are separated
by %. Originally the list was like this, I put the delimeters * and % for
programming purposes:
Ideas Afresh Inc

Address: WZ-100/4, Titarpur, Tagore Garden, New Delhi - 110 015, India
Phone: +(91)-(11)-25464865/25195943/25161408/25915573/25448188 Fax:
+(91)-(11)-25457924
Website: [url]http://www.ideasafresh.com/brass-handicrafts.html[/url]



Sankh Care Craft
Address: 207, Gagan Vihar, Vikas Marg, New Delhi - 110 051, India
Phone: +(91)-(11)-22525019/22015137 Fax: +(91)-(11)-22015137
Website: [url]http://www.sankhindia.com[/url]



Lovson Exports Ltd.
Address: 98, Bajaj Bhavan, Nariman Point, Mumbai - 400 021, India
Phone: +(91)-(22)-22024071 Fax: +(91)-(22)-22045488/56360830
Website: [url]http://www.lovson.com/ethnicgifts/index.html[/url]

So you can see, every paragraphy represents one company with different fields.
I want to insert these companies and their fields into a table with the fields
mentioned above. Is it possible?

Thank you.

[mxstu]

You can use a CFLOOP with the "%" as a row delimiter and "*" as the field
delimiter.



<cfloop list="#yourList#" index="row" delimiters="%">
<cfset values = ListToArray(row, "*")>
<cfset companyName = Trim(values[1])>
<cfset address = Trim(values[2])>
<cfset phone = Trim(values[3])>
<cfset fax = Trim(values[4])>
<cfset website = Trim(values[5])>

<!--- replace this with query to insert values into database --->
<cfoutput>#companyName# , #address# , #phone# , #fax# ,
#website#</cfoutput><hr>
</cfloop>

[DDhillon]

Thank you mxstu. It worked !

[DDhillon]

hey mxstu,

It worked but I m still facing one problem. The way I showed you the secong
list

Ideas Afresh Inc

Address: WZ-100/4, Titarpur, Tagore Garden, New Delhi - 110 015, India
Phone: +(91)-(11)-25464865/25195943/25161408/25915573/25448188 Fax:
+(91)-(11)-25457924
Website: [url]http://www.ideasafresh.com/brass-handicrafts.html[/url]



Sankh Care Craft
Address: 207, Gagan Vihar, Vikas Marg, New Delhi - 110 051, India
Phone: +(91)-(11)-22525019/22015137 Fax: +(91)-(11)-22015137
Website: [url]http://www.sankhindia.com[/url]



Lovson Exports Ltd.
Address: 98, Bajaj Bhavan, Nariman Point, Mumbai - 400 021, India
Phone: +(91)-(22)-22024071 Fax: +(91)-(22)-22045488/56360830
Website: [url]http://www.lovson.com/ethnicgifts/index.html[/url]


Some companies dont have fax or website so it is giving error while compilng
the code. "The element at position 5 of dimension 1, of array variable
"VALUES," cannot be found. " Would it be possible to insert the values using
elements from the list where it says "address, phone, website" etc rather than
using * instead?

Thank you for your help

[DDhillon]

This is what I m trying to do now but it doesnt work either :

<cfif isdefined("values[3]")>
<cfset phone = Trim(values[3])>
<cfelse>
<cfset phone="">
</cfif>

<cfif isdefined("values[4]")>
<cfset fax = Trim(values[4])>
<cfelse>
<cfset fax="">
</cfif>

I m checking to see if the value exists. If it does then insert if it doesnt
then enter blank value.

[mxstu]

Which format are you using? The first format with the "%" and "*" delimiters?
Or the second format with the fields prefixes (Address:, Phone:, Fax:,
Website:)?

If all 5 fields will not always be populated, then the second format would
easier to parse because each field has a prefx (address:, phone:, etc) so you
could easily field the value for each field. You might be able parse the first
format, using REFind() to differentiate between phone/fax numbers and website
addresses. However, I do not know how you woluld tell the difference between a
phone number and a fax.

[MikerRoo]

The problem is that the list functions can't handle empty entries (a minor
annoyance in CF).
There are two basic ways to get around this:
1) Don't use the ListToArray() call but step through the string: character by
character. I do NOT recommend this.
2) Insert default values when the company string is being created or read from
the database.

So,
Please post the code that creates the master list that you are parsing and we
can help make sure that any blank entries are replaced by defaults.

-- MikeR

[DDhillon]

Hi mxstu,

I can use second format with address: phone: fax: and so on but my problem is
how will it know which part is going for phone, fax, website etc. If its
possible then its great! bcos I converted it to first format thinking that it
will help. If it works with the second format then I dont need to. I reall
appreciate your time.

Thank you

[mxstu]

Do you have control over how the list is generated? If so, then MikerRoo's
suggestion would make the parsing much easier, as you could control the list
format.

If not, you could identify the fields by the prefixes "Address, Phone, etc"
and extract the values. I could show you an example.

[DDhillon]

Hi Mike

I m using the list that looks like this

Krishna Profiles Private Limited
Address: Plot no.316 Digthan Road, Gram sejwaya, Pankhedi chauraha,
Ghatabillod Dist. Dhar, Indore - 452 001, India
Phone: +(91)-(731)-3104183/3211280 Fax: +(91)-(7292)-277323
Website: [url]http://www.indiamart.com/krishnaprofiles/[/url]



Ashwathi Enterprises
Address: 136, E Kamarajar Salai, Madurai - 625 009, India
Phone: +(95)-(452)-2311023/2310422 Fax: +(95)-(452)-2310533
Website: [url]http://www.indiamart.com/vscgroup[/url]



Hansraj Steels
Address: G.T. Road, Suranussi, Jalandhar - 144 027, India
Phone: +(91)-(181)-2670701/2671549/2671262/2671263/981406 Fax:
+(91)-(181)-2670702/2402507
Website: [url]http://www.indiamart.com/hansraj-steel[/url]



Virat Pressure Cookers
Address: C-365, DSIDC, Narela Industrial Area, New Delhi - 110 040, India
Phone: +(91)-(11)-30913260
Website: [url]http://www.indiamart.com/viratpressurecookers[/url]


You can see in the last record Fax: is missing. There are several records
throught the list where one or two fields are missing. Is there a way to get
around this problem?

Thank you
Davinder

[DDhillon]

I would appreciate the example mxstu

Thank you

[mxstu]

Do you have control over how the list is generated? And are the same prefixes always used?

[MikerRoo]

Actually I wasn't thinking and butted in.
MXSTU looks like he's well on the way to helping you.
Sorry for the distraction and good luck.

-- MikeR

[DDhillon]

No Mike, You are not distracing at all. Your opinion and solutions are very
valuable to me. I m sorry to make you feel like that.

This list is in Word format. It is not generated from anywhere. Yes, the same
prefixes are always used.

Thank you.

[MikerRoo]

Maybe i mis-phrased that.
I merely did not wish to be rude to mxstu.

It looks like the approach left is to (1) not replace the field names with
"*".
(2) Set default vars for all fields.
(3) Loop through the "%" list and use REReplace to extract the fields by name
-- replacing defaults AMAP.

I believe that mxstu is working on an example that does all that.

Good luck,
-- MikeR

[mxstu]

MikerRoo, Thank you for your considertation, but I didn't take offense at all.
The goal is to answer DDhillon's question, no matter who provides the answer
;-) I just got called away, so if you have time to post an example for
DDhillon, that would be very helpful. If not, I will post an example when I get
back. - Cheers

[MikerRoo]

I got called away too. My team was down 11 to 10 in the ninth...

Anyway, first change your parsing so that this:

Hansraj Steels
Address: G.T. Road, Suranussi, Jalandhar - 144 027, India
Phone: +(91)-(181)-2670701/2671549/2671262/2671263/981406 Fax:
+(91)-(181)-2670702/2402507
Website: [url]http://www.indiamart.com/hansraj-steel[/url]



Virat Pressure Cookers
Address: C-365, DSIDC, Narela Industrial Area, New Delhi - 110 040, India
Phone: +(91)-(11)-30913260
Website: [url]http://www.indiamart.com/viratpressurecookers[/url]


Becomes this:

Hansraj Steels
*Address: G.T. Road, Suranussi, Jalandhar - 144 027, India
*Phone: +(91)-(181)-2670701/2671549/2671262/2671263/981406 *Fax:
+(91)-(181)-2670702/2402507
*Website: [url]http://www.indiamart.com/hansraj-steel[/url]
%
Virat Pressure Cookers
*Address: C-365, DSIDC, Narela Industrial Area, New Delhi - 110 040, India
*Phone: +(91)-(11)-30913260
*Website: [url]http://www.indiamart.com/viratpressurecookers[/url]


This should be the same as what you were doing except that the * doesn't
replace the key fields.

Then the attached code, should work:

Regards,
-- MikeR



<CFSET sReplacedWordText =
"Hansraj Steels
*Address: G.T. Road, Suranussi, Jalandhar - 144 027, India
*Phone: +(91)-(181)-2670701/2671549/2671262/2671263/981406 *Fax:
+(91)-(181)-2670702/2402507
*Website: [url]http://www.indiamart.com/hansraj-steel[/url]
%
Virat Pressure Cookers
*Address: C-365, DSIDC, Narela Industrial Area, New Delhi - 110 040, India
*Phone: +(91)-(11)-30913260
*Website: [url]http://www.indiamart.com/viratpressurecookers[/url]
"

>

<CFOUTPUT>
<table align="left">
<tr align="left">
<th>Comp Name</th>
<th>Comp Address</th>
<th>Comp Phone</th>
<th>Comp Fax</th>
<th>Comp Website</th>
</tr>
</CFOUTPUT>

<CFLOOP list="#sReplacedWordText#" index="sCompanyData" delimiters="%">
<CFSCRIPT>
sRawFields = ListToArray (sCompanyData, "*");
iNumFields = arrayLen (sRawFields);

/*--- Set default values for all fields. ---
** Note that the first field MUST be company name and it CANNOT be
blank.
*/
sCompanyName = Trim (sRawFields [1]);
sCompAddress = "-- not provided --";
sCompPhone = "-- not provided --";
sCompFax = "-- not provided --";
sCompWebsite = "-- not provided --";

/*--- Loop through the rest of the array stuffing any fields that
exist. ---
** Be aware that some fields may not exist.
*/
for (iField=2; iField LTE iNumFields; iField=iField+1)
{
sFieldName = REReplaceNoCase (sRawFields [iField], "([a-z]+):.*",
"\1" );
switch (sFieldName)
{
case "Address":
sCompAddress = Trim (sRawFields [iField]);
break;
case "Phone":
sCompPhone = Trim (sRawFields [iField]);
break;
case "Fax":
sCompFax = Trim (sRawFields [iField]);
break;
case "Website":
sCompWebsite = Trim (sRawFields [iField]);
break;
default:
WriteOutput ('<h1 style="color: red;">Unknown field name,
"#sFieldName#"!</h1>');
}
}
</CFSCRIPT>

<!--- replace this with query to insert values into database --->
<CFOUTPUT>
<tr align="left">
<td>#sCompanyName#</td>
<td>#sCompAddress#</td>
<td>#sCompPhone#</td>
<td>#sCompFax#</td>
<td>#sCompWebsite#</td>
</tr>
</CFOUTPUT>
</CFLOOP>

<CFOUTPUT>
</table>
</CFOUTPUT>

[The ScareCrow]

I've come into this late and have not read all the posts closely, but it
appears there is a lot of needless proccessing here.

If you have the lists as defined, just use the chr(10) to find each element of
the list.
Thus
element 1 would be the name
element 2 would be the address
element 3 would be the phone numbers
element 4 would be the web address

You then need to "split" element 3 into phone fax
So do a find for "Fax:"
If you find it then the left part is the phone the right is the fax, if it's
not found then it's just the phone.

This would then give you up to 5 elements
You could then use the find again to see if the element contains one of
"Address:", "Phone:", "Fax:" or "Website:"
This will then indicate which element you have, it none of these are found
then you have the name.

Ken