473,325 Members | 2,816 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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

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:
Expand|Select|Wrap|Line Numbers
  1. ES~1~1412179200(Oct 02 00:00:00 2014)~1~ITM_W64MQMONITORLOG00~
  2. ITM~W01XXASPAXX1B:W6400~12.34.56.78~~W01XXASPAPP1B~W01XXASPAXX1B~ACK~
  3. Danny~[ admin]~WARNING~
  4. 10/01/2014~
  5. WAR_umW64_MQMOXXXORLOG[(STRSCAN(Logline, "CURDEPTH") = 1 ) OR ( ST=1 OR STRSCAN(Logline, "STATUS") = 1 ) ) ;W642=1 ) ON W01GGASXXXX1B:W6400 (Logline=   CURDEPTH(4642) )]~
  6. CORE_INFRA~0~
  7. 0~0~ES~1~1412179203(Oct 02 00:00:03 2014)~1~ITM_Log_Entries~
  8. ITM~a01gibestmtrep2a:KUL~12.34.56.78~~a01gibesxxxrep2a~a01gibxxxmtrep2a~ACK~
  9. Karthi~[ admin]~WARNING~
  10. 10/02/2014~
  11. 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 )]~
  12. 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).
Nov 10 '14 #1
9 2521
Seth Schrock
2,965 Expert 2GB
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.
Nov 10 '14 #2
jimatqsi
1,271 Expert 1GB
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.TransferText 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
Nov 10 '14 #3
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
Nov 10 '14 #4
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
Nov 10 '14 #5
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?
Nov 10 '14 #6
jimatqsi
1,271 Expert 1GB
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
Nov 10 '14 #7
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.     Dim dbs As DAO.Database, rst As DAO.Recordset, rstInsert As DAO.Recordset, x As Long
  3.  
  4.   Set dbs = CurrentDb
  5.   Set rst = dbs.OpenRecordset("DataTableFrom")
  6.   Set rstInsert = dbs.OpenRecordset("InsertedTable")
  7.   If Not rst.EOF Then
  8.     Do
  9.       If x Mod 6 = 0 Then
  10.         rstInsert.AddNew
  11.         rstInsert![Field1] = rst![Field1]
  12.       ElseIf x Mod 6 = 1 Then
  13.         rstInsert![Field2] = rst![Field1]
  14.       ElseIf x Mod 6 = 2 Then
  15.         rstInsert![Field3] = rst![Field1]
  16.       ElseIf x Mod 6 = 3 Then
  17.         rstInsert![Field4] = rst![Field1]
  18.       ElseIf x Mod 6 = 4 Then
  19.         rstInsert![Field5] = rst![Field1]
  20.       ElseIf x Mod 6 = 5 Then
  21.         rstInsert![Field6] = rst![Field1]
  22.         rstInsert.Update
  23.       End If
  24.       x = x + 1
  25.       rst.MoveNext
  26.     Loop Until rst.EOF
  27.   End If
  28. End Sub
  29.  
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.
Attached Files
File Type: txt Sample.txt (1.4 KB, 486 views)
Nov 11 '14 #8
twinnyfo
3,653 Expert Mod 2GB
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.
Nov 12 '14 #9
Thanks Twinny, I've a rough idea of how to do it already :) Hopefully it works!
Nov 13 '14 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: atse | last post by:
Hi, I am still stick on the text file being imported to database. Can anybody help? I have just done the csv format files. And then I work on text and DAT formats, but I have problem to import...
0
by: adrian GREEMAN | last post by:
When I try to import a text file with new data for an existing table I get the error "1148 - the used command is not allowed with this MySQL version." I have tried with both PHPMyAdmin2.3 and...
1
by: Rob Korsten | last post by:
I want to import frequently an excel format file in an access table. Is it possible to do this with a macro/module (VBA ?). I think it is for daily use more easy than with the standard import...
3
by: Frank Reichenbacher | last post by:
I've been developing an Access database for our small office for several years now so I am not a newbie. However, I have a need for which I am not finding a mechanism to accomplish in the material...
6
by: MLH | last post by:
I was able to do this from Access 2.0. It had to be set up, of course, but it could be done. I'm unsure as to why Access 97 says "Can't find file"??? ...
0
by: TJS | last post by:
I need to import a delimited text file to an msde table, I found discussion item below. anything available for msde which is comparable to this Jet example? =================================...
3
by: Dave G | last post by:
I will shortly be receiving data in the form of a text file, like this: id: 123456 first name: Fred surname: Bloggs age: 26 and so on, for about 60 fields. Each line ends with a carriage...
14
by: mfrsousa | last post by:
hi there, i have a huge large text file (350.000 lines) that i want to import to a MS Acccess Database, of course i don't want to use Access, but do it with C#. i already have tried the...
8
by: diasdaman | last post by:
I know how to import text files, but in this case I need to import a text file line by line on an on-the-fly basis, such that the Access will look at the first two digits of a line, and then import...
12
by: Miguel Valenzue | last post by:
I collect traffic data from a machine that outputs text files with the data. I want to import each text file as it's own table into an Access database and do it without having to run the import...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.