Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-28-2019, 06:14 PM   #61
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,670
Thanks: 27
Thanked 502 Times in 475 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help to import text file of multiple formats

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.

MajP is online now   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lookforsmt (07-28-2019)
Old 07-28-2019, 07:42 PM   #62
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 490
Thanks: 71
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

Thank you so much MajP for looking into this.
lookforsmt is offline   Reply With Quote
Old 08-05-2019, 07:12 PM   #63
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,670
Thanks: 27
Thanked 502 Times in 475 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help to import text file of multiple formats

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.
Attached Files
File Type: accdb Read Multiple Reports_V10.accdb (1.13 MB, 3 views)

MajP is online now   Reply With Quote
Old 08-07-2019, 09:13 AM   #64
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 490
Thanks: 71
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

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.
Attached Images
File Type: jpg VR_Error1.jpg (114.4 KB, 3 views)
lookforsmt is offline   Reply With Quote
Old 08-07-2019, 03:42 PM   #65
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,670
Thanks: 27
Thanked 502 Times in 475 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help to import text file of multiple formats

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
MajP is online now   Reply With Quote
Old 08-07-2019, 06:51 PM   #66
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 490
Thanks: 71
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

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
lookforsmt is offline   Reply With Quote
Old 08-07-2019, 07:14 PM   #67
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,670
Thanks: 27
Thanked 502 Times in 475 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help to import text file of multiple formats

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.
Attached Files
File Type: txt VR2 Sample 3.txt (1.1 KB, 4 views)

MajP is online now   Reply With Quote
Old 08-07-2019, 08:57 PM   #68
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 490
Thanks: 71
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

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
lookforsmt is offline   Reply With Quote
Old 08-08-2019, 03:35 AM   #69
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,670
Thanks: 27
Thanked 502 Times in 475 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help to import text file of multiple formats

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.
MajP is online now   Reply With Quote
Old 08-08-2019, 07:15 AM   #70
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 490
Thanks: 71
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

Thanks MajP, i will try if i can send this across or atleast try to replicate something close.
lookforsmt is offline   Reply With Quote
Old 08-08-2019, 10:48 AM   #71
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,670
Thanks: 27
Thanked 502 Times in 475 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help to import text file of multiple formats

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
 
       Do
          Line Input #intFile, strIn
        Loop Until IsDataLine(strIn)
        tempArray = GetCleanArray(strIn)
MajP is online now   Reply With Quote
Old 08-08-2019, 11:36 AM   #72
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 490
Thanks: 71
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

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
Attached Files
File Type: txt VR2 Sample 3New.txt (6.1 KB, 3 views)
lookforsmt is offline   Reply With Quote
Old 08-08-2019, 11:45 AM   #73
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,670
Thanks: 27
Thanked 502 Times in 475 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Help to import text file of multiple formats

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.
Attached Files
File Type: accdb Read Multiple Reports_V11.accdb (1.04 MB, 3 views)
MajP is online now   Reply With Quote
Old 08-10-2019, 05:28 AM   #74
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 490
Thanks: 71
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help to import text file of multiple formats

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.

lookforsmt is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Print-To-File (Generic/Text only) formats incorrectly Bechert Reports 1 04-13-2017 07:26 AM
Importing multiple text files in different formats mims Modules & VBA 0 10-15-2011 05:40 AM
Import a single Text File into Multiple Tables minute37 Tables 1 10-15-2008 08:02 AM
Import from text file into multiple tables herbwarri0r Modules & VBA 2 04-21-2008 11:00 PM
Text file to MS Excel multiple file import application tokunbo General 3 07-22-2006 11:08 PM




All times are GMT -8. The time now is 06:34 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World