get the numeric part of a string (1 Viewer)

[cpampas]

Hello,
I have splitted the following string by the comma character, so I have now an array containing 6 strings, and now I would like to pass the numeric value of each one to a variable.

},
"Time Series (Daily)": {
"2020-08-31": { 'stored in valor(0)
"1. open": "125.2500", 'stored in valor(1)
"2. high": "125.2500", 'stored in valor(2)
"3. low": "123.0300", 'stored in valor(3)
"4. close": "123.3100", 'stored in valor(4)
"5. volume": "4796902" 'stored in valor(5)
},

this is the data i want to get:

"2020-08-31"
"125.2500"
"125.2500"
"123.0300"
"4796902"

i ve been playing with the function instr and instrREV, but the problem is there are in each each string 4 characters chr(34), wich makes it hard to find the third chr(34) where the numeric value starts.
Any thoughts on this ?
Many thanks for reading my post.

 

[theDBguy]

Hi. Have you tried splitting it again on the colon )?

 

[Gasman]

I think you could split on the colon and then split again on the comma? However 5 is missing a comma before 'stored'?

 

[The_Doc_Man]

I don't know how much work you want to do, but look at this thread. There is some documentation about how to use it to parse characters in chunks.

Text Parsing Support as Class Module

I've been working on something I used a lot at work to help me more easily decipher complex text inputs. For the Navy, I used this as a part of a program to analyze IIS logs, decode complex names, and I even once used a variant of this under OpenVMS to build a command-line calculator for the...

www.access-programmers.co.uk

It would have the ability to extract the quoted strings as a unit that you could then manipulate. Parsing can be tedious, but this might reduce at least some of the tedium for you.

 

[CJ_London]

would be easier to see the actual string - I assume it is like this

"2020-08-31,1. open:125.2500, 2. high: 125.2500, 3. low: 123.0300, 4. close: 123.3100,5. volume: 4796902"

in which case you can split on the comma then split on the colon.


or are you saying it really is like this, double quotes and all?

},
"Time Series (Daily)": {
"2020-08-31": { 'stored in valor(0)
"1. open": "125.2500", 'stored in valor(1)
"2. high": "125.2500", 'stored in valor(2)
"3. low": "123.0300", 'stored in valor(3)
"4. close": "123.3100", 'stored in valor(4)
"5. volume": "4796902" 'stored in valor(5)
},

 

[theDBguy]

would be easier to see the actual string - I assume it is like this

"2020-08-31,1. open:125.2500, 2. high: 125.2500, 3. low: 123.0300, 4. close: 123.3100,5. volume: 4796902"

in which case you can split on the comma then split on the colon.


or are you saying it really is like this, double quotes and all?

},
"Time Series (Daily)": {
"2020-08-31": { 'stored in valor(0)
"1. open": "125.2500", 'stored in valor(1)
"2. high": "125.2500", 'stored in valor(2)
"3. low": "123.0300", 'stored in valor(3)
"4. close": "123.3100", 'stored in valor(4)
"5. volume": "4796902" 'stored in valor(5)
},

It looks to me like a JSON file, so a JSON parser might also be an option.

 

[GK in the UK]

This is where I would start (this isn't code but gives you an idea)

dim varStrArray as variant ' gets you a 1-dimensional array

varStrArray = split (string(1),":") ' string(1) = eg "1. open": "125.2500"

string part = varStrArray(0) ' eg "1. open"

number part = varStrArray(1) 'eg "125.2500"

number part = replace (number part, chr(34), "") ' strip out chr(34), now got 125.2500 as string

value = val(number part) ' now got 125.2500 as numeric

 

[cpampas]

Hello,
Thank you all for your comments, very helpfull
I ended up going with GK sugestion, of spliting with character ":" , and afterwards change the string into a number
It's working fine

 

[theDBguy]

Hello,
Thank you all for your comments, very helpfull
I ended up going with GK sugestion, of spliting with character ":" , and afterwards change the string into a number
It's working fine

Hi. Glad to hear you got it sorted out. I did try to mention about the colon earlier (post #2), but I guess I could have been more detailed. Good luck with your project.