text to column

[bobo]

Hi, I have table wich looks like that

id detail
1 value1=15,value 2=345,value3=2
2 value1=1523,val ue2=32,value3=2 322
3 value1=2,value2 =45,value3=34

How can I change it to this:

id value1 value2 value3
1 15 345 2
2 1523 32 2322
3 2 45 34

Thank you in advance

[Peter Russell]

Re: text to column

bobo previously wrote:
[color=blue]
> Hi, I have table wich looks like that
>
> id detail
> 1 value1=15,value 2=345,value3=2
> 2 value1=1523,val ue2=32,value3=2 322
> 3 value1=2,value2 =45,value3=34
>
> How can I change it to this:
>
> id value1 value2 value3
> 1 15 345 2
> 2 1523 32 2322
> 3 2 45 34
>
> Thank you in advance
>[/color]

What's the difference? They look the same to me.

[Fletcher Arnold]

Re: text to column

"bobo" <bobo@vip.bg> wrote in message
news:c11f5d5c.0 402170156.4ecaf 35e@posting.goo gle.com...[color=blue]
> Hi, I have table wich looks like that
>
> id detail
> 1 value1=15,value 2=345,value3=2
> 2 value1=1523,val ue2=32,value3=2 322
> 3 value1=2,value2 =45,value3=34
>
> How can I change it to this:
>
> id value1 value2 value3
> 1 15 345 2
> 2 1523 32 2322
> 3 2 45 34
>
> Thank you in advance[/color]



The file actually contains the text "value1=", etc?

If this is a quick conversion, paste it into Excel and use Data>Text To
Columns and put the "=" in the "other" box and also select space. Choose to
treat consecutive delimiters as one.

If you are looking for something more automated, then let us know if you can
do anything with VBA.


Fletcher

[borislav simeonov]

Re: text to column



Hi again.
No this is not a quick conversion.
I'm familiar with VB if you give me an idea I think will help...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[borislav simeonov]

Re: text to column



anonymous@devde x.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Peter Russell]

Re: text to column

Fletcher Arnold previously wrote:
[color=blue]
> The file actually contains the text "value1=", etc?[/color]


This function will enable you to pull out any value from the example you
give.


Public Function SplitUp(iText, pos)

Dim x() As String
x = Split(iText, ",")

SplitUp = Val(Mid(Trim(x( pos - 1)), 8, 10))

End Function


Usage:
in a query: update to :
splitup([Details],1) will give the first value
splitup([Details],2) will give the second value etc.

It will work up to value9, provided values are separated with a comma.

Peter Russell

[Fletcher Arnold]

Re: text to column


"borislav simeonov" <bobo@vip.bg> wrote in message
news:403229f8$0 $194$75868355@n ews.frii.net...[color=blue]
>
>
> Hi again.
> No this is not a quick conversion.
> I'm familiar with VB if you give me an idea I think will help...[/color]


Did you get Peter Russell's idea to work?

Fletcher

[Steve Cummings]

Re: text to column

rusty@127.0.0.1 (Peter Russell) wrote in message news:<memo.2004 0217105817.1368 K@russellscott. btinternet.com> ...[color=blue]
> bobo previously wrote:
>[color=green]
> > Hi, I have table wich looks like that
> >
> > id detail
> > 1 value1=15,value 2=345,value3=2
> > 2 value1=1523,val ue2=32,value3=2 322
> > 3 value1=2,value2 =45,value3=34
> >
> > How can I change it to this:
> >
> > id value1 value2 value3
> > 1 15 345 2
> > 2 1523 32 2322
> > 3 2 45 34
> >
> > Thank you in advance
> >[/color]
>
> What's the difference? They look the same to me.[/color]

Perhaps bobo meant that he has one table with 2 columns (Id, Detail)
and wants to transform it into a new table with 4 columns (Id, Value1,
Value2, Value3).

If this was the question, then
1. Use a recordset to retrieve rows from the first table
2. While no more rows in recordset
a. use string parsing functions (Mid, Left, InStr, etc)
to parse each value after the "=" sign and before the comma
","
b. insert into the new table using the values just parsed
3. close recordset

Refer to the On-line help documentation for how to use the string
parsing functions.

Hope this was helpful.
Steve Cummings

[borislav simeonov]

Re: text to column





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[borislav simeonov]

Re: text to column



Yes this work just fine
Thank you

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!