-
February 18th, 2020, 09:46 AM
#1
[RESOLVED] Filter a txt file based on a grid of data
good morning
I need to filter a text file that can be composed of a few lines (100, 200) as well as many (even 4/5 million = about 90,000 Kb) and each line is composed of 6 numbers.
Now to do this I use a routine in Vb within Excel but to do this it can take an hour, I state that I am not a programmer and I can barely interpret the Vb code of the excel routine that friends have written to me
I wanted to know if it is possible to create the same routine with a graphical form where I can paste the data set to be used for filtering the text file from time to time and possibly be able to compile different versions for differentiated filtering, hoping that with a C program compiled the times of analysis are much lower.
i appreciate any suggestion or sample
Attachment 35783Attachment 35785
-
February 19th, 2020, 05:14 AM
#2
Re: Filter a txt file based on a grid of data
If you want to apply some filtering to an input text file to produce an output text file in a required format that can then be imported into Excel then that isn't too difficult. If you provide details of the required filtering and the format of the output text file then I'll see what I can provide in c++ easily.
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
February 19th, 2020, 08:27 AM
#3
Re: Filter a txt file based on a grid of data
Hi 2kaud
first of all thank you for your support and courtesy
I noticed that with the low quality images that I have attached to the thread it is difficult to actually understand what I would like ... I try to fix a link for download two example Excel files where you can possibly investigate better.
also I try to explain myself:
The input file - see the ToPurgeFile.txt file (made of 548 records) that I have attached to my thread - is a simple text file where each record is made up of 6 numbers separated by a space the output file must have the same format with removed records that do not comply with the conditions imposed on the form.
The form should consist of 17 areas each consisting of 3 columns by 15 rows where you can paste - do not insert one by one - groups of numbers that I have in Excel so the routine should check that each row of 6 numbers is validated and therefore is present in the output file, respect the min and max imposed presences.
E.g. the attached file consisting of 548 records according to the conditions imposed in the Level_1.xlsm file - how many groups (3 with x 15 lines) with zero numbers must satisfy the 548 lines? from min 0 to max 2 for the 4 groups highlighted in yellow, min 0 to max 2 for the 4 groups highlighted in green and the same for the blue in any case a total of 3 simultaneous groups with zero prezenze is required will generate an OutPutFile of just 2 rows. This is the first level of analysis that I have to do on a data set, a second level -similar to this- involves a different intersection of conditions - see the example file Level_2.xlsm.
Note: ToPurgeFile.txt file to be used in test with Level_1.xlsm must be in the same folder and renamed to ToPurgeFile.csv
I hope I have been clear and that you can help me out
thank you
regards
RIC63https://we.tl/t-tvg3CI7gJ0
-
February 19th, 2020, 11:56 AM
#4
Re: Filter a txt file based on a grid of data
is a simple text file where each record is made up of 6 numbers separated by a space the output file must have the same format with removed records that do not comply with the conditions.
If you can specify the requirement for the output file based upon the input file without any reference to Excel, then I'll see what I can do to provide a C++ program that takes an input file, processes the data and produces an output file. You mention highlighted groups etc in Excel. I can't assist with Excel or Excel programming (VBA etc).
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
February 20th, 2020, 04:20 AM
#5
Re: Filter a txt file based on a grid of data
morning 2kaud
I am attaching the image 'Level_2.pdf' with some comments in the hope that it will help to understand my request
Thanks for your patience
regards
RIC63
-
February 20th, 2020, 06:23 AM
#6
Re: Filter a txt file based on a grid of data
You have a populated spreadsheet an example as shown in the image in post #5. You have an input text file containing lines of numbers and you want to filter these lines to produce an output file based upon data contained in the spreadsheet - yes?
This is not just a simple case of data manipulation. To do this in C++ outside of Excel is not a simple task and unfortunately I won't be able to assist further.
This task would probably be easier in C# (or vb.net) using .net and the Excel class etc. Perhaps some guru who knows c#/vb.net may be able to assist further?
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
February 20th, 2020, 08:09 AM
#7
Re: Filter a txt file based on a grid of data
Hi 2kaud
yes you are right
is exactly what i would like to do
however, I appreciate your attempt to help me
thanks
greetings
RIC63
-
February 20th, 2020, 08:30 AM
#8
Re: Filter a txt file based on a grid of data
A different approach to this problem would be to save the spreadsheet with the required filtering data as a separate csv file (text filter file). Then you have the requirement of producing a text output file from the input text file based upon criteria from the filter text file. This is certainly doable in C++ and should be quite fast.
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
February 20th, 2020, 08:52 AM
#9
Re: Filter a txt file based on a grid of data
Update. I've found some C++ code that enables data from an .xls spreadsheet to be read. Can you send me a copy of the .xls spreadsheet to use as a filter and I'll see if I can easily obtain values from it. If I can, then I should be able to convert the provided VBA code in post #1 into a standalone C++ program.
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
February 20th, 2020, 10:28 AM
#10
Re: Filter a txt file based on a grid of data
good news :-)
at the end of post # 3 there is a link to download both xlsm files
xls..m means it is a file that contains vb code 'a macro'
See you soon
RIC63
-
February 20th, 2020, 12:04 PM
#11
Re: Filter a txt file based on a grid of data
Ok. I can now read the data in the .xlsm files in post #3 in a C++ program.
What is level_1.xlsm and level_2.xlsm? Which one is supposed to be used for the filter? Given this .xlsm file and the ToPurgeFile.txt in post #1 as an example, what would be the expected content of the output file using the only_zero vb code from post #1?
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
February 20th, 2020, 02:56 PM
#12
Re: Filter a txt file based on a grid of data
What is level_1.xlsm and level_2.xlsm?
They represent two levels of filtering with different conditions, normally I use the output from Level_1 as input for Level_2. I put both for example, I am confident that if I can have the code for one then I can study it a bit to create the code for the second one by myself ... I hope
Which one is supposed to be used for the filter?
It is indifferent .. I would to say to start with Level_1
Given this .xlsm file and the ToPurgeFile.txt in post #1 as an example, what would be the expected content of the output file using the only_zero vb code from post #1?
The result will be that of the 578 lines present in ToPurgeFile.txt only 44 will be validated therefore present in the output file.
-
February 21st, 2020, 06:50 AM
#13
Re: Filter a txt file based on a grid of data
Yes - but can you provide the file with the expected 44 lines so that I can check the code is working properly.
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
February 21st, 2020, 07:19 AM
#14
Re: Filter a txt file based on a grid of data
This is some 'proof of concept' C++ code. It should display the values in row 10 of the level_1.xlsm file. I used MS VS2019. You may need to change the file names/locations in the #import lines 1, 6 & 8 as I use Office 2010. Also, line 22 (sname) states the full path name for the level_1 file.
Before you can progress much further with this, you need to be able to compile (ignore the 2 warnings for line 9) this code and produce an .exe file which when run from the console shows the data in row 10 which on the version of this file I have displays:
Code:
1 16 31 16 31 46 31 46 61 46 61 76 69 31 51 31 51 90 51 90 2 90 2 6 79 6 63 6 63 8 63 8 48 8 48 50
Code:
#import "c:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE14\\mso.dll" \
rename( "RGB", "MSORGB" ) \
rename( "DocumentProperties", "MSDocument") \
rename( "SearchPath", "MSSearch")
#import "c:\\Program Files (x86)\\Common Files\\microsoft shared\\VBA\\VBA6\\VBE6EXT.OLB"
#import "c:\\Program Files (x86)\Microsoft Office\\Office14\\excel.exe" \
rename( "DialogBox", "ExcelDialogBox" ) \
rename( "RGB", "ExcelRGB" ) \
rename( "CopyFile", "ExcelCopyFile" ) \
rename( "ReplaceText", "ExcelReplaceText" )
#include <iostream>
#include <iomanip>
#include <string>
using namespace Excel;
using namespace std;
const string sname = "c:\\MyProgs\\Level_1.xlsm";
int main()
{
const auto hr = CoInitializeEx(0, COINIT_MULTITHREADED);
if (FAILED(hr))
return (cout << "Failed to initialize COM library. Error code = 0x" << hex << hr << endl) , 1;
_ApplicationPtr pXL;
if (FAILED(pXL.CreateInstance("Excel.Application")))
return (cout << "Failed to initialize Excel::_Application!" << endl) ,2;
_WorkbookPtr pBook;
try {
pBook = pXL->Workbooks->Open(sname.c_str());
}
catch (...) {
pXL->Quit();
return (cout << "Cannot open file" << endl), 3;
}
pXL->PutVisible(0, FALSE);
const _WorksheetPtr pWksheet = pXL->ActiveSheet;
const RangePtr pRange = pWksheet->Cells;
try {
for (int c = 14; c < 50; ++c)
cout << (int)pRange->Item[10][c] << " "; // row column
}
catch (...) {
cout << "Cannot read the data!" << endl;
}
pWksheet->Release();
pBook->Release();
pXL->Quit();
}
Last edited by 2kaud; February 21st, 2020 at 07:21 AM.
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
February 21st, 2020, 09:03 AM
#15
Re: Filter a txt file based on a grid of data
Hi 2kaud
attached you can find the OutPutFile.csv that is the result of applied filters (contain the expected 44 lines )
Thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|