Separating Text Data in a field

  • Thread starter Thread starter Don Garry
  • Start date Start date
D

Don Garry

Hello, I've just imported some inconsistent data from a csv file into an
Access2000 database. Most of the fields come through just fine except a
field where the data is separated by .

Here's an example of what it looks like:
APLUSJohn DoeShineproIcbc

Anyone have any ideas on how I might separate this data into separate fields
progammatically ?
 
Don Garry said:
Hello, I've just imported some inconsistent data from a csv file into an
Access2000 database. Most of the fields come through just fine except a
field where the data is separated by .

Here's an example of what it looks like:
APLUSJohn DoeShineproIcbc

Anyone have any ideas on how I might separate this data into separate fields
progammatically ?
Click to expand...
Hi,
Is that all one field or where are the commas?
If it is comma delimited or set width fields it is straight forward through
the import wizard - File -> get External Data -> choose csv file. The wizard
will prompt you through the rest.

If not - I would go back to the source and ask nicely for a importable file,
unless you relish many hours of programming for something which should take
2 minutes.
HTH
Marc
 
Don,

First you need to find out what the separator character () is. You can do that
with this expression:
SepChr = Asc(Mid(MyField,5,1))
To get "APlus":
FirstField = Left(MyField,Instr(MyField,SepChr)-1)

To get "John Doe":
SecondField =
Mid(MyField,Len(FirstField)+1,Instr(Len(FirstField)+2,MyField,Instr(MyField,SepC
hr)-1))

To get "Shinepro":
ThirdField =
Mid(MyField,Len(FirstField&SecondField)+2,Instr(Len(FirstField&SecondField)+3,My
Field,Instr(MyField,SepChr)-1))

To get "lcbc":
FourthField =
Right(Left(MyField-1),Len(Left(MyField-1)-Len(FirstField&SecondField&ThirdField)
-3)

Wheww-www!!!
 
Thanks for your help....I'm kinda new at this so here is the code I tried
to use to separate the field based on your email and it ran into a run time
error. SepChar ended up = 85 and the the following code bombed after that.
I'd sure appreciate a little additional guidance if you could.
******
Public Sub splitter()
Dim rst As Recordset
Dim myField As Field
Dim firstField As Field

Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "VehicleCosts", , , , adCmdTable


rst.MoveFirst

Set myField = rst.Fields("Supplier")
SepChr = Asc(Mid(myField, 5, 1))
firstField = Left(myField, InStr(myField, SepChr) - 1)
'I get runtime error 5....invalid call or argument.
secondField......etc.
End Sub
 
Don,

Change these:
Dim myField As Field
Dim firstField As Field
To:
Dim myField As String
Dim firstField As String
Add:
Dim SecondField As String
Dim ThirdField As String
Dim FourthField As String

To test the equations:
Add:
MsgBox FirstField
---Right after FirstField = ............

Do the same for SecondField, ThirdField and FourthField

Report back what you get in each of the message boxes and if the messages are
your fields.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
 
Hello Steve, first off I really appreciate you spending the time to help me
out !!

Secondly, Everything runs fine until I get to the FirstField = line and then
I'm still getting the following error code: I'm using Access 2000.
*********
Public Sub splitter()
Dim rst As Recordset
Dim MyField As String
Dim FirstField As String
Dim SecondField As String
Dim ThirdField As String
Dim FourthField As String



Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "VehicleCosts", , , , adCmdTable


rst.MoveFirst

MyField = rst.Fields("Supplier")
SepChr = Asc(Mid(MyField, 5, 1))

'To get "APlus":
FirstField = Left(MyField, InStr(MyField, SepChr) - 1)
MsgBox FirstField
'I get runtime error 5....invalid call or argument.
'Some part of the call can't be completed. This error
'has the following causes and solutions:

'An argument probably exceeds the range of permitted values.
'For example, the Sin function can only accept values within
'a certain range. Positive arguments less than 2,147,483,648
'are accepted, while 2,147,483,648 generates this error.
'Check the ranges permitted for arguments.

'This error can also occur if an attempt is made to call a
'procedure that isn't valid on the current platform. For example,
'some procedures may only be valid for Microsoft Windows, or for
'the Macintosh, and so on.
'Check platform-specific information about the procedure.

*********
 
Don Garry said:
Thanks for your help....I'm kinda new at this so here is the code I tried
to use to separate the field based on your email and it ran into a run time
error. SepChar ended up = 85 and the the following code bombed after that.
I'd sure appreciate a little additional guidance if you could.
Click to expand...

You still haven't determined the actual value of the 'SepChar'. Chr$(85) is
an
upper case U. My guess is that you have space characters at the beginning
of your target string. No code will return correct results until you
correctly identify
'SepChar'.

From your result I'm guessing SepChr = Asc(Mid(myField, 7, 1)) will
identify
the correct SepChar.
 
Your absolutely right , when I shifted over by one I believe I now have the
correct identifier which is an asc code '29' which looks like a square ().
Here's what MyField looks like "A PLUSDon StobbeShineproICBCA PLUS"
Were making progress but the same line of code still bombs. Any further help
would sure be appreciated.

Here's the latest code:
*********
Public Sub splitter()
Dim rst As Recordset
Dim MyField As String
Dim FirstField As String
Dim SecondField As String
Dim ThirdField As String
Dim FourthField As String



Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "VehicleCosts", , , , adCmdTable


rst.MoveFirst

MyField = rst.Fields("Supplier")
SepChr = Asc(Mid(MyField, 7, 1)) 'this now shows as code 29

'To get "APlus":
FirstField = Left(MyField, InStr(MyField, SepChr) - 1)
' this is still bombing with 'error 5 invalid call or argument.
'Some part of the call can't be completed. This error
'has the following causes and solutions:

'An argument probably exceeds the range of permitted values.
'For example, the Sin function can only accept values within
'a certain range. Positive arguments less than 2,147,483,648
'are accepted, while 2,147,483,648 generates this error.
'Check the ranges permitted for arguments.

'This error can also occur if an attempt is made to call a
'procedure that isn't valid on the current platform. For example,
'some procedures may only be valid for Microsoft Windows, or for
'the Macintosh, and so on.
'Check platform-specific information about the procedure.

*********
 
Don,

Let's next make sure the Left function is working. Comment out the FirstField
..... line you have and add this right below it:
FirstField = Left(MyField, 5)

Below MsgBox FirstField Add:
Exit Sub


Try this and see if you get a message with MsgBox FirstField

You are using ADO. DAO is a better programming library for what you are doing
here. Is there a reason you are using ADO?
 
Don Garry said:
Your absolutely right , when I shifted over by one I believe I now have the
correct identifier which is an asc code '29' which looks like a square ().
Here's what MyField looks like "A PLUSDon StobbeShineproICBCA PLUS"
Were making progress but the same line of code still bombs. Any further help
would sure be appreciated.

Here's the latest code:
*********
Public Sub splitter()
Dim rst As Recordset
Dim MyField As String
Dim FirstField As String
Dim SecondField As String
Dim ThirdField As String
Dim FourthField As String



Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "VehicleCosts", , , , adCmdTable


rst.MoveFirst

MyField = rst.Fields("Supplier")
SepChr = Asc(Mid(MyField, 7, 1)) 'this now shows as code 29

'To get "APlus":
FirstField = Left(MyField, InStr(MyField, SepChr) - 1)
' this is still bombing with 'error 5 invalid call or argument.
Click to expand...

You need to do some old fasioned debugging.
Try the following and see if it helps you sort things out.

After 'To get "APlus": add:

dim found as integer

found = InStr(MyField, SepChr)
if found > 0 then
debug.print found
FirstField = Left(MyField, found - 1)
else
msgbox "The seperation character was not found in the string " & _
vbcrlf & Myfield
end if
 
Thanks again for you help. The Left function works fine but I'm still
getting an error.
I've tried the following suggested code from another person trying to help
and for whatever reason it doesn't see the separation character.
******
dim found as integer

found = InStr(MyField, SepChr)
if found > 0 then
debug.print found
FirstField = Left(MyField, found - 1)
else
msgbox "The seperation character was not found in the string " & _
vbcrlf & Myfield
end if
*******
I've tried both ADO and DAO and it doesn't seem to make a difference. Any
further suggestions as I'm seem to be getting in over my head on this little
problem.

One final item is that I probably was using ADO because the last little
project I did was an .adp and just got used to it. So far everything seems
to be working OK.

Am I heading down a rat hole by using ADO ?

P.S. I guess I'm still pretty new to all this stuff and from what I've seen
so far there is always a better way.
 
Don Garry said:
Thanks again for you help. The Left function works fine but I'm still
getting an error.
I've tried the following suggested code from another person trying to help
and for whatever reason it doesn't see the separation character.
******
dim found as integer

found = InStr(MyField, SepChr)
if found > 0 then
debug.print found
FirstField = Left(MyField, found - 1)
else
msgbox "The seperation character was not found in the string " & _
vbcrlf & Myfield
end if
*******
Click to expand...

Maybe you should load your original data (from the file you imported) into
a hex editor an see what the heck that box character actually is. Your
entire
problem centers around not being able to locate and identify that character.
 
Back
Top