Solved

Text File via Putty into Access

Posted on 2014-01-13
15
89 Views
Last Modified: 2016-01-05
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.75087DKM L07.5907.3803507.49 R07.9707.3014407.64DNT L01    20/2.7*2AV20.0/2.70 R02    20/2.7      23/3.1*2 AV21.5/2.9027ADDRDmodel/RTT-1133NO56900DAJAN/09/2014.17:17VD12.00WD35OL+00.75-00.25004OR+01.25-00.00000PD59??????DKM L07.5407.4700307.51 R07.5807.5517307.57DNT L02    15/2.0     18/2.4*2AV16.5/2.20 R01    15/2.0*3 AV15.0/2.002D8F

What I need to do is get this information into a table, query or on the "clipboard" with access to copy them to fields.  For example the line "OL-01.00-01.75087DKM L07.5907.3803507.49 " needs to be separated into "L" for left, 01.00 one field, 01.75 into another field, 087 into another field, 07.59 into one field, 0738 into another field, etc.

The process which occurs is the I open putty (or another terminal program), the device print the results on screen and I have them logged into a file via putty.  What I need to do is get them into access and copy them to specific fields of a form.  I can do the pushing them to a form once they are in access.  However, my issue is that I am not sure of how to get the text document sepated and into access.
0
Comment
Question by:PriceD
  • 7
  • 7
15 Comments
 
LVL 57
ID: 39777416
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.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39777604
How many records are there?
Can you given a sample table, with how you want the text separated out?
0
 
LVL 15

Author Comment

by:PriceD
ID: 39779114
@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.
0
 
LVL 15

Author Comment

by:PriceD
ID: 39779116
@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.
0
 
LVL 57
ID: 39779127
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
0
 
LVL 15

Author Comment

by:PriceD
ID: 39779593
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
0
 
LVL 57
ID: 39779663
I'd need the actual raw data.  

Jim.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 15

Author Comment

by:PriceD
ID: 39780740
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.75087DKM L07.5907.3803507.49 R07.9707.3014407.64DNT L01    20/2.7*2AV20.0/2.70 R02    20/2.7      23/3.1*2 AV21.5/2.9027ADDRDmodel/RTT-1133NO56900DAJAN/09/2014.17:17VD12.00WD35OL+00.75-00.25004OR+01.25-00.00000PD59??????DKM L07.5407.4700307.51 R07.5807.5517307.57DNT L02    15/2.0     18/2.4*2AV16.5/2.20 R01    15/2.0*3 AV15.0/2.002D8F
0
 
LVL 57
ID: 39780885
<<
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.
0
 
LVL 15

Author Comment

by:PriceD
ID: 39782114
Ops.  Here you go.
putty.log
0
 
LVL 57
ID: 39782514
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.
0
 
LVL 15

Author Comment

by:PriceD
ID: 39783356
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.75087DKM 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.
0
 
LVL 57
ID: 39785916
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           27ADDRMIDNIDEK/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.
0
 
LVL 15

Author Comment

by:PriceD
ID: 39800002
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?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39800045
<<What does the code actually tell it to do>>

 Look carefully at the output.    What it's doing is looking for a specific character, and then splitting up the record read into fields based on the character.

varFields is an array with one field per element.

So the 3rd field in had this (varFields() is 0 based, 0 is the 1st field, 1 is the 2nd, 2 the 3rd):

2            DAJAN/09/2014.16:54

I then showed you how to break that down:

 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)

 Left give the leftmost 'x' number of characters, Mid() picks out characters in the middle of a string.

<<The biggest thing about the code is how do you tell it "see" the file with the code at at what line or space?>>

  Please understand that there are a number of different ways to read through a file.  This is the simplest, and is based on the fact that:

1. There is a record delimiter
2. There is a field delimiter

 If those don't exist, then there are other ways to read the file. For example a file can be opened as random, where can then individually address each byte in the file.

 Based on what you gave me though, this method should work and what I gave you should get you to where you need to go.

  However since I don't know what this file represents or how it's actually structured, then I really can't give you anything more specific.

Jim.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now