Solved

split by comma separate columns

Posted on 2008-06-26
13
781 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 300 total points
ID: 21873678
fValues = fValues & CleanXML(Trim(XMLField(mx, "fv_"&f, False))) & Separator
0
 
LVL 15

Assisted Solution

by:sr75
sr75 earned 200 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

729 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