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

[RIC63]

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 youAttachment 35889

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

[2kaud]

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??

[RIC63]

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

[2kaud]

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.

[RIC63]

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

[2kaud]

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??

[2kaud]

If that is really what you want, then consider:

#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]

[RIC63]

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
Attachment 35890Attachment 35891

[2kaud]

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?

[RIC63]

Morning,

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

thank you

[2kaud]

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

#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

[RIC63]

ok

thank you so much

[RIC63]

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

[2kaud]

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:

// 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.

[RIC63]

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