CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Thread: [RESOLVED] Filter a txt file based on an Excel data sheet

  1. #1
    Join Date
    Feb 2020
    Posts
    27

    [RESOLVED] Filter a txt file based on an Excel data sheet

    I would like to extend the functionality of the routine kindly developed by 2kaud - see: [RESOLVED] Filter a txt file based on a grid of data started by RIC63 user on 18 Feb 2020 - I need to add a further condition to the control loop, namely the following:

    the sum of the values vPar [8] + vPar [7] + vPar [6] should be included in the range identified by the fields BB7 (min) and BC7 (Max) -see the attached .xlsm file-

    I attach the code, (excel file and a sample txt file 'zip' via wetransfer, if the routine is launched on this last one as it is at present it produces an OutputFile.txt of 14.650 KB I am confident that after the code modification the starting txt file will be reduced considerably.
    The code was compiled using VisualStudio 2019 16.7.2

    I don't have the knowledge to be able to write the code by myself for this reason any help is appreciated

    thank youxxx_6_Vix_90_3_gruppi_di_15_Orizz_x_6_Cpp.txt

    https://we.tl/t-5F4oINsQf4

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

    Re: Filter a txt file based on an Excel data sheet

    the sum of the values vPar [8] + vPar [7] + vPar [6] should be included in the range identified by the fields BB7 (min) and BC7 (Max)
    This equates to BB7 <= (Y6 + Y7 + Y8) <= BC7 ??

    This doesn't involve any data read from the file and is purely reliant upon the filter spreadsheet. So either all existing filtered data is included or none of it is??????? which I don't think is what you mean??
    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++17 Compiler: Microsoft VS2019 (16.7.7)

  3. #3
    Join Date
    Feb 2020
    Posts
    27

    Re: Filter a txt file based on an Excel data sheet

    Hi 2kaud
    pleasure to hear from you

    (Y6 + Y7 + Y8) must be <= BC7 and at the same time >= BB7

    the data file 'ToPurgeFile.txt must be filtered by all filters specified in the Excel file included this new 'constraint' which I hope can be implemented,

    thanks 2kaud

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

    Re: Filter a txt file based on an Excel data sheet

    The new 'constraint' doesn't specify filtering criteria regarding the data from the file. You need to specify how this constraint is applied to the 6 values for each input file line.
    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++17 Compiler: Microsoft VS2019 (16.7.7)

  5. #5
    Join Date
    Feb 2020
    Posts
    27

    Re: Filter a txt file based on an Excel data sheet

    Hello 2kaud

    I was simply convinced that within the code you wrote to me it was possible to nest this control .. where a record of 6 numbers would be accepted and then inserted in the Output.txt file only if in addition to having met all the conditions of min and max values (vPar [n]) had also satisfied the control of compliance with the sum of the three values indicated (presences 0, 1 and 2).

    ....You need to specify how this constraint is applied to the 6 values for each input file line : in practice, the record of 6 values is accepted only if the sum of the three values specified in the excel file in rows 6, 7 and 8 column 25 is within the range specified by the two values present in row 7, columns 54 and 55.

    Thanks 2kaud for your patience

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

    Re: Filter a txt file based on an Excel data sheet

    This doesn't make much sense to me as a filter. If you want that criteria then either you're going to get no output or the current output. The result of the test BB7 <= (Y6 + Y7 + Y8) <= BC7 is independent of the data from the file and just depends entirely upon the spreadsheet as file data isn't involved. It's easy to add if that is really what you want??
    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++17 Compiler: Microsoft VS2019 (16.7.7)

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

    Re: Filter a txt file based on an Excel data sheet

    If that is really what you want, then consider:

    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>
    #include <fstream>
    
    using namespace Excel;
    using namespace std;
    
    const string sname = "c:\\myprogs\\xxx_6_Vix_90_3_gruppi_di_15_Orizz_x_6.xlsm";	// Complete path name to Excel filter file
    const string pname = "ToPurgeFile.txt";			// Input file name
    const string oname = "OutputFile.txt";			// Output file name (over written)
    
    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 spreadsheet file " << sname << endl), 3;
    	}
    
    	pXL->PutVisible(0, FALSE);
    
    	const _WorksheetPtr pWksheet{ pXL->ActiveSheet };
    	const RangePtr pRange{ pWksheet->Cells };
    
    	const int vSize{ 8 };	// Size of vmin/vmax
    	const int nRange{ 17 };	// Number of ranges
    	const int rRow{ 22 };	// Number of rows per range
    	const int rCol{ 3 };	// Number of cols per range
    	const int rcStart{ 8 };	// H for starting col of first range
    	const int rrStart{ 10 };	// Starting row for the ranges
    	const int nval{ 6 };	// Number of ints per line in file
    
    	int min {0};
    	int max {0};
    	int vMin[vSize]{ 0 };
    	int vMax[vSize]{ 0 };
    	int vPar[vSize]{ 0 };
    	int rData[nRange][rRow][rCol]{ 0 };	// Contains the data for the various required ranges from the spreadsheet
    
    	bool bad{ false };	// Has data read failed
    
    	try {
    
    		min = (int)pRange->Item[7][54];	// BB7
    		max = (int)pRange->Item[7][55];	// BC7
    
    		for (int r = 1; r <= 8; ++r) {	// Rows 1 to 8
    			vMin[r - 1] = (int)pRange->Item[r][48];	// AV col
    			vPar[r - 1] = (int)pRange->Item[r][25];	// Y col
    			vMax[r - 1] = (int)pRange->Item[r][50];	// AX col
    		}
    
    		// Obtain the range data
    		for (int r{ 0 }; r < nRange; ++r)
    			for (int cl{ 0 }; cl < rCol; ++cl)
    				for (int rw{ 0 }; rw < rRow; ++rw)
    					rData[r][rw][cl] = (int)pRange->Item[rrStart + rw][rcStart + (r * rCol) + cl];
    	}
    	catch (...) {
    		bad = true;
    	}
    
    	pWksheet->Release();
    	pBook->Release();
    	pXL->Quit();
    
    	if (bad)
    		return (cout << "Cannot read the range data!" << endl), 4;
    
    	// BB7 <= (Y6 + Y7 + Y8) <= BC7 OK, otherwise exit
    	if (const auto ysum = vPar[5] + vPar[6] + vPar[7]; (ysum < min) || (ysum > max))
    		return (cout << "Out of range. No filtering applied!" << endl), 7;
    
    	ifstream ifs(pname);
    
    	if (!ifs.is_open())
    		return (cout << "Cannot open input file " << pname << endl), 5;
    
    	ofstream ofs(oname);
    
    	if (!ofs.is_open())
    		return (cout << "Cannot open output file " << oname << endl), 6;
    
    	int buf[nval]{ 0 };	// One line of int data
    
    	// Try to read first number on line. If this fails, at EOF
    	while (ifs >> buf[0]) {
    		int cntl[vSize]{ 0 };
    
    		// Read rest of line of int data
    		for (int v{ 1 }; v < nval; ++v)
    			ifs >> buf[v];
    
    		for (int i{ 0 }; i < nRange; ++i) {
    			int cnt2{ 0 };
    
    			for (int j{ 0 }; j < nval; ++j)
    				// cnt2 = cnt2 + countif()
    				for (int rw{ 0 }; rw < rRow; ++rw)
    					for (int cl{ 0 }; cl < rCol; ++cl)
    						if (rData[i][rw][cl] == buf[j])
    							++cnt2;
    
    			// Select Case
    			for (int v{ 0 }; v < vSize; ++v)
    				if (vPar[v] == cnt2) {
    					++cntl[v];
    					break;
    				}
    		}
    
    		bool out{ true };
    
    		// Compound if statement
    		for (int v{ 0 }; v < vSize; ++v)
    			if ((cntl[v] < vMin[v]) || (cntl[v] > vMax[v])) {
    				out = false;
    				break;
    			}
    
    		// Output line of data
    		if (out)
    			for (int d{ 0 }; d < nval; ++d)
    				ofs << buf[d] << ((d == (nval - 1)) ? '\n' : ' ');
    	}
    
    	ifs.close();
    	ofs.close();
    }
    [check file locations]
    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++17 Compiler: Microsoft VS2019 (16.7.7)

  8. #8
    Join Date
    Feb 2020
    Posts
    27

    Re: Filter a txt file based on an Excel data sheet

    Hello 2kaud

    Surely I explained myself badly, the purpose is not to exit the routine if the sum of the three values is not in the range but to filter the input file as usual.
    To clarify I am attaching a ToPurgeFile.txt of 5000 lines (80KB) where I have added the last 12 lines to explain myself better. This file submitted to the original routine (xxx_6_Vix_90_3_gruppi_di_15_Orizz_x_6.exe attached to the opening of the thread) is reduced to the OutputFile of 4827 lines (77KB) where my 12 added lines are always present.
    Now if you take a look at the pdf file - where I used as a test one of the 12 lines I inserted in the input file - I tried graphically through the 3 highlighted points to explain what I really want.

    I hope I have explained
    in the meantime, thank you again
    ToPurgeFile.txtxxx_6_Vix_90_3_gruppi_di_15_Orizz_x_6.pdf

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

    Re: Filter a txt file based on an Excel data sheet

    Sorry, but I still don't know what processing is required. Previously, I just converted the VB code to C++ - I didn't understand the algorithms used/what the code did! This time there's no VB code as the provided spreadsheet doesn't have the updated code. If you can get the VB code updated, I'll certainly provide a conversion to C++.

    Consider this. You have a new colleague. They know nothing about what is done. You have to explain to them in detail how to filter the file using the numbers on the spreadsheet without using a computer using just a pen and paper. What exact instructions would you provide?
    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++17 Compiler: Microsoft VS2019 (16.7.7)

  10. #10
    Join Date
    Feb 2020
    Posts
    27

    Re: Filter a txt file based on an Excel data sheet

    Morning,

    attached you can find the Vb code as I modified it for the provided spreadsheet
    Hope it will help

    thank you
    Attached Images Attached Images

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

    Re: Filter a txt file based on an Excel data sheet

    Ok. No problem. This is the updated code. Check the file locations.

    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>
    #include <fstream>
    
    using namespace Excel;
    using namespace std;
    
    const string sname = "c:\\myprogs\\xxx_6_Vix_90_3_gruppi_di_15_Orizz_x_6.xlsm";	// Complete path name to Excel filter file
    const string pname = "ToPurgeFile.txt";			// Input file name
    const string oname = "OutputFile.txt";			// Output file name (over written)
    
    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 spreadsheet file " << sname << endl), 3;
    	}
    
    	pXL->PutVisible(0, FALSE);
    
    	const _WorksheetPtr pWksheet{ pXL->ActiveSheet };
    	const RangePtr pRange{ pWksheet->Cells };
    
    	const int vSize{ 8 };	// Size of vmin/vmax
    	const int nRange{ 17 };	// Number of ranges
    	const int rRow{ 22 };	// Number of rows per range
    	const int rCol{ 3 };	// Number of cols per range
    	const int rcStart{ 8 };	// H for starting col of first range
    	const int rrStart{ 10 };	// Starting row for the ranges
    	const int nval{ 6 };	// Number of ints per line in file
    
    	int sMin1 {0};
    	int sMax1 {0};
    	int vMin[vSize]{ 0 };
    	int vMax[vSize]{ 0 };
    	int vPar[vSize]{ 0 };
    	int rData[nRange][rRow][rCol]{ 0 };	// Contains the data for the various required ranges from the spreadsheet
    
    	bool bad{ false };	// Has data read failed
    
    	try {
    
    		sMin1 = (int)pRange->Item[7][54];	// BB7
    		sMax1 = (int)pRange->Item[7][55];	// BC7
    
    		for (int r = 1; r <= 8; ++r) {	// Rows 1 to 8
    			vMin[r - 1] = (int)pRange->Item[r][48];	// AV col
    			vPar[r - 1] = (int)pRange->Item[r][25];	// Y col
    			vMax[r - 1] = (int)pRange->Item[r][50];	// AX col
    		}
    
    		// Obtain the range data
    		for (int r{ 0 }; r < nRange; ++r)
    			for (int cl{ 0 }; cl < rCol; ++cl)
    				for (int rw{ 0 }; rw < rRow; ++rw)
    					rData[r][rw][cl] = (int)pRange->Item[rrStart + rw][rcStart + (r * rCol) + cl];
    	}
    	catch (...) {
    		bad = true;
    	}
    
    	pWksheet->Release();
    	pBook->Release();
    	pXL->Quit();
    
    	if (bad)
    		return (cout << "Cannot read the range data!" << endl), 4;
    
    	ifstream ifs(pname);
    
    	if (!ifs.is_open())
    		return (cout << "Cannot open input file " << pname << endl), 5;
    
    	ofstream ofs(oname);
    
    	if (!ofs.is_open())
    		return (cout << "Cannot open output file " << oname << endl), 6;
    
    	int buf[nval]{ 0 };	// One line of int data
    
    	// Try to read first number on line. If this fails, at EOF
    	while (ifs >> buf[0]) {
    		int cntl[vSize]{ 0 };
    
    		// Read rest of line of int data
    		for (int v{ 1 }; v < nval; ++v)
    			ifs >> buf[v];
    
    		for (int i{ 0 }; i < nRange; ++i) {
    			int cnt2{ 0 };
    
    			for (int j{ 0 }; j < nval; ++j)
    				// cnt2 = cnt2 + countif()
    				for (int rw{ 0 }; rw < rRow; ++rw)
    					for (int cl{ 0 }; cl < rCol; ++cl)
    						if (rData[i][rw][cl] == buf[j])
    							++cnt2;
    
    			// Select Case
    			for (int v{ 0 }; v < vSize; ++v)
    				if (vPar[v] == cnt2) {
    					++cntl[v];
    					break;
    				}
    		}
    
    		bool out{ true };
    
    		// Compound if statement
    		for (int v{ 0 }; v < vSize; ++v)
    			if ((cntl[v] < vMin[v]) || (cntl[v] > vMax[v])) {
    				out = false;
    				break;
    			}
    
    		// Output line of data
    		if (out)
    			// New filter test
    			if (const auto tot = cntl[5] + cntl[6] + cntl[7]; (tot >= sMin1) && (tot <= sMax1))
    				for (int d{ 0 }; d < nval; ++d)
    					ofs << buf[d] << ((d == (nval - 1)) ? '\n' : ' ');
    	}
    
    	ifs.close();
    	ofs.close();
    }
    It reduces the output file from 14.3Mb to 12.2Mb
    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++17 Compiler: Microsoft VS2019 (16.7.7)

  12. #12
    Join Date
    Feb 2020
    Posts
    27

    Re: Filter a txt file based on an Excel data sheet

    ok

    thank you so much

  13. #13
    Join Date
    Feb 2020
    Posts
    27

    Re: Filter a txt file based on an Excel data sheet

    Sorry 2kaud

    i receive some errors and the exe is not produced, in the attached errors list do you notice somewhat helpful to solve the problem ?
    thank you
    Attached Images Attached Images

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

    Re: Filter a txt file based on an Excel data sheet

    What version of VS are you using? If VS2019, what is project/properties/C++ Language Standard set to? It should be at least C++17.

    That section of the code could be re-written as:

    Code:
    // Output line of data
    		if (out) {
    			// New filter test
    			const auto tot = cntl[5] + cntl[6] + cntl[7];
    
    			if ((tot >= sMin1) && (tot <= sMax1))
    				for (int d {0}; d < nval; ++d)
    					ofs << buf[d] << ((d == (nval - 1)) ? '\n' : ' ');
    		}
    if you are using an earlier VS compiler.
    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++17 Compiler: Microsoft VS2019 (16.7.7)

  15. #15
    Join Date
    Feb 2020
    Posts
    27

    Re: Filter a txt file based on an Excel data sheet

    OK

    I have set the standard of the language to C++17 in the project properties and everything went ok without the need to further modify the code ;-)


    Thank You

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)