HELP!! Trying to nomalize existing data by updating data into new 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 on 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

 

[Dreamweaver]

Can't you do it by line If each line relates to what you want in a new field Then All you'll need do is use the trim() To clean up the white space.

you'll need to loop through the table one record at a time then loop through the field using either VbCr Or VbCrLf which are line breaks but you'll also need to keep track of where you are in the field loop so that the correct line will be added to the correct Field.

Maybe somebody else will be able to help more but hope it helps.

Mick
p.s. I would add the data to a new table either using a Query or a recordset.

 

[nanabuch]

Thanks Mick,

I do know how to update the data with recordsets or queries, the problem I am having is to physically, locate each set of data in each row, which uniquely identifies the position of each character string that is based primary upon the spaces, meaning the code would seek out the first string by "HOW MANY SPACES or NULL SPACES to the right, and I would either use a recordset or query to update this data to the appropriate columns, based only on the number spaces.

Text Data1 and TextData5 would be automatically updated into Field2 since they have the exact same position. I don't need help with updating data, only a code sample of how to seek the position of the data to move it programmatically.

example again.

Field1
AC1 Text Data
Text Data1
Text Data2
AC2 Text Data4
Text Data5
Text Data6

Thanks again, hopefully this is helpful.
NaNa

 

[SRAB]

If I understand your sample data...is your data entered in a couple of different patterns or is it completely random?
Can you first split the data based on pattern, then pass through each pattern to check for the spaces.
ie put all the AC1 Text Data patterned records into one table
all the Text Data1 records into another. Then parse those into two/three fields by looking for spaces.

 

[Steve R.]

In your sample AC1 is C10A and AC2 is M1A. Will all you AC numbers have an "A" followed by a space? Would this be a unique string?

If that is the case you could identify the beginning of a record by finding an "A" followed by a space with the first five characters.

 

[nanabuch]

Hi Ortiasis and everyone thanks for the response!!

The problem I am having is that I have all the existing data is in one column, I need to move all relevant data into the same columns, the problem when I give you sample data with spaces DO NOT SHOW UP on this website when I hit the submit button.

But anyway, the main thing I am trying to accomplish is to create some type of routine such as a loop or I believe a string routine which may work like left, right, mid, or len strings. The code or function needs to update the data by the POSITION of the data ONLY, such as null space position. Example all data that has 3 spaces to the right would be updated into same field (column), all data that has 4 spaces to the right would be updated into the same coumn and so forth, that is the pattern that I am looking. Hope this makes sense.

NaNa...thanks again!!! you guys are great!!

 

[Bat17]

you can use the [ code ] tags to display data

Field1:
C10A CHOLEST&TRIGLY 
ATORVASTATIN   
LIPITOR   
PFIZER     
SIMVASTATIN   
SIMVASTATIN TEVA    
TEVA   
M1A ANTIRHEUMATIC N-STEROID   
ETODOLAC   
ETOPAN   
TARO PHARMA

if you are trying to work out how many spaces there are at the end of the row you could probably just do it by comparing lengths before/after trimming.

NumberOfSpaces = len([myfield])-len(trim([myfield]))

peter

 

[nanabuch]

Thanks Peter,

I need to determine how many null spaces there are at the beginning of the field, not at the end. The problem is if I trim the data using an updatable query, it will be very difficult for me to parse the data out. I really need those leading null spaces to update the data with the same number of spaces into columns, example all data that has 3 spaces should be updated in the same column and all data that has 4 beginning spaces would be updated in the same column.

Thanks
Nana

 

[The_Doc_Man]

You have posted this same question under "General" and "Queries" - please do not do this. Pick one or the other and let it ride.

 

[nanabuch]

Hello DocMan,

I am very sorry for this, is there anyway to delete this once? I am getting a great feedback on the other thread, I am a newbie here. I apologize.

Nana

 

[Bat17]

you can do the work on the fly with live data, no need to update first.
If you are sure that none of the line could start with a digit then you could use something like
Val(Replace([myField], " ", "1"))
to get a numeric value dependent on number of spaces

Peter