VBA code to import and format log file

Hi
Is it possible to code excel so that it will import the last X number of lines from the log file i have uploaded ?

I have renamed the log file to .txt and have also uploaded a screenshot of the original showing the encoding method.

The log file changes size frequently.

I dont really care what the output looks like providing just the words and numbers (no strange symbols) are within individual cells each time the code runs.

I have tried to achieve what i want by just importing the data from the original log file and also from the file changed to .txt using the various options available but I just can't get any order to it

thanks for looking

Thanks for taking the time with this,

That has sorted out which rows i want to look at, now is it possible for your code to get the layout more like this new upload so things maintain column structure and are contained within cells, without the unwanted symbols ?

so a cell containing ”‚ EMA spread would become just EMA spread and a cell containing ”‚ 0.09575415 would become just 0.09575415

That new upload btw is my attempt using import file function but as you see i have unwanted symbols inside data cells and no way to run the import constantly on a timed loop


I would like to add that when i run the code i would like it to continue working on a file extension of .log purely because i have many files that need to be loaded regularly and i dont want to be renaming all the time

thanks

If you have excel version 2010 or later (Your profile shows 2007), then you might try importing the file to Power Query and manipulating the data as necessary to meet your requirements. If you are running a later version of Excel than 2007, please update your profile accordingly.

Hello semajjames,

The attached workbook should be very close to what you want. I can tweak it as needed. I ran this on the example log file you posted.

Module1 Macro Code

Please Login or Register  to view this content.

Hello semajjames,


Do you need all the punctuation lines i.e. lines like *************, +_____________+, etc intact?

Are you saying you require only the last entry of the log file to be imported to the worksheet? If this is the case there is no need for you to input the number lines. The macro can isolate that section for you.

I can include a hard coded path for the log file. You can either tell me what the path is or edit the code yourself.

Originally Posted by semajjames

... i am only interested in the last cycle at the end at any time (the cycle at the bottom )...

1. If you do not have many log files to process, then you only need to manually import data from the "import from text" option with 65001 Unicode (utf-8) encoding. In the import window you can specify from which rows the data should be imported.

2. However, if you need all "text" from file (including the tables from the log file), then you need to examine unicode characters from 9472 to 9551 - the table frames in the log are built of them:

Table frame => ChrW range from 9474/9484 to 9547 (the full range: from 9472 to 9551):
"-¦¦"..."¦T¦"..."¦¦¬" => 9484 9472 9472 ... 9472 9516 9472 ... 9472 9472 9488
"+¦¦"..."¦+¦"..."¦¦+" => 9500 9472 9472 ... 9472 9532 9472 ... 9472 9472 9508
"L¦¦"..."¦+¦"..."¦¦-" => 9492 9472 9472 ... 9472 9524 9472 ... 9472 9472 9496

The modified Leith Ross code will work very well for that.

Hello semajjames,

This version uses a fixed file path. The macro code has been changed to provide a "clean" output by dividing the text at natural breaks. It starts at cell "A1" on the "Sheet1". This can be changed to start on the next empty row of the worksheet if you like. Let me know and I will will amend the code.

Module1 - Updated Macro Code

Please Login or Register  to view this content.

Hello samejjames,

Is the complete cycle from "?????? RT Record" to the line below "Profit/Loss" ?

Will this record be copied to the next empty row on the worksheet?

Will the files all be located in the same folder? Is so, what is the folder path and what do the file names look like?

Originally Posted by Leith Ross

Hello samejjames,

Is the complete cycle from "?????? RT Record" to the line below "Profit/Loss" ?

in this case the complete cycle is from

GBRT_16.5.2_RC USDT-DASH@poloniex Round #2652 2020/02/06 15:59:04
to
All good...keep calm and Gunbot.



but this can change with each different .log file tho the basic format is the same just some lines could be omitted from the file which is why i kind of need to be able to define the amount of lines

the files will all be in the same folder and the names will look like this:- poloniex.USDT-DASH.log

ok so the worksheet ,,, the code can first "clear the worksheet" then copy to the worksheet.
I guess to save having a separate worksheet for each log file the first instance of the code could start at A:1 then additional instances could use the next available column's along defined by user ?

Hello semajjames,

I have made some changes to the import macro and the workbook. The output now appears on "Sheet2" while the button to run the macro is on "Sheet1".

Each time the macro runs "Sheet2" is cleared. Each file will be separated from the next file by an empty column.

The folder path is fixed. You will need to edit the macro to point to the folder you will be using. The macro will examine the files in the folder and only import those that match the pattern "*.DASH.log". Case is ignored.

As for your request about the specifying lines, I have not approached this problem. Since each line is cleaned before it is output, the number of lines in the original log file will not match up with the cleaned lines output. Taking a guess at how many you need to display seems like more work. Perhaps if I were involved in day trading cryptos, I would better understand what you doing.

Anyway, look it over and let me know what you think.

Please Login or Register  to view this content.

Hi Leith Ross

I have added the folder path in one place as follows

Please Login or Register  to view this content.

the folder contains the files

poloniex.USDT-BCHSV.log
poloniex.USDT-DASH.log
poloniex.USDT-ETC.log
poloniex.USDT-LTC.log
poloniex.USDT-TRX.log

unfortunately tho when i run the macro i get the error shown in attached screen shots

I have also tried with just this one file in the folder

DASH.log

I have tried changing code below by removing the "*DASH" and just leaving ".log" When I do that the code then runs but does not copy to sheet.

Please Login or Register  to view this content.

I now get your point about "the number of lines" where by having to import before clean ,,,

The log files come a running piece of software with no user GUI, I am or rather you are kindly trying to make a simple GUI for me,
At present time the files contain 200000 lines each and update. I am only interested in the most recent cycle at any one time but the number of lines the cycles consist of changes ! So could we just say read and clean the LAST 300 lines ? this will cover all possible cycle sizes.

I am trying to save excel importing then cleaning 200000+ lines x 8 when they are not needed.

thanks again for your patience

Hello semajjames,

Okay, that was my mistake. Delete the macro ImportLogFile and replace it with this amended macro code. The first problem was with the file name. I was looking for DASH in the name instead of poloniex. The second problem was the folder path was not included with the file name. I don't use Dir that much and often forget it only returns the file name and not the fully qualified path.

I will need to make some more changes to extract the last 300 lines. Before I do, I want to know this code is working correctly.

Please Login or Register  to view this content.