input past end of file problem
input past end of file problem
input past end of file problem
input past end of file problem
input past end of file problem
input past end of file problem input past end of file problem input past end of file problem input past end of file problem input past end of file problem input past end of file problem input past end of file problem input past end of file problem
input past end of file problem input past end of file problem
input past end of file problem
Go Back  Xtreme Visual Basic Talk > > > input past end of file problem


Reply
 
Thread Tools Display Modes
  #1  
Old 03-06-2017, 07:29 AM
starmanMike starmanMike is offline
Junior Contributor
 
Join Date: Oct 2005
Location: near Norwich, UK
Posts: 287
Default input past end of file problem


Hi,
I am trying to read data from a text file and keep getting the error 62 message. The txt file is field-delimited by double hash symbols (##) - I have no choice, that's how the org. in question delimits its text files. The file exists - in fact here are a couple of lines from it (several fileds in each line are empty):
Type ## StarName ## AUID ## JD ## Fainter ## Mag ## Comment Codes ## Uncertain ## Filter ## Mag Error ## Comp Label ## Comp Mag ## Check Label ## Check Mag ## ChartID ## MagType

Vis ## TZ CYG ## 000-BCF-969 ## 2457029.2 ## 0 ## 11.0 ## ## 0 ## Vis. ## ## 108 ## ## ## ## 1995 ## STD
Vis ## S CYG ## 000-BCK-485 ## 2457029.2 ## 0 ## 13.0 ## ## 0 ## Vis. ## ## 129 ## ## ## ## 4600QA ## STD

(though looking in notepad after copy/pasting, there is no visible break between the end of the header - which I don't need - and the first record; i.e., it just says "MagTypeVis". I assume that's just 'cosmetic').
The actual code is (error line in bold):
Code:
Dim sFields(16) As String, rs As DAO.Recordset, i As Integer, filenum As Integer
'code to open the mdb file/rs goes here. No problem.
'Open CSV file to read
filenum = FreeFile
 Open App.Path & "\backup\allmyobs.txt" For Input As filenum
 
'Start read CSV line by line and upload it in the recordset
 Do Until EOF(filenum)
   
   'Get fields from CSV file into the array
   For i = 0 To 15
      Line Input #1, sFields(i)
   Next

'code to add the retrieved txet into the mdb goes here...

 Loop

'Close CSV file
 Close filenum
Are the hash symbols causing the problem? If so, could I just do a find/replace inside the file?
Thanks.
Reply With Quote
  #2  
Old 03-06-2017, 08:02 AM
Flyguy's Avatar
Flyguyinput past end of file problem Flyguy is offline
Lost Soul

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Vorlon
Posts: 19,164
Default

You are reading complete lines, not fields.

What you have to do is read the file line by line.
Then split the fields using ## as the separator and then store the content in the MDB
Reply With Quote
  #3  
Old 03-07-2017, 06:01 AM
starmanMike starmanMike is offline
Junior Contributor
 
Join Date: Oct 2005
Location: near Norwich, UK
Posts: 287
Default

Thanks - so evident when you point it out! But how exactly would I split each line into fields without (if possible) introducing ever more nested loops which could become very confusing? I am working with a small 'dummy' subset at the moment of about 25 records. However if I were to apply this to (say) my own full set this consists of about 30,000 records so might become very time-consuming!
Originally I was going to import the text file into a database with the aid of a schema.ini file but cannot understand the SQL involved, and kept getting error messages, so decided to try this method instead.

Last edited by starmanMike; 03-07-2017 at 06:03 AM. Reason: trying to remove excessive tags
Reply With Quote
  #4  
Old 03-07-2017, 08:08 AM
TheRealTinTin's Avatar
TheRealTinTin TheRealTinTin is offline
Junior Contributor
 
Join Date: Nov 2008
Location: Glasgow, UK
Posts: 383
Default

I would personally read the entire file into memory and work with the data that way. This will greatly improve performance.

Code:
Option Explicit

Private Const RecordDelimeter As String = vbLf
Private Const FieldDelimeter As String = "##"

Public Sub ReadFile(ByVal filePath As String)
    Dim hFile As Long
    Dim fileData As String
    Dim fileLines() As String
    Dim fileFields() As String
    Dim l As Long
    Dim f As Long
    
    ' Open the file...
    hFile = FreeFile
    Open filePath$ For Binary As #hFile
    
    ' Read the contents of the file into memory.
    fileData = Space$(LOF(hFile))
    Get #hFile, , fileData
    
    ' Close the file.
    Close #hFile
    
    fileLines = Split(fileData, RecordDelimeter)
    
    ' Loop through each line reading it's data.
    ' We start at 1 assuming the first record is the headings.
    For l = 1 To UBound(fileLines)
        If Not LenB(Trim$(fileLines(l))) = 0 Then
            fileFields = Split(fileLines(l), FieldDelimeter)
            
            For f = 0 To UBound(fileFields)
                ' Do something with the data.
                Debug.Print Trim$(fileFields(f))
            Next f
            
            Erase fileFields
        End If
    Next l
End Sub
As a side note, the reason there is no visible break between the header and first record is it's likely delimited with just the Line Feed character only to save space. When you press the Return key the application like Notepad and Word insert to control characters: Carriage Return and Line Feed. Each character uses a byte, meaning 2 bytes of data for each new line. To save space and improve performance larger files only use one character, the Line Feed, meaning for each new line (record) 1 byte is saved. Might not seem a lot but multiplied by thousands of records and you save thousands of bytes. Programs can still read this data knowing the CR character is for display purposes only. I've therefore assumed all records are delimited only by the LF character. If this is incorrect you will need to change the RecordDelimiter constant.
__________________
Artificial Intelligence is no match for natural stupidity
Reply With Quote
  #5  
Old 03-14-2017, 08:53 AM
starmanMike starmanMike is offline
Junior Contributor
 
Join Date: Oct 2005
Location: near Norwich, UK
Posts: 287
Default

Just tried this and... brilliant! Many many thanks. I would never have thought of this in 10^6 years!! (PS - wasn't the real Tintin Belgian?)
;-)
Reply With Quote
Reply

Tags
file, line, mag, csv, filenum, vis, code, input, read, error, std, close, mdb, check, integer, label, comp, cyg, text, hash, symbols, string, i.e, record;, magtypevis


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
input past end of file problem
input past end of file problem
input past end of file problem input past end of file problem
input past end of file problem
input past end of file problem
input past end of file problem input past end of file problem input past end of file problem input past end of file problem input past end of file problem input past end of file problem input past end of file problem
input past end of file problem
input past end of file problem
 
input past end of file problem
input past end of file problem
 
-->