Solved: Import Data from text document

[bkudulis]

I am trying to split out data from a text document. I would like to get the numbers from the field which are located to the right of the line in their own field. The problem is that the numbers are not always of equal length and there are multiple spaces. Here are a couple examples of the data. I want to import this into a table.

31145 Apodaca, Tony P 83.00
36101 Acevedo, Carlos 1163.00
36379 Miller, Michael E 1684.50

[Lister]

Not looking good I'm afraid, I have tried a few ways to alter the data but without sitting down and doing a good amount of cut past. I couldn?t get your three lines of text into Access.

Even just adding tabs between each group didn?t work.

How many records/lines of data do you have to enter? If its less than 200, start cutting and pasting.

[SJ McAbney]

Simple way would be to copy the text into Excel and se the Text To Columns method on it. Then import the spreadsheet into Access.

[bkudulis]

Is there any way to start a search from the right to look for the left most number? How about remove all of the spaces and do a search from the left for a number after the sixth position? Just reaching here.

[lynn_victoria]

using vba, remove spaces, using string functions, test to see if char is a char or int, then add to variable, then cut.
cant do example today, but if you can wait a day or two will be happy to try to work out something for you.

[babsc01]

I agree with Abulafia...should break up the strings into columns before importing. But, if you can't do that, try this function. I use it in a lot of my queries. It identifies "substrings" within a longer string, and will allow you to break up the longer string into columns. This assumes the substring in each record will always be in the same location in each string.

[VBA] Function xg_GetSubString(mainstr As String, n As Integer, delimiter As String) As String
'* Get the "n"-th substring from "mainstr" where strings are delimited by "delimiter"
Dim i As Integer
Dim substringcount As Integer
Dim pos As Integer
Dim strx As String
Dim val1 As Integer
Dim w As String
On Error GoTo Err_xg_GetSubString
w = ""
substringcount = 0
i = 1
pos = InStr(i, mainstr, delimiter)
Do While pos <> 0
strx = Mid(mainstr, i, pos - i)
substringcount = substringcount + 1
If substringcount = n Then
Exit Do
End If
i = pos + 1
pos = InStr(i, mainstr, delimiter)
Loop
If substringcount = n Then
xg_GetSubString = strx
Else
strx = Mid(mainstr, i, Len(mainstr) + 1 - i)
substringcount = substringcount + 1
If substringcount = n Then
xg_GetSubString = strx
Else
xg_GetSubString = ""
End If
End If
Exit Function
Err_xg_GetSubString:
MsgBox "xg_GetSubString " & Err & " " & Err.Description
Resume Next
End Function
[/VBA]

[VicRauch]

bkudulis

Try this code. It worked for me.

[VBA] Option Compare Database
Global RetVal As Variant 'Used and reused as a Return Variable
Global txtID As String
Global txtName As String
Global txtAmount As String
'The idea here is to pass in each record, have the routine put
' the data into individual fields, and return. The individual
' fields are set as global so you can retrieve the values in
' the code you request this information from.
Function GetTextRecordFields(txtRecord) As Boolean
'Start on the left, knowing we have three fields per record
' and assuming the first field is always 5 digits long. Plus,
' assume the name always starts in the 7th position and goes
' until the "Amount" field starts. Then we will look for the
' "Amount" field starting at the end of the record and moving
' to the left until a blank is found. The name field will be
' everything between the 7th position and the start of the
' "Amount" field.
Dim i As Integer
On Error GoTo GetTextRecordFieldsError
txtID = Left(txtRecord, 5)
For i = Len(txtRecord) To 1 Step -1
If Mid(txtRecord, i, 1) = " " Then Exit For
Next i
txtName = Mid(txtRecord, 7, i - 7)
txtAmount = Mid(txtRecord, i + 1)
GetTextRecordFields = True
Exit Function

GetTextRecordFieldsError:
GetTextRecordFields = False
MsgBox Err.Number & "; " & Err.Description, , "Error Parsing Text Record Fields"
Exit Function
End Function
[/VBA]

Good luck,
Vic