Board index » Visual Studio » Remove thousands seperator

Visual Studio244
I am trying to format data from a .csv file that has a comma as a

thoushands seperator. How do I remove the thousands seperator.



Sample Data

10/5/2003,Payment,$15.46,86891456

10/15/2003,Payment,"$1,253.32",86891457

9/2/2003,Payment,"$182,256.87",89861458

9/18/2003,Payment,$58.32,89861458



Amounts 1000.00 and larger have " as a text qualifier and all amounts

have a leading $.



I thought I posted this same question a couple of days ago but could

not find my posting.


-

Mike,



Do you want to import these into something. If so, you don't need to if the

application importing excepts numeric formatting and optional Field

Enclosures.



If not, read the line one by one looking for the "$ start, parse the field,

strip the commas and rewrite a new file.



Something Like:



iPos =instr(f, ",")

while iPos

f = left(f,iPos-1) & mid(f, iPos+1)

iPos =instr(f, ",")

wend



HTH,



Justin



"Michael Thompson" <michael.thompson@summitmg.com>wrote in message



-

Is there any constants that can be drawn from the data?..Like, the dollar

value will always be the last column in the comma-delimted data?...If you

cant derive some sort of logic that can be applied to the data, then it will

be near impossible to make assumptions as to the comma being a data

seperator or a thousands seperator...



Do you have control over the creation of the CSV file?..Can you format to

always use Quote to enclose data...



hmm..Looking at your data again. I guess you could create something that

checks for the presence of the first Quote, then assume the next comma is

associated with a dollar value. This too would be difficult...



--

Chris Hanscom

MVP (Visual Basic)

www.veign.com">www.veign.com

Application Design Section

www.veign.com/information/application/info_app.html">www.veign.com/information/application/info_app.html

------

"Michael Thompson" <michael.thompson@summitmg.com>wrote in message



-

Use LineInput to retrieve the line of data from the file, use replace to eliminate the quotes, use Split to parse the columns and

finally, use the Format$ function to eliminate the $ and thousands separator (Format$(vntSplit(2),"#0.00").



I hope this helps.

--

Al Reid



"It ain't what you don't know that gets you into trouble. It's what you know

for sure that just ain't so." --- Mark Twain



"Michael Thompson" <michael.thompson@summitmg.com>wrote in message news:2d83305f.0310270659.74464981@posting.google.com...



-

Try



format("$12,345.45","General Number")



Saga



"Michael Thompson" <michael.thompson@summitmg.com>wrote in message



-

Here is a response that I have posted before which contains a function that

allows a text string (a record perhaps) to be split correctly around

embedded delimiters... the function allows you to specify the delimiter, the

default is the comma. You will need to provide the optional 2nd argument,

specifying a blank space for it. Here is that previous post:



Rick - MVP



You should be able to use the following function (it returns a String array,

use it like you would Split) to handle this problem (note the comment in the

middle of the code). Notice, it leaves the comma (your delimiter) inside the

quote marks (whether removed or not) alone.



Function SplitAroundQuotes(TextToSplit As String, _

Optional Delimiter As String = ",") As String()

Dim QuoteDelimited() As String

Dim WorkingArray() As String

QuoteDelimited = Split(TextToSplit, """")

For x = 1 To UBound(QuoteDelimited) Step 2

QuoteDelimited(x) = Replace$(QuoteDelimited(x), _

Delimiter, Chr$(0))

Next

TextToSplit = Join(QuoteDelimited, """")

TextToSplit = Replace$(TextToSplit, Delimiter, Chr$(1))

TextToSplit = Replace$(TextToSplit, Chr$(0), Delimiter)

' Uncomment the following line if you

' want the quote marks to be removed

'TextToSplit = Replace$(TextToSplit, """", "")

WorkingArray = Split(TextToSplit, Chr$(1))

SplitAroundQuotes = WorkingArray

End Function







-

For a file like that, you can load the data with the Input# statement, for

example...



Input #1, DateIn, TypeIn, AmountIn, SomethingElse



Then you can get rid of the thousandths separator using the Format$

function, maybe something like...



AmountIn = Format$(AmountIn, "$#0.00")









"Michael Thompson" <michael.thompson@summitmg.com>wrote in message



-