CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Feb 2020
    Posts
    46

    [RESOLVED] Filter a txt file based on a grid of data

    good morning

    I need to filter a text file that can be composed of a few lines (100, 200) as well as many (even 4/5 million = about 90,000 Kb) and each line is composed of 6 numbers.
    Now to do this I use a routine in Vb within Excel but to do this it can take an hour, I state that I am not a programmer and I can barely interpret the Vb code of the excel routine that friends have written to me
    I wanted to know if it is possible to create the same routine with a graphical form where I can paste the data set to be used for filtering the text file from time to time and possibly be able to compile different versions for differentiated filtering, hoping that with a C program compiled the times of analysis are much lower.

    i appreciate any suggestion or sample
    Attachment 35783Attachment 35785

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

    Re: Filter a txt file based on a grid of data

    If you want to apply some filtering to an input text file to produce an output text file in a required format that can then be imported into Excel then that isn't too difficult. If you provide details of the required filtering and the format of the output text file then I'll see what I can provide in c++ easily.
    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)

  3. #3
    Join Date
    Feb 2020
    Posts
    46

    Re: Filter a txt file based on a grid of data

    Hi 2kaud
    first of all thank you for your support and courtesy

    I noticed that with the low quality images that I have attached to the thread it is difficult to actually understand what I would like ... I try to fix a link for download two example Excel files where you can possibly investigate better.



    also I try to explain myself:

    The input file - see the ToPurgeFile.txt file (made of 548 records) that I have attached to my thread - is a simple text file where each record is made up of 6 numbers separated by a space the output file must have the same format with removed records that do not comply with the conditions imposed on the form.
    The form should consist of 17 areas each consisting of 3 columns by 15 rows where you can paste - do not insert one by one - groups of numbers that I have in Excel so the routine should check that each row of 6 numbers is validated and therefore is present in the output file, respect the min and max imposed presences.

    E.g. the attached file consisting of 548 records according to the conditions imposed in the Level_1.xlsm file - how many groups (3 with x 15 lines) with zero numbers must satisfy the 548 lines? from min 0 to max 2 for the 4 groups highlighted in yellow, min 0 to max 2 for the 4 groups highlighted in green and the same for the blue in any case a total of 3 simultaneous groups with zero prezenze is required will generate an OutPutFile of just 2 rows. This is the first level of analysis that I have to do on a data set, a second level -similar to this- involves a different intersection of conditions - see the example file Level_2.xlsm.

    Note: ToPurgeFile.txt file to be used in test with Level_1.xlsm must be in the same folder and renamed to ToPurgeFile.csv

    I hope I have been clear and that you can help me out
    thank you
    regards
    RIC63https://we.tl/t-tvg3CI7gJ0

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

    Re: Filter a txt file based on a grid of data

    is a simple text file where each record is made up of 6 numbers separated by a space the output file must have the same format with removed records that do not comply with the conditions.
    If you can specify the requirement for the output file based upon the input file without any reference to Excel, then I'll see what I can do to provide a C++ program that takes an input file, processes the data and produces an output file. You mention highlighted groups etc in Excel. I can't assist with Excel or Excel programming (VBA etc).
    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)

  5. #5
    Join Date
    Feb 2020
    Posts
    46

    Re: Filter a txt file based on a grid of data

    morning 2kaud

    I am attaching the image 'Level_2.pdf' with some comments in the hope that it will help to understand my request

    Thanks for your patience
    regards
    RIC63

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

    Re: Filter a txt file based on a grid of data

    You have a populated spreadsheet an example as shown in the image in post #5. You have an input text file containing lines of numbers and you want to filter these lines to produce an output file based upon data contained in the spreadsheet - yes?

    This is not just a simple case of data manipulation. To do this in C++ outside of Excel is not a simple task and unfortunately I won't be able to assist further.

    This task would probably be easier in C# (or vb.net) using .net and the Excel class etc. Perhaps some guru who knows c#/vb.net may be able to assist further?
    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)

  7. #7
    Join Date
    Feb 2020
    Posts
    46

    Re: Filter a txt file based on a grid of data

    Hi 2kaud
    yes you are right
    is exactly what i would like to do

    however, I appreciate your attempt to help me
    thanks
    greetings
    RIC63

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

    Re: Filter a txt file based on a grid of data

    A different approach to this problem would be to save the spreadsheet with the required filtering data as a separate csv file (text filter file). Then you have the requirement of producing a text output file from the input text file based upon criteria from the filter text file. This is certainly doable in C++ and should be quite fast.
    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)

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

    Re: Filter a txt file based on a grid of data

    Update. I've found some C++ code that enables data from an .xls spreadsheet to be read. Can you send me a copy of the .xls spreadsheet to use as a filter and I'll see if I can easily obtain values from it. If I can, then I should be able to convert the provided VBA code in post #1 into a standalone C++ 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)

  10. #10
    Join Date
    Feb 2020
    Posts
    46

    Re: Filter a txt file based on a grid of data

    good news :-)

    at the end of post # 3 there is a link to download both xlsm files
    xls..m means it is a file that contains vb code 'a macro'

    See you soon
    RIC63

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

    Re: Filter a txt file based on a grid of data

    Ok. I can now read the data in the .xlsm files in post #3 in a C++ program.

    What is level_1.xlsm and level_2.xlsm? Which one is supposed to be used for the filter? Given this .xlsm file and the ToPurgeFile.txt in post #1 as an example, what would be the expected content of the output file using the only_zero vb code from post #1?
    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)

  12. #12
    Join Date
    Feb 2020
    Posts
    46

    Re: Filter a txt file based on a grid of data

    What is level_1.xlsm and level_2.xlsm?
    They represent two levels of filtering with different conditions, normally I use the output from Level_1 as input for Level_2. I put both for example, I am confident that if I can have the code for one then I can study it a bit to create the code for the second one by myself ... I hope
    Which one is supposed to be used for the filter?
    It is indifferent .. I would to say to start with Level_1
    Given this .xlsm file and the ToPurgeFile.txt in post #1 as an example, what would be the expected content of the output file using the only_zero vb code from post #1?
    The result will be that of the 578 lines present in ToPurgeFile.txt only 44 will be validated therefore present in the output file.

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

    Re: Filter a txt file based on a grid of data

    Yes - but can you provide the file with the expected 44 lines so that I can check the code is working properly.
    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)

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

    Re: Filter a txt file based on a grid of data

    This is some 'proof of concept' C++ code. It should display the values in row 10 of the level_1.xlsm file. I used MS VS2019. You may need to change the file names/locations in the #import lines 1, 6 & 8 as I use Office 2010. Also, line 22 (sname) states the full path name for the level_1 file.

    Before you can progress much further with this, you need to be able to compile (ignore the 2 warnings for line 9) this code and produce an .exe file which when run from the console shows the data in row 10 which on the version of this file I have displays:

    Code:
    1 16 31 16 31 46 31 46 61 46 61 76 69 31 51 31 51 90 51 90 2 90 2 6 79 6 63 6 63 8 63 8 48 8 48 50
    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>
    
    using namespace Excel;
    using namespace std;
    
    const string sname = "c:\\MyProgs\\Level_1.xlsm";
    
    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 file" << endl), 3;
    	}
    
    	pXL->PutVisible(0, FALSE);
    
    	const _WorksheetPtr pWksheet = pXL->ActiveSheet;
    	const RangePtr pRange = pWksheet->Cells;
    
    	try {
    		for (int c = 14; c < 50; ++c)
    			cout << (int)pRange->Item[10][c] << " ";	// row column
    	}
    	catch (...) {
    		cout << "Cannot read the data!" << endl;
    	}
    
    	pWksheet->Release();
    	pBook->Release();
    	pXL->Quit();
    }
    Last edited by 2kaud; February 21st, 2020 at 07:21 AM.
    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)

  15. #15
    Join Date
    Feb 2020
    Posts
    46

    Re: Filter a txt file based on a grid of data

    Hi 2kaud

    attached you can find the OutPutFile.csv that is the result of applied filters (contain the expected 44 lines )

    Thanks

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
  •  





Click Here to Expand Forum to Full Width

Featured