Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

split by comma separate columns

Posted on 2008-06-26
13
Medium Priority
?
783 Views
Last Modified: 2010-08-05
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
Comment
Question by:newbie27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 8

Author Comment

by:newbie27
ID: 21873231

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
 
LVL 15

Expert Comment

by:dosth
ID: 21873255
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
 
LVL 8

Author Comment

by:newbie27
ID: 21873320
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 15

Expert Comment

by:dosth
ID: 21873344
How you get the values to this field
FieldValues= ?

0
 
LVL 8

Author Comment

by:newbie27
ID: 21873410


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
 
LVL 15

Expert Comment

by:dosth
ID: 21873437
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
 
LVL 8

Author Comment

by:newbie27
ID: 21873474
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
 
LVL 15

Expert Comment

by:dosth
ID: 21873520
& 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
 
LVL 8

Author Comment

by:newbie27
ID: 21873565
     
' 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
 
LVL 15

Accepted Solution

by:
dosth earned 1200 total points
ID: 21873678
fValues = fValues & CleanXML(Trim(XMLField(mx, "fv_"&f, False))) & Separator
0
 
LVL 15

Assisted Solution

by:sr75
sr75 earned 800 total points
ID: 21874707
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
 
LVL 8

Author Closing Comment

by:newbie27
ID: 31470910
thanks
0
 
LVL 15

Expert Comment

by:dosth
ID: 21875897
thanks for the points
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question