How to import data from text files into access?

S

shawnntjr

Registered User.
Local time
Yesterday, 21:43
Joined
Oct 28, 2014
Messages
42
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?
 
Last edited:
I'd import all rows and then parse them with DAO or ADO VBA, creating and appending a target table.
 
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
 
Last edited:
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.
 
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.
Click to expand...
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
 
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).
 
I gave you a DAO/ADO solution. If you dn't know how to use either, .....
 
Is there a way to tell access that every 6 lines of txt = 1 record?
 
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
 
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.
 
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?
 
Last edited:
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...
 
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 ")]~".
 
Could you give some raw data samples, from the txt file and in txt file format?
 
JHB said:
Could you give some raw data samples, from the txt file and in txt file format?
Click to expand...

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.
 

Attachments

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.
 
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
 
Since you start with the addnew immediately, you will be left with a partial record if you dont read in the faulty/long ones.
 
You must log in or register to reply here.

Similar threads

P
Import text file
Replies
5
Views
367
ebs17
E
P
Reading a long text file to insert rows in a table
Replies
8
Views
302
pacctono
P
B
Retrieve 6 numbers from string
Replies
2
Views
438
arnelgp
arnelgp
C
How to download a file with VBA
Replies
3
Views
267
CosmaL
C
E
open saved import external data vba code
2
Replies
23
Views
598
DickyP
DickyP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom