-
October 25th, 2022, 09:46 AM
#1
Filter a txt file based on status TRUE FALSE of specific Excel cells
The attached Excel file contains a VB macro which analyzes each record of an input file 'ToPurgeFile.csv' and writes to an output file 'OutPutFile.csv' the records for which all the conditions examined by the macro Vb 'PurgeFile2 'present in the xlsm file are TRUE.
..on my pc the Output file is produced after about 6 'and is equal to 1161KB
I need a console application that does the same thing to reduce parsing time. Can anyone help me in this regard?
The analysis and output files can also be text files with the 6 arguments present for each record separated by space.
link to download the sample files : https://we.tl/t-MG0wgIVnSe
thanks for any suggestion in this direction
-
October 25th, 2022, 10:45 AM
#2
Re: Filter a txt file based on status TRUE FALSE of specific Excel cells
Originally Posted by RIC63
Couldn't you attach this file to your post?
Victor Nijegorodov
-
October 26th, 2022, 03:58 AM
#3
Re: Filter a txt file based on status TRUE FALSE of specific Excel cells
The macro is basically one conditional statement which if true copies an input file line to the output file.
The conditional statement is an AND of the bool values of these cells:
AI48 - set true when other conditions apply
AI103 - set true when other conditions apply
AI160 - set true when other conditions apply
AI217 - set true when other conditions apply
AI272 - set true when other conditions apply
B16 B19 B112 B115
These do not appear to have any data set in these cells - or refer to other cells. Hence as far as I can see these bool values are always false irrespective of any data read from the file.
Hence the macro condition statement will always be false and the output file will not be created.
However, running the macro does produce a much reduced output file. So I'm missing something here! Does any one know what I'm missing as I'm not an Excel expert and I don't use/know VB?? Can someone explain why the macro conditional statement is sometimes true for a given file data line? I haven't really the time to keep investing this. Until that is understood, it's pointless trying to convert to C++.
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)
-
October 26th, 2022, 10:38 AM
#4
Re: Filter a txt file based on status TRUE FALSE of specific Excel cells
good morning VictorN
i received a message of 'invalidd file' when i tried to upload the .xlsm ...
any suggestion ?
thanks
-
October 27th, 2022, 01:31 AM
#5
Re: Filter a txt file based on status TRUE FALSE of specific Excel cells
Hello 2kaud
first of all thank you for your interest and disposition to help me
so I want to communicate the presence of an error in the VB routine highlighted thanks to your comments:
the references B16 B19 B112 and B115 must be BL6 BL9 BL12 and BL15 ... this error did not prevent the VB routine from moving forward precisely producing an outputfile of 1161KB, with the correct references for example by changing the value in BR16 to 5 the OutPutFile produced will be 885KB
lastly, I illustrate the logic of the controls on the Excel worksheet and related to the routine:
each record read from the ToPurgeFile file is 'buffered' in A2÷F2 and at the same time some formulas return the quantities of the 6 numbers of that record that are found in each group of numbers (6 groups for the first section, 6 groups for the second, 9 for the third, 6 for the fourth and again 6 for the fifth) .. see 'Found Values' on lines 48, 103, 160, 217 and 272
these sets of 'Found Values' are compared with the respective 3 records immediately below' Found Values' and in column AA the number of corresponding values found are written if they respect a min/Max shown in columns BB/BC
at the same time in column AB will be reported 'TRUE' (if is the case) and reported in column AD the 3 values whose sum will be shown in AD48/103/160/217/272 if these summation respect the min/Max indicated in columns BE/BF then AI48 AI103 AI160 AI217 and AI272 (controlled by the VB routine) will return 'TRUE'
similarly the cells BL6 BL9 BL12 and BL15 controlled by the routine will remain 'TRUE' if the summations:
in BJ6 will respect the min/Max imposed in BJ2/BK2,
...if the summation
in BJ9 BJ12 and BJ15 will respect a min/Max as set in BR/BS9 ... 12 ... 15
then summarizing -and rightly as you wrote you- each record of the input file (made of 6 numbers) will go into the output file only if all 9 conditions ( AI48/103/160/217/272 + BL6/9/12/15) will be 'TRUE'
-
October 27th, 2022, 10:09 AM
#6
Re: Filter a txt file based on status TRUE FALSE of specific Excel cells
Try this:
Code:
#pragma warning (disable : 4471)
#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 <string>
#include <fstream>
#include <sstream>
using namespace Excel;
using namespace std;
const string sname {"c:\\MyProgs\\codeguru\\xxx_5_Gruppi_Ripetizioni1_.xlsm"}; // Complete path name to Excel file
const string inpname { "c:\\MyProgs\\codeguru\\ToPurgeFile.csv" };
const string outname { "c:\\MyProgs\\codeguru\\test.csv" };
int main() {
ifstream ifs(inpname);
ofstream ofs(outname);
if (!ifs || !ofs)
return (cout << "Cannot open data files\n"), 1;
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 spreadsheet file " << sname << endl), 3;
}
pXL->PutVisible(0, FALSE);
const _WorksheetPtr pWksheet {pXL->ActiveSheet};
bool bad {};
pWksheet->EnableCalculation = true;
for (string line; getline(ifs, line); ) {
istringstream iss { line };
for (size_t e {}; e < 6; ++e) {
int v {};
char del;
iss >> v >> del;
pWksheet->Cells->Item[2][1 + e] = v;
}
//pXL->Calculate();
try {
const auto ai48 = (int)pWksheet->Cells->Item[48][35];
const auto ai103 = (int)pWksheet->Cells->Item[103][35];
const auto ai160 = (int)pWksheet->Cells->Item[160][35];
const auto ai217 = (int)pWksheet->Cells->Item[217][35];
const auto ai272 = (int)pWksheet->Cells->Item[272][35];
const auto bl6 = (int)pWksheet->Cells->Item[6][64];
const auto bl9 = (int)pWksheet->Cells->Item[9][64];
const auto bl12= (int)pWksheet->Cells->Item[12][64];
const auto bl15 = (int)pWksheet->Cells->Item[15][64];
//cout << ai48 << " " << ai103 << " " << ai160 << " " << ai217 << " " << ai272 <<
//" " << bl6 << " " << bl9 << " " << bl12 << " " << bl15 << '\n';
if (ai48 && ai103 && ai160 && ai217 && ai272 && bl6 && bl9 && bl12 && bl15)
ofs << line << '\n';
} catch (...) {
bad = true;
}
}
pWksheet->Release();
pBook->Release();
pXL->Quit();
if (bad)
return (cout << "Cannot read the data!" << endl), 4;
}
Although I don't think you'll see much speed improvement as the time taken to re-calculate the spreadsheet after every cell change is the major factor. For the given file of 300000, it's still running on my system after 3 hours!
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)
-
October 27th, 2022, 10:18 AM
#7
Re: Filter a txt file based on status TRUE FALSE of specific Excel cells
Hello 2kaud
thanks for the code anyway
actually I'm trying to find the solution that drastically decreases the analysis times, as it was for the other cases you solved for me
in any case I will try the time it will take on my pc and update you
thank you
Greetings
-
October 27th, 2022, 10:39 AM
#8
Re: Filter a txt file based on status TRUE FALSE of specific Excel cells
The others didn't involve updating spreadsheet cells!
If you can express the cells used in the condition (AI48, AI103, AI160, AI217, AI272, BL6, BL9, BL12, BL15) using just the cells A2:F2 (and any other data cell that doesn't depend upon A2:F2) then the required condition can be calculated without having to update the spreadsheet cells. This will massively decrease the time taken - as the required cell data for the conditions can be obtained just once and re-used for every file item and the read file data needn't be used to update the spreadsheet.
If you can obtain those formulas from the spreadsheet and post them here, I'll update the 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)
-
October 27th, 2022, 02:44 PM
#9
Re: Filter a txt file based on status TRUE FALSE of specific Excel cells
I really appreciate your comments useful to find a solution
I know all the formulas of the worksheet so I will try to identify a method to release certain dependencies, if I can I will post the formulas here and what is necessary for a continuation
thank you again
-
November 15th, 2022, 07:45 AM
#10
Re: Filter a txt file based on status TRUE FALSE of specific Excel cells
Select any cell within the range. Select Data > Filter. Select Text Filters or Number Filters, and then select a comparison, like Between. Enter the filter criteria and select OK.
-
November 22nd, 2022, 04:06 AM
#11
Re: Filter a txt file based on status TRUE FALSE of specific Excel cells
To show dynamic filtering mode on all supported lists by default, navigate to the More menu » Administration » Settings » General » Dynamic filters' settings » check "Show dynamic filters by default". guest survey Restaurant Survey
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
|