CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2020
    Posts
    41

    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

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,301

    Re: Filter a txt file based on status TRUE FALSE of specific Excel cells

    Quote Originally Posted by RIC63 View Post
    link to download the sample files : https://we.tl/t-MG0wgIVnSe
    Couldn't you attach this file to your post?
    Victor Nijegorodov

  3. #3
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,688

    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.4.1)

  4. #4
    Join Date
    Feb 2020
    Posts
    41

    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

  5. #5
    Join Date
    Feb 2020
    Posts
    41

    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 A2F2 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'

  6. #6
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,688

    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.4.1)

  7. #7
    Join Date
    Feb 2020
    Posts
    41

    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

  8. #8
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,688

    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.4.1)

  9. #9
    Join Date
    Feb 2020
    Posts
    41

    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

  10. #10
    Join Date
    Nov 2022
    Posts
    2

    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.

  11. #11
    Join Date
    Nov 2022
    Posts
    2

    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
  •  





Click Here to Expand Forum to Full Width

Featured