• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 785
  • Last Modified:

split by comma separate columns

Hello Experts,
I am using the attached code snippet to retrieve data for the ids provided, and save them in the csv file.
One of the field is used for the Title which holds the value as "mark, henson" is there any way I can skip this comma in the script? As the name is been added in 2 different columns in the file.
Can someone please advice
thanks
s
0
newbie27
Asked:
newbie27
  • 6
  • 6
2 Solutions
 
newbie27Author Commented:

parts = split(ISBNs, ",")
			AddCount = 0
			For each p in parts
			 	AddCount = AddCount + 1	
				If AddCount mod ReportStep = 0 Then
					msg("> records processed: [" & Padz(AddCount, 8) & "]")
				End If
				mx = DataHash(p)
				 
				Fields = Split(FieldList, ",")
				 
				For each f in Fields
					fValues = fValues & CleanXML(Trim(XMLField(mx, "fv_"&f, False))) & Separator
				Next
				
				fValues = fValues & VbCrLf  
				
		         
			Next
		     AppendtoFile OutFile, fValues   

Open in new window

0
 
dosthCommented:
Fields = Split(FieldList, ",")

from where you get this FieldList, if your database values going to have comma, then you need to go for TABS seperated or some special charecter seperation when you get the field list

if you still not able to do that

you can add a if else statement for that filed name that comes with coma or something

0
 
newbie27Author Commented:
hello dosth,
thanks for looking this out for me, FieldList is ok but the data is not.

FieldList = "id, title,contents,notes,price"
id = 1
title =mike,henson
contents = hello, this is a test
notes = simple ntoes
price = 23.00
FieldValues="1,mike,henson,hello, this is a test, simple notes, 23.00"

because i am using comma as a separator it is splitting it on every comma if is finding in the fieldValues

i have to use comma as a delimiter

can you please let me know how to handle this ?

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dosthCommented:
How you get the values to this field
FieldValues= ?

0
 
newbie27Author Commented:


mx = DataHash(p)

mx = "<fv_id>1</fv_id><fv_title>mike,henson</fv_title><fv_contents>hello, this is a test</fv_contents><fv_notes>simple ntoes</fv_notes><fv_price>23.00</fv_price>"

I have a XMLField function which retrieves values from the mx hash.

FieldValues here is fValues
mx = DataHash(p)
                                 
                                Fields = Split(FieldList, ",")
                                 
                                For each f in Fields
                                        fValues = fValues & CleanXML(Trim(XMLField(mx, "fv_"&f, False))) & Separator
                                Next

Open in new window

0
 
dosthCommented:
its bit hard to do with comma then

either when you pull the data from this xml you need to change the data to be come like this if possible

FieldValues="1,mike,henson,hello, this is a test, simple notes, 23.00"

to

FieldValues="1|mike,henson|hello, this is a test| simple notes| 23.00"

you see i seperated with | symbol, if possible duplicate the xml function and change that so other place you wont have problems
0
 
newbie27Author Commented:
hello again,
i have used something like this
fValues = fValues & CleanXML(Trim(Replace(XMLField(mx, "fv_"&f, False),",",""))) & Separator

this replace every comma from the fields which ideally is not the solution

I cannot change the comma separator as I am asking users to whether download into comma delimited csv or dat

please can you advice or would you think it is not possible?

thanks


0
 
dosthCommented:
& Separator, what it is doing?

what i said is, if your values going to have comma, just change the seperator to some other like TABS or | Symbol
0
 
newbie27Author Commented:
     
' How is the File to be formatted?
      if Ucase(LoadFormat) = "TAB" Then
            Separator = VBTAB
      ElseIf LoadFormat = "" Then
            Separator = ","
      Else
            Separator = LoadFormat
      End if    


fValues = fValues & CleanXML(Trim(Replace(XMLField(mx, "fv_"&f, False),",",""))) & Separator

dosth,
i have got what you have said earlier but if the chose to save the file in comma delimited file then I have to set this separator as comma

the above is writing the data correctly in the csv columns but without any commas

1   mike henson   hello this is a test    simple notes  23.00


0
 
dosthCommented:
fValues = fValues & CleanXML(Trim(XMLField(mx, "fv_"&f, False))) & Separator
0
 
sr75Commented:
Using this, it generates a tab seperated document.  

' How is the File to be formatted?
      if Ucase(LoadFormat) = "TAB" Then
            Separator = VBTAB
      ElseIf LoadFormat = "" Then
            Separator = ","
      Else
            Separator = LoadFormat
      End if    


fValues = fValues & CleanXML(Trim(Replace(XMLField(mx, "fv_"&f, False),",",""))) & Separator


That can be also be split with what dotsh placed. Only replace the "," with vbtab like so:

Fields = Split(FieldList, vbtab)

0
 
newbie27Author Commented:
thanks
0
 
dosthCommented:
thanks for the points
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now