Solved

split by comma separate columns

Posted on 2008-06-26
13
775 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
  • 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now