Solved

split by comma separate columns

Posted on 2008-06-26
13
776 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Vb  script to restart the services 13 74
ASP Focus problem 3 53
ASP Button to clear text 4 44
Automate switch config backup 3 46
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

18 Experts available now in Live!

Get 1:1 Help Now