Help to import text file of multiple formats (1 Viewer)

MajP · Jul 29, 2019

This one is so different from the others that you cannot code it the same way. You would need to do something similar to your initial request and hardwire the code for this report. The exceptions and warnings need to go in a separate table exactly like tblReasons except the FK would be a serial_ID. In the code if it is a VR report it calls it own routine and not the generic routine used on the other reports. I will take a look tomorrow.

lookforsmt · Jul 29, 2019

Thank you so much MajP for looking into this.

MajP · Aug 6, 2019

This version has a separate module for the VR report. It pushes the values into 3 VR tables linked by zone_code and serial_ID. The code works but it is super confusing with a lot of if thens and loops. You probably want to make the form similar to the original MOM code where you have two tables.

lookforsmt · Aug 7, 2019

Hi! MajP, Apologies, couldnt respond to you earlier.
Thank you for the code, i have amended the names as per my text file and tried to upload the data. Got the following run-time error '9' Subscript out of rang

After i upload, only 11 records get uploaded before showing the error and highlights in module ImportVR
SQL = Split(tempArray(2), " " ) (1)

The text file has more than 200+ records.
tblVR & tbl_Reasons has displayed correct details but it only displays 11 records in each table.
tblVR_Transactions has also displayed 11 records but there is duplicate of amount picked in "Const" field.
Pls refer to attached snapshot. i tried to check the spacing in the text files and i dont see any difference.

The field name "Const" seems to be the issue, but i might be wrong.

MajP · Aug 8, 2019

Surround the code as follows

Code:

  If UBound(Split(tempArray(2), " ")) = 1 Then
      SOL = Split(tempArray(2), " ")(1)
  End If

This will keep that error from happening, but does not explain why it is happening. You need to look at that line where it is failing and see what is different.
YTD 1203

lookforsmt · Aug 8, 2019

HI! MajP, now it gives me another run-time error 9, Subscript out of range.
The error has moved to " AvlBal = tempArray(0)
i am not able to identify the reason

MajP · Aug 8, 2019

In the example you have two data lines. So it finds and reads the first and then moves to the next line. My guess is that there is some line possibly blank between them. That would cause it. Can you look for that. See sample lines A and B for something in between.

lookforsmt · Aug 8, 2019

Hi I have checked the line A as you mentioned.
If I keep my cursor after Acct Name and hit right key on keyboard. It moves to next row exactly where you placed the B.
The second example is also another instance which in my text is on Srl No 9. Between the A and B there us a row space after the ------- dash and then B follows.
But surprisingly as mentioned earlier it captures 11 self no data.
I hope I have explained you well enough

The error on temp(0) subscript out of range

MajP · Aug 8, 2019

Any chance you can extract the sheets where it fails and send them? I would need to see how the format is different. Without seeing the "exceptions" it is hard to come up with a strategy. The rule currently is that it finds a dataline then move down a line, but that does not seem to always be the case. I need to see cases when it does not.

lookforsmt · Aug 8, 2019

Thanks MajP, i will try if i can send this across or atleast try to replicate something close.

MajP · Aug 8, 2019

Try this it is a little more robust. In the 'Datalines section of code wrap the Line Input with a loop

Code:

Debug.Print SerialNo & " " & AcctNo & " " & CRN & " " & SOL & " " & AcctName
        'Move to next line
[COLOR="Red"][B] 
       Do
          Line Input #intFile, strIn
        Loop Until IsDataLine(strIn)
[/B][/COLOR]        tempArray = GetCleanArray(strIn)

lookforsmt · Aug 8, 2019

i have updated the code in between the lines. I does not give me any run-time error. It takes time to read the entire doc close to a minute. But does not update any row in any of the 3 tables.

i have attached updated txt file. I realized there is no tab space between any words. it is always with space bar.
hope this is close to real text file

MajP · Aug 8, 2019

You may have made a mistake somewhere which put it into a loop. My version seems to read fine. I did not get any errors, but I did not fully verify the all the data. Try this.

lookforsmt · Aug 10, 2019

Thanks MajP, i was trying to check different ways to avoid the loop, possibly may have messed with the code. I will test the updated txt file with the v11 DB on my office network and update you by coming Thr or Fri.

Help to import text file of multiple formats (1 Viewer)

MajP

You've got your good things, and you've got mine.

lookforsmt

Registered User.

MajP

You've got your good things, and you've got mine.

Attachments

lookforsmt

Registered User.

Attachments

MajP

You've got your good things, and you've got mine.

lookforsmt

Registered User.

MajP

You've got your good things, and you've got mine.

Attachments

lookforsmt

Registered User.

MajP

You've got your good things, and you've got mine.

lookforsmt

Registered User.

MajP

You've got your good things, and you've got mine.

lookforsmt

Registered User.

Attachments

MajP

You've got your good things, and you've got mine.

Attachments

lookforsmt

Registered User.

Similar threads

Users who are viewing this thread