How to import an unstructured text file into an access table?

[shawnnnnnnn]

Hi all, I'm really at my wits end already. I can't seem to figure out how to import a text file that has no delimiters or common field widths.

Basically, I have a text file containing events that happened to a system over the month. I need to somehow create a table to store all the details in it.

I can't change the text file as the program that generates it only does it this way. However, the program made it such that every 6 lines is a record, meaning the details of 1 event is stored within every 6 lines.

A sample of the text file is given below:

ES~1~1412179200(Oct 02 00:00:00 2014)~1~ITM_W64MQMONITORLOG00~
ITM~W01XXASPAXX1B:W6400~12.34.56.78~~W01XXASPAPP1B~W01XXASPAXX1B~ACK~
Danny~[ admin]~WARNING~
10/01/2014~
WAR_umW64_MQMOXXXORLOG[(STRSCAN(Logline, "CURDEPTH") = 1 ) OR ( ST=1 OR STRSCAN(Logline, "STATUS") = 1 ) ) ;W642=1 ) ON W01GGASXXXX1B:W6400 (Logline=   CURDEPTH(4642) )]~
CORE_INFRA~0~
0~0~ES~1~1412179203(Oct 02 00:00:03 2014)~1~ITM_Log_Entries~
ITM~a01gibestmtrep2a:KUL~12.34.56.78~~a01gibesxxxrep2a~a01gibxxxmtrep2a~ACK~
Karthi~[ admin]~WARNING~
10/02/2014~
WAR_ulGIBESTXXXEP_EADVICE_XXX[(Log_Path="/estmtrephk/ibrhk/eSRBatch/logs/" AND Log_Name="eaxxxceSchedule.log" AND STRSCAN(Descrxxxion_U, N"dir=/usr/java6/jre/lib/ext") = 1 ;XXXOGENT.ENTR=1 ) ON a01gibxxxmtrep2a:KUL (Log_Path=/estxxxephk/ibxhk/exxxxxch/logs/ Log_Name=eadvixxXedule.log Description_U=dir=/usfssr/javaaa6/jreasdfsa/lib/ext )]~
APPS~0~

The sample shows 2 records/events.
If the line contains "ES", it's the start of a record/event.
I need to insert into the database the date (first line), the admin acknowledge (third line), the details (fifth line) and the event name (sixth line).

[Seth Schrock]

It looks like the ~ is the delimiter. Using the sample you provided above, please post the exact bits of information that you want for each field. In other words, write out what the record would look like using the sample data above. For example, for the admin acknowledge, which of the following do you want stored?

1. Karthi
2. [ admin]
3. WARNING
4. Karthi~[ admin]

I think that this won't be too hard once we get the information that we need.

[jimatqsi]

Shawn, it's not clear what you're asking for. Are you okay with writing some VBA code? The most obvious solution would be to link to the text file (using DoCmd.TransferT ext Method). You can treat the entire row as either a single text string (or memo, depending on the size). Once you have a link to the file just parse each row.

Looks like possibly ~ is delimiting fields. If that's true you can use the SPLIT function to break each line into an array of fields to simplify.

Does that help?

Jim

[shawnnnnnnn]

Hi, the records should look like

Date. | Acknowledgment | Details. | Event name
Oct 02 00:00:00. | Yes. | WAR************ ******)]. | APPS


The yea or no would depend on the positioning of the "[admin]" in the line. If it is at the start of the line, it's a yes. If it's in the middle or at the back, it's a no.

E.g. [Admin]~Danny ~WARNING~ = yes
Danny~ [Admin] ~WARNING~ = no

[shawnnnnnnn]

Hi Jim. I'm pretty new to VBA, so I may need a clearer explanation. I am able to modify codes but not skilled enough to write from scratch

[shawnnnnnnn]

Hi all,

I managed to populate a 6 column table with the data. However, another problem just surfaced.

Somehow, there are certain lines of data that are so long that the closing brackets ")]~" end up on the next line, thus, it messed up the tables.

E.g.
[(Timeout has expired. (Code 15)
)]~

So instead of having "[(Timeout has expired. (Code 15) )]~" inside 1 field, it actually seperates into "[(Timeout has expired. (Code 15)" and ")]~".

Is there a way to use vba to format the txt file such that these closing brackets are joined back to the previous line?

Or an alternative is to completely remove those files with 7 lines due to the closing brackets. So, from my really limited vba knowledge, I think I should use a loop to count the number of lines between each "ES~" and remove those that are more or less than 6 before populating the raw data table with it.


Is this possible? And how do I do a loop to count the lines?

[jimatqsi]

Shawn,
Let's start by getting an understanding of what you've done and how you've done it. Explain "I managed to populate a 6 column table with the data." How did you do that? When we know that, we might know how you are getting lines split in two parts.

Jim

[shawnnnnnnn]

I basically just imported the file into a table with only 2 columns (ID, Field1) using access's import function, so that every line of the text file is in 1 field.

I then used this code to seperate to transfer it into 6 columns.

Private Sub Command0_Click()
    Dim dbs As DAO.Database, rst As DAO.Recordset, rstInsert As DAO.Recordset, x As Long
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("DataTableFrom")
  Set rstInsert = dbs.OpenRecordset("InsertedTable")
  If Not rst.EOF Then
    Do
      If x Mod 6 = 0 Then
        rstInsert.AddNew
        rstInsert![Field1] = rst![Field1]
      ElseIf x Mod 6 = 1 Then
        rstInsert![Field2] = rst![Field1]
      ElseIf x Mod 6 = 2 Then
        rstInsert![Field3] = rst![Field1]
      ElseIf x Mod 6 = 3 Then
        rstInsert![Field4] = rst![Field1]
      ElseIf x Mod 6 = 4 Then
        rstInsert![Field5] = rst![Field1]
      ElseIf x Mod 6 = 5 Then
        rstInsert![Field6] = rst![Field1]
        rstInsert.Update
      End If
      x = x + 1
      rst.MoveNext
    Loop Until rst.EOF
  End If
End Sub

I have attached a sample of my text file. It contains 3 different events, the first 2 events are both normal as they are within 6 lines, however the last event has 7 lines due to the ")]~" being on the next line from where it was intended to be.

I don't think I'll be able to change it as this text file is generated by a program.

I just need to figure out how to ignore all the 7-liner events and populate a table with the 6-liner events for now.

[twinnyfo]

Shawn,

I think you will have to explore parsing each line to determine its content, as explained in Post #3. Once you get the bits and pieces evaluated, you can figure out what to do with them.

[shawnnnnnnn]

Thanks Twinny, I've a rough idea of how to do it already :) Hopefully it works!