How to import data from text files into access? (1 Viewer)

shawnntjr · Nov 7, 2014

I have a text file that details every single incident that happened in the system. This means that it is extremely messy and non-comprehensible to a normal person due to the use of codewords and all.

Is it possible to scan through the document and insert the data into access, such that the different keywords are put as seperate events?

Ps, the number of characters between each event and data may not always be the same, so seperating them by that is not the way to do it. However, when I open the file in notepad++, I can confirm that each event is 6 lines.

E.g. Line 1 = Event timestamp, Line 2 = Event Name, Line 3 = Acknowledged or not, Line 4 = Acknowledged by who, Line 5 = Event Details, Line 6 = Application Owner.

Basically, the 6 lines are the 6 columns that I would need in my table. So, is it possible to seperate out and read every 6 lines of data in the text file?

llkhoutx · Nov 7, 2014

I'd import all rows and then parse them with DAO or ADO VBA, creating and appending a target table.

smig · Nov 7, 2014

You can import a text file.
It can be delimited (you need to know the delimiter) or having fixed size, which will require you to know the size of every field

namliam · Nov 7, 2014

Have a look at this thread, where I lead another member on this forum more or less step by step on how to read these kind of files:
http://www.access-programmers.co.uk/forums/showthread.php?t=270362&highlight=namliam

shawnntjr · Nov 7, 2014

Thanks for all your input guys, I'll try each solution out and see if I get anywhere.

Anyway, anyone knows how to extract the actual timestamp out from epoch time? The timestamp in my txt file is listed as epoch, but I'm required to turn it into human time.

namliam · Nov 7, 2014

from wiki

The current standard epoch is called "J2000.0" (and is approximately noon January 1, 2000, Gregorian calendar, at the Royal Observatory, Greenwich, in London, England). This is equivalent to:

1.The Julian date 2451545.0 TT (Terrestrial Time).[6]
2.January 1, 2000, 11:59:27.816 TAI (International Atomic Time).[7] or
3.January 1, 2000, 11:58:55.816 UTC (Coordinated Universal Time).
When dates or times are expressed as years with a decimal fraction from J2000, the years are of exactly 365.25 days, which is the average length of a year in the Julian calendar.

Depending on your required accuracy you should simply be able to calculate it thus
#1/1/2000 11:59:27# + EpochTime

Which seems simple enough

shawnntjr · Nov 10, 2014

Hi all, I've tried several methods and I can't seem to do it. I think the problem is that my text file has no delmiters or fixed widths. Is it possible to do it without these? Maybe through VBA codes or something?

This is a sample of 1 record in the text file:

Code:

ES~1~1234567890(Oct 02 00:00:00 2014)~1~ITM_XX4XXXXXXXXXXX00~
ITM~X01XXXXXXXX1X:X6400~12.34.56.78~~X01XXXXXXXX1X~X01XXXXXXXX1X~ACK~
XXXXXXXXXX~[ admin]~WARNING~
10/01/2014~
XXX_XXX64_XXXXXXXXXXXX[(STRSCAN(Logline, "CURDEPTH") = 1 ) OR ( ST=1 OR STRSCAN(Logline, "STATUS") = 1 ) ) ;W642=1 ) ON X01XXXXXXXX1X:X6400 (Logline=   CURDEPTH(4642) )]~
XXXX_XXXXX~0~

1. ES marks the start of a new record.
2. I need to capture:
- the date (located on the first line).
- the admin warning (located on the third line).
- the details (the entire fifth line).
- the event name (located on the sixth line).

llkhoutx · Nov 10, 2014

I gave you a DAO/ADO solution. If you dn't know how to use either, .....

shawnntjr · Nov 10, 2014

Is there a way to tell access that every 6 lines of txt = 1 record?

JHB · Nov 10, 2014

Create a form place a button on it and in the click event paste in the below code.
Create a table with 6 fields, (here called "InsertedTable").
"DataTableFrom" is your tablename where you've the raw-data.

Code:

  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![FieldName1] = rst![Id]
      ElseIf x Mod 6 = 1 Then
        rstInsert![FieldName2] = rst![Id]
      ElseIf x Mod 6 = 2 Then
        rstInsert![FieldName3] = rst![Id]
      ElseIf x Mod 6 = 3 Then
        rstInsert![FieldName4] = rst![Id]
      ElseIf x Mod 6 = 4 Then
        rstInsert![FieldName5] = rst![Id]
      ElseIf x Mod 6 = 5 Then
        rstInsert![FieldName6] = rst![Id]
        rstInsert.Update
      End If
      x = x + 1
      rst.MoveNext
    Loop Until rst.EOF
  End If

namliam · Nov 10, 2014

The ~ seems to act as a delimiter to me?
Also ES marking a new record

The link I gave you should be able to get you started, or use JHB's sample as a starting point.

shawnntjr · Nov 10, 2014

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)
)]~

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?

namliam · Nov 10, 2014

Post your current code if you want us to find a problem with it...

JHBs sample does the counting already I think?

Also you could "simply" code around the problem, if the bracket isnt there assume it is on the next line...

shawnntjr · Nov 10, 2014

It's not the problem of the codes. I'm using exactly the same one as the one JHB provided. It works perfectly.

The problem lies with the data. I overlooked the fact that the there may be some anomalies like:

The ")]~" on line 468 is actually supposed to be at the end on line 467.

When I populated the table line by line, instead of having "[(Timeout has expired. (Code 15) )]~" inside 1 field, it actually seperates into "[(Timeout has expired. (Code 15)" and ")]~".

JHB · Nov 10, 2014

Could you give some raw data samples, from the txt file and in txt file format?

shawnntjr · Nov 10, 2014

JHB said:
Could you give some raw data samples, from the txt file and in txt file format?

Here you go.

I put in 3 data samples. As you can see, the first 2 samples are 6 lines each. However, the last is 7 lines due to the ")]~" on line 18 being on a seperate line from line 17.

So, I'm trying to remove those events with additional lines, and just populate the table with those that contain only 6 lines.

Ps, there's like half a million lines of text, so I only extracted 3.

namliam · Nov 10, 2014

Problem is the Mod 6 breaks because it is no longer mod 6... all the time

Dont you want to read the faulty lines of information as well?

Also inkeeping with the thread I linked earlier, I wouldnt stage the text file inside the db, rather read the file directly.

Solving your problem though is probably as easy as adding

Code:

Do while Right(rst![Id],2) <> "]~" 
    rstInsert![Yourfield] = rstInsert![Yourfield] & rst![Id]
    rst.movenext
loop

to your code at the right line, this will read all subsequent lines intill it finds the proper line ending.

shawnntjr · Nov 10, 2014

I don't really need to read the faulty ones in. I'm fairly new to VBA, so I would feel more comfortable if I can work with the data from the tables as I'll be able to see it instead of directly from the source. But all in all, I'll choose the more efficient method if you really think playing directly with the data source is a better idea

namliam · Nov 10, 2014

Since you start with the addnew immediately, you will be left with a partial record if you dont read in the faulty/long ones.

How to import data from text files into access? (1 Viewer)

Registered User.

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

Registered User.

Registered User.

Have been here a while

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

Have been here a while

Registered User.

Attachments

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread