Transfer Text

  • Thread starter Thread starter TIML
  • Start date Start date
T

TIML

I have the following code written to import a text file comma delimated. I
want to be able to import the file comma and space delimated. Is there a
way to do that?

DoCmd.TransferText acImportDelim, _
"Receive Import Specification", "Receiv1", _
"\\George\Public\Common\Falcon\Receiv1.txt"

The file has 3 fields at the beginning that are seperated by spaces and the
rest of the fields are seperated by comma's.

ie. 3078109 EB0403 03/19/03,REBOX,1600,19:47:23,04/02/2003,,BC1

Currently, I bring in the file and use Left, Mid and Right to seperate the
the first 3 fields, but they are not always the same length and it is
causing problems.

Thanks!
 
Tim

you could try this:

Dim strField123 as String
Dim strField1 As String
Dim strField2 As String
Dim strField3 As String
Dim lngSpace1 As Long
Dim lngSpace2 As Long
Dim lngLength As Long

strField123 = Trim(strField123)
lngLength = Len(strField123)
lngPosition1 = InStr(1, strField123, Space(1))
If lngPosition1 = 0 Then
strField1 = strField123
strField2 = ""
strField3 = ""
Else
lngPosition2 = InStr(lngPosition1 + 1, strField123,
Space(1))
If lngPosition2 = 0 Then
strField1 = Left(strField123, lngPosition1 - 1)
strField2 = Right(strField123, lngLength -
lngPosition1)
strField3 = ""
Else
strField1 = Left(strField123, lngPosition1 - 1)
strField2 = Mid(strField123, lngPosition1 + 1,
lngPosition2 - lngPosition1 - 1)
strField3 = Right(strField123, lngLength -
lngPosition2)
End If
End If

or this:

Dim strField123 as String
Dim strField1 As String
Dim strField2 As String
Dim strField3 As String
Dim strCharacter As String
Dim lngLength As Long
Dim lngPosition As Long
Dim lngCounter As Long

strField123 = Trim(strField123)
strField1 = ""
strField2 = ""
strField3 = ""
lngCounter = 1
lngLength = Len(strField123)

For lngPosition = 1 To lngLength
strCharacter = Mid(strField123, lngPosition, 1)
If strCharacter = Space(1) Then
lngCounter = lngCounter + 1
Else
Select Case lngCounter
Case 1
strField1 = strField1 & strCharacter
Case 2
strField2 = strField2 & strCharacter
Case 3
strField3 = strField3 & strCharacter
Case Else
MsgBox "Too many spaces in the first field of
this record:" & vbCrLf & _
strField123, vbExclamation, "Seperate
field..."
FieldSeperator = Null
Exit Function

End Select
End If
Next lngPosition

'strField123 contains the value "3078109 EB0403 03/19/03"
'at the end strField1, strField2 and strField3 will
'contain the seperated values "3078109", "EB0403"
'and "03/19/03". it doesn't matter how long the fields
'are. There will be an error message when there are more
'than 2 seperating spaces in the value (second solution).

success
 
Thanks for the help!! How do you combine this code with the rest of the
text file that is seperated by comma's and import that into access?

Tim
 
Back
Top