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).
9 2521
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? - Karthi
- [ admin]
- WARNING
- Karthi~[ admin]
I think that this won't be too hard once we get the information that we need.
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
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
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
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?
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
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.
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.
Thanks Twinny, I've a rough idea of how to do it already :) Hopefully it works!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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"???
...
|
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?
=================================...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |