Text File via Putty into Access

You'll need to OPEN the file in VBA, then read with INPUT, and parse the record using Left$(), Mid$(), InStr$(), etc.

 Unless the delimiters are specific and consistent (and they don't appear to be), then using TransferText won't work well.

Jim.

How many records are there?
Can you given a sample table, with how you want the text separated out?

@jim.  Can you give me some code.  As that is really what I need help with.  I don't know how the left$, etc work.  How do I get each value into each field.  If you need field names I can give those to you.

@jerryb30 this process could be done 15 times a day with just one record.  There would be about 10 fields the information would go into.

Below is an example of some import code.  This deletes all records from a local table, reads a text file, parses the records into fields, then saves a record into the local table.

Jim.


          ' Delete existing status table
550       strSQL = "DELETE FROM tbl_SOWhseShipStatus_Import"
560       CurDb().Execute strSQL, dbFailOnError

570       Set rstStatusImport = CurDb().OpenRecordset("tbl_SOWhseShipStatus_Import")

          ' Import the file into the local temp table
580       intFileNumber = FreeFile
590       Open strTempFile For Input As intFileNumber

600       Do Until EOF(intFileNumber)
610           Line Input #intFileNumber, strRecord
620           If strRecord <> "" Then

                  ' Based on the location, parse the record.
630               Select Case strLocation

                      Case "zzz"
640                       intPosition = InStr(1, strRecord, ",")
650                       strTransID = Trim$(left$(strRecord, intPosition - 1))
660                       strStatus = Trim$(Mid$(strRecord, intPosition + 1))
670                       intPosition = InStr(1, strStatus, ",")
680                       varStatusDT = DateValue(Mid$(strStatus, intPosition + 1, 10)) + TimeValue(Mid$(strStatus, intPosition + 12, 5))
690                       strStatus = left$(strStatus, intPosition - 1)

700                   Case "yyy"
710                       intPosition = InStr(1, strRecord, "|")
720                       strTransID = Trim$(left$(strRecord, intPosition - 1))
730                       varStatusDT = Now()
740                       strStatus = Trim$(Mid$(strRecord, intPosition + 1))

750                   Case "xxx"
760                       intPosition = InStr(1, strRecord, "|")
770                       strTransID = Trim$(left$(strRecord, intPosition - 1))
780                       varStatusDT = Now()
790                       strStatus = Trim$(Mid$(strRecord, intPosition + 1))
800                       intPosition = InStr(1, strStatus, "|")
810                       strStatus = left$(strStatus, intPosition - 1) & Mid$(strStatus, intPosition + 1)

820                   Case Else
830                       MsgBox "Location import not defined"

840               End Select

850               rstStatusImport.AddNew
860               rstStatusImport![TransId] = strTransID
870               rstStatusImport![Status] = strStatus
880               rstStatusImport![StatusDT] = varStatusDT
890               rstStatusImport.Update
900           End If

910       Loop

          ' Done reading.
          ' Close and delete.
920       Close #intFileNumber

          ' Kill the temp file
930       On Error Resume Next
940       Kill strTempFile
950       On Error GoTo GetOrderStatusFile_Error

960       rstStatusImport.Close
970       Set rstStatusImport = Nothing

I have seen those before but can you give me little more guidance (hand holding).  I want the following "VD12.00WD35OL+00.75-00.25004OR+01.25-00.00000PD59??????
DKM L07.5407.4700307.51 R07.5807.5517307.57" to be placed in different fields as such.  I don't need you to do all of them but can you explain three of them:
VD12.00
D35
+00.75
-00.25
004
OR+01.25
-00.00000
PD59
L07.54
07.47
003
07.51
R07.58
07.55
173
07.57

I'd need the actual raw data.  

Jim.

the following is the actually raw data, just copied and pasted from the txt file:
The following is what I get from a text file from Putty.  =~=~=~=~=~=~=~=~=~=~=~= PuTTY log 2014.01.09 16:14:44 =~=~=~=~=~=~=~=~=~=~=~=

DRDmodel/RTT-1133NO56899DAJAN/09/2014.16:54VD12.00WD35OL-01.00-01.75087
DKM L07.5907.3803507.49 R07.9707.3014407.64
DNT L01    20/2.7*2AV20.0/2.70 R02    20/2.7      23/3.1*2 AV21.5/2.9027AD
DRDmodel/RTT-1133NO56900DAJAN/09/2014.17:17VD12.00WD35OL+00.75-00.25004OR+01.25-00.00000PD59??????
DKM L07.5407.4700307.51 R07.5807.5517307.57
DNT L02    15/2.0     18/2.4*2AV16.5/2.20 R01    15/2.0*3 AV15.0/2.002D8F

<<
PriceD Member Since: 07/17/2009 400 Solution(s) the following is the actually raw data
>>

 No, I mean I need the file itself.  Many of the characters I'm sure are non-visible.  I need to look at them either in VBA or with a Hex editor.

Jim.

Ops.  Here you go.
putty.log

Is that a complete file or no?  Doesn't look like it.  If you un-comfortable with posting a complete file, send it directly to me jimdettman"at"online-computer-services.net

What I'm trying to determine is:
a. What the end of file marker is
b. What the end of record delimiter is
c. What the field delimiters are.

 I've got c and possibly b, but not a.

 If the delimiters are consistent, then you can use SPLIT() to break up a record into fields quite easily.

  There's also the issue of record length, which looks to be variable.  DRD, DNT, and DKM appear to be record type flags, with each having a different number of fields.

Jim.

That is it.  Its measurements for one individual.  Once the measurements are done, they are copied to the log file you see, and then I need to import that log file into access to go into specific fields.  That will be the size of the file each time just with different numbers.  We will need to do this about 10 or 15 times a today, and each of the 15 times the file is overwritten with the information above; again just with different numbers.  With that said the numbers each time the numbers may change but the sequence of structure is the same, such as "DAJAN/09/2014.16:54VD12.00WD35OL-01.00-01.75087
DKM L07.5907.3803507.49 R07.9707.3014407.64"

"DAJAN/09/2014" - it will always state DA for date the next three letters will represent the month, the next two number will be the date and the next four will be the year.

VD12.00 - It will also have VD and four number with the "." between the two.

OL-01.00-01.75 - OL will always befollowed by a minue or plus and four number with a decimal between and the next set will be another set of four numbers with a decimal between them (these numbers will always be a set of four numbers with two number on either side of the decimal.

Hope that helps.

OK, see if this gets you going.

Code:

Sub ReadLogFile()

  Dim strFile As String
  Dim intFileNumber As Integer
  Dim strRecord As String
  Dim varFields() As String
  Dim intK As Integer
  Dim intPos As Integer
 
  strFile = "C:\Putty.log"
  intFileNumber = FreeFile

  Open strFile For Input As intFileNumber

   
  ' Read the log header rec
  Line Input #intFileNumber, strRecord
   
  ' Read the data
  Line Input #intFileNumber, strRecord
 
  varFields = Split(strRecord, Chr$(23))
 
  For intK = 0 To UBound(varFields) - 1
    Debug.Print intK, varFields(intK)
  Next intK
 
  'Examples
  Debug.Print Left$(varFields(2), 13)
  Debug.Print Mid$(varFields(2), 3, 3)
  Debug.Print Mid$(varFields(2), 7, 2)
  Debug.Print Mid$(varFields(2), 10, 4)
 
  intPos = InStr(1, varFields(2), ".")
  Debug.Print Mid$(varFields(2), intPos + 1, 5)
   
 
  ' Done reading.
  ' Close and delete.
  Close #intFileNumber

End Sub

Output:

call ReadLogFile
 0            
DRMIDNIDEK/RKT-7700
 1            NO5708
 2            DAJAN/09/2014.16:54
 3            VD12.00
 4            WD35
 5            OL-01.00-01.75087
 6            
DKM L07.5907.3803507.49
 7             R07.9707.3014407.64
 8            
DNT L01    20/2.7*2AV20.0/2.70
 9             R02    20/2.7      23/3.1*2 AV21.5/2.90
 10           27AD
DRMIDNIDEK/RKT-7700
 11           NO5709
 12           DAJAN/09/2014.17:17
 13           VD12.00
 14           WD35
 15           OL+00.75-00.25004
 16           OR+01.25-00.00000
 17           PD59??????
 18          
DKM L07.5407.4700307.51
 19            R07.5807.5517307.57
 20          
DNT L02    15/2.0     18/2.4*2AV16.5/2.20
 21            R01    15/2.0*3 AV15.0/2.00
DAJAN/09/2014
JAN
09
2014
16:54

  That's a good example of how to start breaking up the file.  But since I don't understand the format, then it may take a slightly different form.

 For example, this file appears to have two sets of measurements (I see two date/time stamps).  So you might have to read the "records" into a buffer, then strip off one measurement at a time, split that into fields, then parse the fields.

  Or you can just keep a position in the string, and keep parsing forward through the string.  When you run out of string, you read more records from the file.

  Hard to give more specifics at this point without understanding what's there.

Jim.

This is great and it what I need.  The biggest thing about the code is how do you tell it "see" the file with the code at at what line or space?

What does the code actually tell it to do
varFields = Split(strRecord, Chr$(23))
 
  For intK = 0 To UBound(varFields) - 1
    Debug.Print intK, varFields(intK)
  Next intK
 
  'Examples
  Debug.Print Left$(varFields(2), 13)
  Debug.Print Mid$(varFields(2), 3, 3)
  Debug.Print Mid$(varFields(2), 7, 2)
  Debug.Print Mid$(varFields(2), 10, 4)

It think the first one says go in 23 character, etc.  But how do I interpret the others?