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

[RIC63]

Hi
just one last question
Is it possible to specify in the code that it searches for the input file from the folder from which the exe is started instead of specifying the Complete path name to Excel filter file (sname)?

thank you

[2kaud]

Of course you can! Just do:

const string sname = "xxx_6_Vix_90_3_gruppi_di_15_Orizz_x_6.xlsm";

This will try to open the file in the folder from which exe is started. If an absolute (complete) path is not specified, then the file/path used is relative to the folder from which the exe is started.

Also, if you are using the same program with different files, then the program can be modified to ask for the file name or to allow the name to be specified together with the program name on the command line. In this case, though, I would choose shorter file names! Let me know if you want this option.

[RIC63]

Perfect ;-)

thanks again

[RIC63]

Hello 2kaud

Do you think it is also possible to specify the file to use as input through a classic selection window ... so that I can choose between multiple folders and different files?

it would be really useful
thank you

[2kaud]

Windows WIN32 APIs provide a control for selecting files via a window - although I haven't programmed with WIN32 for many, many years, I'll see what I can do.

There is also MFC which is geared to Windows GUI - but I don't use MFC.

[2kaud]

Do you mean the input file name - or the xlsm filter file name?

[2kaud]

To to be able to choose the input file (file to purge), consider for the last program from post #11:

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

#include "windows.h"

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)

const int MAXFILE = 256;

bool OpenFile(std::string& filnam)
{
	OPENFILENAME	ofn = {0};
	char			file[MAXFILE] = {0};

	filnam.clear();

	ofn.lStructSize = sizeof(ofn);
	ofn.hwndOwner = NULL;
	ofn.lpstrFile = file;
	ofn.nMaxFile = sizeof(file) - 1;
	ofn.lpstrFilter = "Input file name (*.txt)\0*.txt\0All files (*.*)\0*.*\0";
	ofn.nFilterIndex = 1;
	ofn.lpstrTitle = "Open Input file";
	ofn.Flags = OFN_PATHMUSTEXIST | OFN_FILEMUSTEXIST | OFN_READONLY;

	if (!GetOpenFileName(&ofn)) {
		if (const auto ret = CommDlgExtendedError(); ret)
			MessageBox(NULL, "Cannot open file", "Error with open file dialogue", MB_OK | MB_ICONERROR);

		return false;
	}

	filnam = ofn.lpstrFile;
	return true;
}

int main()
{
	std::string newfile;

	if (!OpenFile(newfile))
		return (std::cout << "Cancelled" << std::endl), 4;

	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);
	ifstream ifs(newfile);

	if (!ifs.is_open())
		return (cout << "Cannot open input file " << newfile << 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
			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' : ' ');
		}
	}

	ifs.close();
	ofs.close();
}

[RIC63]

Hello 2kaud

thanks for the last variant of the code, I take this opportunity to show you a problem I have with one of the codes you sent me and that I tried to modify because in a particular situation it did not return the expected values.

In practice, it is the usual type of control only that the records of the ToPurgeFile file that must be validated according to the conditions as well as a total min and max (AY7 / AZ7) must simultaneously respect the min and max (AV4 / AX4 for the 6 groups (columns), T ÷ Y) (AV6 / AX6 for the 6 groups (columns) Z ÷ AE) and (AV8 / AX8 for the 6 groups (columns) AF ÷ AK)

I believe that the check referred to the three groups of 6 columns is done on line 125 of the code, that is:

for (int i {n * 6 + 1}, ie {n * 6 + 6}; i <= ie; ++ i) {// Check <= what's with 4, 2, 5 ??

but in the output file I do not find validated the record made by the 6 numbers 16 17 38 39 68 84 which I used as an example in the xlsm file (2 zeros in the first six columns + 3 zeros in the second group of 6 columns + 3 zeros in the third group of six columns equal to a total of 8 zeros...and also other records present in the OutputFile seem not to respect the conditions

I can't understand where the problem is, can you take a look?
Link to download 3 samples files : https://we.tl/t-kv9J15tgUX


thank you

[2kaud]

Is the original provided vba code working as expected? If yes, please attach the original .xlsm vba code that I used for the conversion, and the converted C++ code and I'll have a look. If the original vba code also has the issue, then if you change that so that it works as needed and attach it, I'll update the C++ code.

[RIC63]

After a lot of attention to the various pieces of code you sent me ... with no small effort ... I changed the string responsible for the incorrectly filtered data .. :-)
thank you for all the support you have given me and for your patience

[2kaud]