How to move data from one colum by data position to other fields

[nanabuch]

Hello Tech gurus,

I have a database with existing data, that is not normalized, and all the data is in one field... This what I am dealing with:

As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY one the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.

Field1:
C10A CHOLEST&TRIGLY
ATORVASTATIN
LIPITOR
PFIZER
SIMVASTATIN
SIMVASTATIN TEVA
TEVA
M1A ANTIRHEUMATIC N-STEROID
ETODOLAC
ETOPAN
TARO PHARMA
Thanks so much for your help...

Nana

 

[M8KWR]

The only way i can thin of doing this is to create a new column and work out how many leading spaces each string in the first field has.....

I am taking from the data you copied into the post, it does not show this spaces!!!!!

I think i may have a function that i can modify to do what you require, so it can tell you have many spaces you have, and then you can move the fields according to this value easily.

If you can post some actually data containing these spaces so i can use that to test the function before providing it for you.

 

[The_Doc_Man]

You are looking at, not "normalization" but "justification" - a word processing function. There is no apparent reason to choose position vs. the other for your data. What are you REALLY trying to do?

 

[nanabuch]

Hello M8KWR,

Thanks for responding, I am not sure why the data does not show up in view of the spaces...that stinks because you can get a clearer picture of what I am trying to accomplish..

Let me try to space the data again, with just sample data.
Data Text 1
Data Text 11
Data Text 111
Data Text 2
Data Text 22
Data Text 222
You see the pattern, I am hoping that you can see the spaces, somehow when I use the space bar it does not show up when I submit the messagae but anyhow if you cannot see the space, what I am trying to do is, just assume that Data Text1 is indented with 3 spaces over to the right and the next line Data Text11 is indented with 4 spaces over to the right, all the data that has the exact same number of spaces gets updated into the "SAME NEW COLUMN, I do know how to update recordsets with queries, but the problem I am having is trying to identify the precision of the spaces like using string methods like right, mid, len or left, when I try those functions it only separates the entire text, I know there is a way to do this, I would really appreciate any insight.

Thanks!!
Nana

 

[M8KWR]

i think i understand what you are tryin to achieve.... i have a module that i have created to remove space and characters from a string to leave myself with only numbers.... i will alter this for your appliations to basically to look at each characters from the right of the string, and to count the number of spaces that occurs, and when it hits a normal characters it will move on...

I hope that is what you are tryin to do, just bare with me altering the module, its quite simple to interate it into a query for example, and i will explain do not worry!!!

 

[M8KWR]

ok.... create a module and copy the code below

Function CountSpaces(ByVal AlphaNum As Variant)


   Dim SpaceCount
   Dim Pos, A_Char$

   Pos = 1
   SpaceCount = 0
   If IsNull(AlphaNum) Then Exit Function

   For Pos = 1 To Len(AlphaNum)
      A_Char$ = Mid(AlphaNum, Pos, 1)
      If A_Char$ = " " Then
      
       SpaceCount = SpaceCount + 1
      
      Else
      
         SpaceCount = 0
      End If
   Next Pos

   CountSpaces = SpaceCount

End Function

if you create a new field in the table you wish to count the spaces to the right, and i have in my example called it "SpaceCount". I do not know the name of your table, but again in the code below i have just called it table1. The field from Table 1 that i am counting the spaces from i have called field1.

You can just alter the code to suit your field and table names etc.

What i am going to do is create an update query to put the number of the spaces in the SpaceCount field, then from there you are able to run others queries to move the data to certain columns etc.

here we go.... create a new query, no need to add a table just go straight to the sql code windows and paste the following code in, it will work as long as you have renamed the fields correctly. Just in case backup your table before you go ahead and run the query.

UPDATE table1 SET table1.spacecount = countspaces([field1]);

Click on the "!" sign to run the query, and it should work....

Let me know how you get on.

M8KWR

 

[nanabuch]

Thanks MB8KWR,

Wow, I think this is exactly what I am looking for, but somehow I get this error when trying to run the code in the SQL window "Undefined function 'countspaces' in expression".

I changed the table name as "table1", and the Data Field as "field1" and the column I am updating the data to as "countspaces"

Any ideas? Could it be that we did not declare "SpaceCount" as string? in the code...

Thanks so much, you just don't know how much I appreciate this.

Nana.

 

[KeithG]

Change this

Function CountSpaces(ByVal AlphaNum As Variant)

to this

Public Function CountSpaces(ByVal AlphaNum As Variant)

 

[nanabuch]

Change this

Function CountSpaces(ByVal AlphaNum As Variant)

to this

Public Function CountSpaces(ByVal AlphaNum As Variant)

Thanks Keith!!, it worked but one problem it updated all zeros only to the "spacescount" field, which I believe is what the code is doing.

This is what I am trying to acheive, all data that has the same number of spaces gets updated into new separate fields, we are testing with spacescount at this point. How do we count the spaces first and then have another routine to move the data into the appropriate fields by space count.

Oh and I forgot to mention, once the data is updated in the appropriate column by spaces, I will clean up the data using the trim function.


Field1

C10A  CHOLEST&TRIGLY.REGULATOR  - 3 spaces
     ATORVASTATIN - 5 spaces
       LIPITOR - 8 spaces
         PFIZER - 9 spaces
     SIMVASTATIN - 5 spaces
       SIMVASTATIN TEVA - 7 spaces
         TEVA
       SIMVACOR
         UNIPHARM
       SIMOVIL

 

[KeithG]

copy this function to your db and give it a go

Public Function Spaces(Text)


    Dim strText As String
    Dim lngLeadSpaces As Long
    
    strText = Text
   
    
    lngLeadSpaces = 0
    
    If IsNull(strText) = True Then GoTo Exit_Loop
    
    For x = 1 To Len(strText)
    
    If Mid(strText, x, 1) = " " Then
        lngLeadSpaces = lngLeadSpaces + 1
    Else
        GoTo Exit_Loop
    End If
    
    Next x

Exit_Loop:


    Spaces = lngLeadSpaces

End Function

 

[The_Doc_Man]

You have posted this problem both in "Queries" and in "General" - please do not do this. Post in one or the other.

 

[nanabuch]

Do I keep the remaining code? in addition to the new code? The new code did not relate to any tables or fields.

Thanks!!

 

[Bat17]

how do you expect this data to look when it has been transformed?
it looks like you will turn 10 rows into 3/4 rows, but what ties them together?
is C10A a reference that will be needed for each row?
I think that you may have to do the whole job in code rather than with a query.

Peter