How to read text data from .dat files and fetch the data in to excel based on input..

Greetings!

Problem Statement: How to read text data from .dat files and fetch the data in to excel based on input criteria entered in Search Field in user form built
--------------------------------------------------------------
Please refer attachment of prototype zip file and extract to local test folder that has sample excel file and sample .dat files(the text data can be seen by opening in notepad)

REQ tab has details on need and referring here below the high level.

looking for a function / program that should read all the .dat files from selected folder and Search for TokenID# (user will enter Token Id and this value as input for Token Id search criteria for example(1113265055) which is the input parameter to verify whether the number exists in .dat file/.dat file's or not.

If matching Token Id number found (TokenId# would be field5 value in .dat file always) in .dat file then that record needs to be written in to BID1 worksheet tab in the attachment with ecah value in to unique column. Every Record ends with ; is the identification for end of the record.

appreciate any help on this.

Thanks
Mahindra

See if this works

Please Login or Register  to view this content.

Thank you very much Jindon, this is exactly what I was looking for awesome response.
Few things I need here.

please see sequence of user steps/events as below:

step1.Basically as a user I will click 'Provide Destination Path' button to select the Folder location
Once user selects the folder it should display the folder path in 'txtOutPut' label.

step2.If this selected folder has any .dat files available or not need to be checked and if yes then allow user to enter Token Id number in 'txtInput' Text Field and enable the Search button.
if no .dat files in folder then message No Dat files in given folder.

step3:based on Token Id criteria it should fill in BID1 which you are doing with your latest code.
however is it possible to add file name that has the inserted record and date/time stamp
(Column AG need to be appended with ".dat file name" that has matching Token Id when it writes in to BID1 worksheet and column AG for every record that gets entered based on TokenID Search criteria if matches.)

step4: If new data gets added to saved template it should simply append at last row with above conditions.
If BID1 has existing rows then the latest record based on Search TokenID need to be inserted as last row in BID1 worksheet.

please see earlier I gave some code for 'cbDestFolderPath_Click' for reference. you can ignore the code written for 'ListAllFile'

Please let me know on above steps.
Thank you so much for your kind response.

Much appreciated!

Regards,
Mah

Hello Jindon, Thank you very much again for prompt response with great work, I really appreciate the logic you gave here.

I want to discuss with you regarding following points which will definitely make this code as very handy and robust search program code


Please refer Before and After Search Token comparison results as below:
I have screenshots for your reference in zip file (Search Results-Before & After-05-08.zip) if required I will send but not able to attach with thread..

problem here is if you search for a Token number (For example: 6029997777)
based on search the records (in this case 3 records for 6029997777) are getting inserted in to BID1 and
you can save the template and close the template.

Then open the template again and search for another Token number (For example: 1113265055)

Bug here the latest records(3 records for 1113265055) with second search Token number records are overwriting the previous records that are already in Template as existing records.

Actual result suppose to be, it should append from last row i.e row4 and we should see total records(6) based on
combination of existing records as well as new records (existing records+new records)
We always have unique time stamp after every search this is just a note.

after search if user saves then you will have 6 records in the BID1 worksheet,
if user choose do not save the template when user clicks x, then it should remain with original 3 records.

I hope I represented the issue to make it clear, please let me know if it is not clear I can provide with more example and details.


Requirement1: Can we have Date + Time stamp so that we know the time exactly when the record got inserted into BID, current code shows Date Stamp only.

Requirement2: Is it possible to show the UserFormSearchToken Pop up Form always on Search Records Tab, by default when we open the template as well as when we move from other tabs to Search Records Tab. Please let me know.
This will help alot otherwise we have to close the template always and open the template to see the search pop screen.

Requirement3: After Search Operation complete, can we please display a message in label (txtOutput2)
below the Search button (message would be "Found Records & available in BID1 worksheet"

I appreciate the great work you did and look forward to work with you.

Have a good weekend!
Regards,
Mah

attaching the screenshots to last response to Jindon.

Try this and change the wording as you wish in txtOutput

Please Login or Register  to view this content.

Hi Jindon, Thanks for your support again.

The overwriting problem looks like resolved, but the latest code created a new issue here,
when I look for 1113265055 in search field it found 3 records and they got inserted at row51 in excel table in BID1 from last 3 rows.

We need to latest found records supposed to be appended at row4.
please see attached template for this issue.

Regards
Mah

please see the attached template (BI-DataSetTemplate-05-08.zip) with latest code for your reference on this issue.

You have unused rows in the table, so delete such rows first then run the code.

Yes removing empty rows resolved the blank rows Jindon, this is cool, I will verify with bit larger .dat files now to see the results.

Regarding below need please advise.

Requirement2: Is it possible to show the UserFormSearchToken Pop up Form always on Search Records Tab,
by default when user opens this template as well as when we move back and forth from other tabs to Search Records Tab. Please let me know.

This will help a lot otherwise we have to close the template every time to open the template to see the search pop screen to enter Token Id information to search for.

Thanks for looking in to these.

1) Found bug in UserForm, change to

Please Login or Register  to view this content.

2) To SearchRecords sheet module

Please Login or Register  to view this content.

Thank you very much Jindon for your thoughtsand great support

Can we always show Folderpath in txtOutput first label and Search Token results in txtOutPut2 (this second label exists after Search text box)
current we are showing the search results by overwriting the folder path.

The enhanced code looks great, can we incorporate error handler to the code for every procedure/function like below

Err Handler:
---------------------------------------------------------
If we have err handler we will have good control if any unknown issue/bug comes while in program, please advise!

On Error GoTo FileError

Exit Sub

FileError:
Msg = "The following error has occurred" & vbCrLf _
& " Error #" & Err.Number & vbCrLf _
& " " & Err.Description
MsgBox Msg, vbCritical + vbOKOnly

Can we give message in 2nd label(txOutPut2) instead of MsgBox in below code, please let me know.
---------------------------------------------------------


File size Design: Also I want to discuss few advanced design options about file size for .dat files while searching in folders.

These BI .dat files are in same data format with data like we have in TestDataSet-BIData1.dat
but however the dat file size will be huge like more than 100 MB some times.

is it possible to split if the file size is large like more than 100 MB in to multiple small files while we are checking for dat files available in a selected folder.
(For example if I have file size (312,855 KB dat file) can we split in to 10 equal dat files)

If we can split the large size file in to multiple with out losing any record will it helpful otherwise searching might take long time if I have large file like 300 MB.


Thank you.

Hi Jindon, the file size will be more than 100 mb some times, but not always.

Not sure whether we can split programatically and automatically of a large dat file in to smaller dat files with out losing the content in this case the record data with same structure like ; end as indicator for record end.

Try this

Please Login or Register  to view this content.

Hi Jindon, with this change I think we are not updating all records especially column AF data is getting missed as well as
File Name in Column(AG) and Date & Time Stamp in Column (AH) are missing with latest records.

I will attach another sample dat file I created please take a look, I gave a search on token id(9402185221) from TestDataSet-BIData5.dat for example.
I am seeing missing data for last 3 columns (AF, AG & AH).
Some where in the logic if we have mismatch ? please look in.

attaching the TestDataSet-BIData5 for reference

with this latest change the file name is coming thru how ever date and timestamp is still missing..
also noticed while I gave search token (9402185221) it is bringing an additional second row from last with search token(6029997777) not sure how this one is getting added from TestDataSet-BIData5

attaching the latest template for reference, please look in to..

Change to

Please Login or Register  to view this content.

Format the last column ro Time manually.

I think there is a bug hidden here.
If you have data .dat file like continuous mode then it is not identifying the end of the record correctly.
if you take the TestDataSet-BIData5 see the last but one record and it is getting displayed in correctlyincorrect row display.jpg
please see atatchment

I can not replicate that problem here.
If you are talking about different .dat files, I should stop posting here.

no I am not talking about different dat files here, the data structure with number of columns remain same and data is correct,
I am seeing y'day file brings data correctly before changes with txtoutput2

not sure somewhere in the latest changed logic looks like there might be a gap.
how ever thanks for attempting on to re pro the issue.

I will write again later as I am seeing run time error.
can we give some err handler mechanism in proc please.

What error handler?

As I said I can not replicate that error here.
If dat file is not correctly formatted, nothing can do.

Dear Jindon, Thank you very much for taking time and providing quick responses, this helped me to look into and run during the weekend. I appreciate the knowledge u have awesome and sharing thoughts across this forum. error handling we can look in to later...

How ever with the latest changes from 05/10 the template is not able to fetch the records if you attempt to load .dat file size of 31 MB with search token number.
where as the same 31 MB dat file I could load and fetch the data based on Search criteria with Token number

Since you don't have this large .dat file(31MB size) you are not able to replicate the infinite loop err I am facing. I hope I represented this issue so that you can understand from my point.
Since these dat files are large I may not be able to upload for you with this forum, not sure I can do it or not since this dat file is large like I said 31 MB size.

For now let us use 05/09 code base which is working perfect with large file as well. I will also upload the working template with version number (BI-DataSetTemplate-v05-09) for our reference, please give comments for any changes in the code that will help me to understand what you are changing.
and the same with me If I am adding any for our mutual reference.

Now with that being said I want to discuss following possibilities with this 05-09 template (BI-DataSetTemplate-v05-09) code base.

Requirement1:

While searching for Token Numbers can we disable 'Provide Destination Path' button,
once results found then this button should be enabled.

Similarly
While searching for Token Numbers can we disable 'Search' button,
once results found then this button should be enabled.

Requirement2:

Can we have seperate procedure to handle File size verification especially with larger dat files like more than 100 MB.

is it possible to split, if the .dat file size in selected folder is large like more than 100 MB
in to multiple small files while we are checking for dat files available in a selected folder.
(For example if I have file size (312,855 KB dat file) can we split in to 10 equal dat files)

If we can split the large size file in to multiple with out losing any record will it helpful otherwise searching might take long time if I have large file like 300 MB.


If parentsample.dat file size is greater than or equal to (>=) 100 MB then can we split the original source .dat file in to multiple .dat files like below (this is an example)
Sample1.dat
Sample2.dat
Sample3.dat
Sample4.dat

and change the file extension of parentsample.dat to parentsample.zip
after splitting the large dat file in to subset of small dat files the search operation should continue with the token number given in search field.

If parentsample.dat file size less than 100 MB then no need to split the parentsample.dat file and continue the search with token number given as input.

please let me know on above points.

Thank you very much again for your kind support.

Regards

attaching the 05/09 version which is working for large files like 30 MB dat files.

Hi Jindon,

please let me know if we can verify .dat file size before enabling Search button.

the condition is to look for .dat files (one or more) in a selected folder with file size only less than 100MB then enable the search button.

Private Sub cbDestFolderPath_Click()
Dim myDir As String, fn As String
Me.txtOutput.Caption = ""
Me.txtInput.Enabled = False
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then myDir = .SelectedItems(1) & "\"
End With
fn = Dir(myDir & "*.dat")
If (myDir = "") + (fn = "") Then Exit Sub
Me.txtOutput.Caption = myDir
Me.cbSearch1.Enabled = True
With Me.txtInput
.Enabled = True: .SetFocus
End With
End Sub

Thanks much.

Hi Jindon,

Is it possible to generate the data based on record entry like I will have number "1" or "2" or "3" or "4" in every input dat file.
the current search code puts the filtered data in to BID1 how ever if
record entry=1 (in first column data in dat file) then this record should be in BID1
record entry=2 (in first column data in dat file) then this record should be in BID2
record entry=3 (in first column data in dat file) then this record should be in BID3
record entry=4 (in first column data in dat file) then this record should be in BID4

please see the attached STP zip file that has sample data and search code from Sub DATSearchOperation() in STP-v07-14
The original code needs to be chnaged for above need, please let me know on this.

Appreciate any help with this request.

Thanks Jindon for prompt response!

when I gave a token number for search it is throwing following Runtime err'53' file not found

Open fn For Input As #ff

please see attached err images for reference.
please note BID1 and BID2 tabs has same number of columns (34 columns) and BID3, BID4 tabs has 28 columns.

rest everything I have in working condition except with this search condition to split data records based on record entry.
please let me know on this, I am sure this can be resolved. Thank you!

Ah, OK, I didn't see the codes inside...

Try change to

Please Login or Register  to view this content.

Note:
No error trap when worksheet doesn't exists. e.g. BID6 etc...

Thanks Jindon, earlier I have cleaned up and added lot of err conditions to have clean code.

With above change your Note is very valid and it is throwing "Subscript out of range err" Run time err 9
to debug this and For time being I have commented the err check in this sub.

please let me know on this err troubleshoot..greatly appreciate your support!

and also just a note, I will have BID1, BID2, BID3 and BID4 and if any new sheets to add that we can think later.
the challenge is token number can be in any of dat files but based on colimn1 in dat files (this column 1 will have 1 or 2 or 3 or 4) the corresponding record needs to be entered in to corresponding tab (BID1 or BID2 or BID3 or BID4)

if renaming the tabs will solve this problem, I can rename BID1 to BID-A and BID2 to BID-B, BID3 to BID-C and BID4 to BID-D, but not this helps anything with above subscript out of range err.

I mean

but not sure above tab renaming will help anything with above subscript out of range err.

I'm on iPod
If you google

IsSheetExists jindon

You will find the function to check

Otherwise I'll post the code when I can

Thanks Jindon, please post the new code related to Sub DATSearchOperation() with IsSheetExists when you have some time, that would be helpful.
looks like this IsSheetExists may resolve but please add your code to the latest version, I am attaching here (STP-v07-15.zip)
The change in the template is I have renamed BID tabs to BID-A, BID-B, BID-C, BID-D and attaching the sample dat files in case if u need.
The file names are just example, the file name can be any name.

I can quickly validate with a test once you have new code please do let me know.
Thank you!

skywriter, thanks for the help.

mahendra.asapu,

File attached

STP-v07-15 with code.xlsm

Thanks skywriter for helping with func.

Jindon, Thank you very much for the change as I see you have modified based on Template which is good.

I would like to verify with you on following logic.

Is it possible to have four templates(Template-A, Template-B, Template-C, Template-D) instead of one template this is because I will have different column names in each template
Template-A, Template-B, Template-C, Template-D

If we have below Logic that would be very helpful
BID-1 should be generated based on Template-A (If Column1 value = 1 in DAT Files)
BID-2 should be generated based on Template-B (If Column1 value = 2 in DAT Files)
BID-3 should be generated based on Template-C (If Column1 value = 3 in DAT Files)
BID-4 should be generated based on Template-B (If Column1 value = 4 in DAT Files)

I believe this will make simple and also flexible with column names which are defined in 4 templates in first row of each of four templates.

Please let me know on this logic.
I appreciate all the great support with SearchOperation procedure.

If there is a clear logic to find out which template to copy, it is possible.

just a correction below

BID-4 should be generated based on Template-D (If Column1 value = 4 in DAT Files)

if so I can have four templates wondering where should the logic based on templates

is it to change below code, appreciate if you can give code if it is not too much to ask, please let me know.

If Not IsSheetExists("BID-" & a(i)(0)) Then
Sheets("template").Visible = -1
Sheets("template").Copy after:=Sheets(Sheets.Count)
Sheets("template").Visible = 2
Sheets(Sheets.Count).Name = "BID-" & a(i)(0)
End If
Sheets("bid-" & a(i)(0)).Range("a" & Rows.Count).End(xlUp)(2) _
.Resize(, UBound(a(i)) + 1).Value = a(i)

It is not like that easy...

1) Add 2 variables "temp" "msg"

Please Login or Register  to view this content.

2) change to

Please Login or Register  to view this content.

Thanks Jindon, just added four templates and changed with above code in the proc and it is throwing Subscription out of range err 9 and it is creating duplicate template-D when I entered a token number that was based on record value=4
attching the code and err screenshots, please let me know.

Thank you very much.

OK, fixed, I hope.

Yes Jindon, seems working. need to verify with large dataset and will let u know If I see any issues if u dont mind, but lot of learning and inspiration from u.
is it just a change with - in below, looks like.

Sheets("template-" & temp).Visible = 2

I appreciate all the support.
I will let u know If I see any other issues with search operation , looks fantastic!

Have a wonderful day!

Hi Jindon, The SearcOperation Proc is working fine with data returning in to BID1 / BID2 / BID3 / BID4 based on search results. This part is working as expected.

However I am seeing the worksheets are getting duplicated like BED-1 and BED-1, please see screenshot and attachment of the spreadsheet(STP-v07-16) for the duplicate tabs.

Is it possible always to have single instance of BID-1, BID-2, BID-3 and BID-4 displayed after SearchRecords Tab, please let me know.

Thanks much and appreciate all the help.

You have a space in between hyphen and 1 in tab name "BID- 1"

Hi Jindon, Couple of clarifications as below if u don't mind about this discussion, which will help to understand where the gap is in DATSearch Proc.

1. A bit unclear about this extra space creation automatically, as a user we didn't create these tabs and they were generated based on templates. Is it possible to eliminate this space between hypen and number (I believe number is dynamic from a(i).

2. Start with just "SearchRecords" Tab and if you search for a token record for example (Token:7773228279, and this token has Record Type1 and Record Type2 and is present in two DAT Files DAT1, DAT2 (I am attaching the sample data for your reference) and once click Search button what is happening is it is creating duplicate Templates instead of showing the results with BID-1 & BID-2 tabs.

Is it possible to validate in the proc about this valid user scenario in DATSearch Proc, please let me know.
attaching the duplicate template tabs screenshot and speadsheet (no changes in the code from last version but kept with only tab "SearchRecords".

Please see the sample test data for common token reords in zip file for your reference and let me know your thoughts and any adjustment in the code which will resolve this problem.

Thank you once again!
Best.

It was actually line feed, no a space...

Can you add one line

Please Login or Register  to view this content.

Hi Jindon,

I think after adding above one line the line feed issue looks like resolved.

But duplicate template tabs issue2 still exists after adding above additional line code, please advise.

Repro Steps: Start with just "SearchRecords" Tab and if you search for a token record for example (Token:7773228279, and this token has Record Type1 and Record Type2 and is present in two DAT Files DAT1, DAT2 (I am attaching the sample data for your reference) and once click Search button what is happening is it is creating duplicate Templates instead of showing the results with BID-1 & BID-2 tabs.

Just working fine here.

I just used STP-V07-16 from above and still gets this error.

Please see following repro steps.
Step1: please make sure you have only "SearchRecords" Tab, delete BID-1 and BID-2 and save the STP spreadsheet.
Step2: Please make sure to have TestDataSet-BID1.DAT and TestDataSet-BID2.DAT since these tabs has token records 7773228279.
Step3: please give this token value 7773228279 and search.

I am getting the Template-A (2)
attaching repro steps screenshots if that can help to troubleshoot.

or else I can call u if you can give your number to explain on repro steps, please let me know.

OK, this seems to be the problem

Change to

Please Login or Register  to view this content.

Thank you very much Jindon, yes above change with add one line and commented the line (Sheets(Sheets.Count).Name = "BID-" & a(i)(0)....... Delete) resolved this duplicate tab problem.

However I noticed if u search for four numbers one after the other the values are correctly coming but last search number was not showing up in BID-4 if there is a token of record type4.

To resolve this what I did is added commented below lines and added a new line

' usrFrmSrchBID.cbSearch1.Enabled = True
' usrFrmSrchBID.cbClose1.Enabled = True
' usrFrmSrchBID.cbClose1.Visible = True
' usrFrmSrchBID.cbClose1.SetFocus

Unload usrFrmSrchBID

This helped to solve the last search record results to show in BID-4.

Since duplicate tab issue now resolved I still need to load additional large data and see how this works which I will do later.

Once again thank you very much for staying on Forum late on weekend to troubleshoot above critical problem.
Have a good weekend!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi Jindon, I hope you are doing good.

Certainly we can close this thread with resolved, just need some thought on below message issue with Found Records.

Ideally we need to display In lblOutput, the message of found records in lblOutput (added this label in today's cleaned-up version, please take attachment of latest version "STP-v07-18")

Following code added in DATSearchoperation proc.

Me.lblOutput.Caption = "Found " & n & " record" & IIf(n > 1, "s", "") & vbLf & _
"Now available in " & "BID-" & a(i)(0)


With above current code it is only displaying single BID which is ok if search returns with single record available in any single BID tab, how ever the problem is if we have a same record in more than one BID.

Problem: How do we display if a token is available in more than one BID tab, best example is token id: 7773228279

Please let me know is it possible to resolve above message problem d, I am sure you will have some solution,
appreciate your support on this and we are near with closing this thread..

I am attaching the modified spreadsheet STP-v07-18 which has interface modification.

Thank you

Couple of clean up and change

Please Login or Register  to view this content.

Thank you, the adjustment with messaging works.