Re: Importing Text File that is vertically oriented

ghadley_00@xxxxxxxxx wrote: 
Hi,

I have a MS access database into which I need to import a text file
that is oriented vertically (fields are listed from top to bottom with
the value for each field appearing to the right of the field name (with
a "|" as a delimiter)).  The text file will only have 1 record's data.
I would ideally like to setup a linked table to import this data - does
anyone know of a way I can setup this import?

Any help that can be provided would be greatly appreciated.

Best wishes,

George Hadley
ghadley_00@xxxxxxxxx


Well, you should know what fields are associated with a line number or some other way to identify the field. Let's say you have a table called Table1. ID (autonumber) is the first column which would have an index of 0, and the rest is of text file matches the first 10 fields. Some code like the following would work

Private Sub ReadFile(strFile As String)
		
    Close #1
    Open strFile For Input As #1  'open the text file

    Dim intCnt As Integer	
    Dim strLine As String
    Dim rst As Recordset
    set rst = Currentdb.Openrecordset("Table1",dbopendynaset)
    rst.AddNew
	
    Do While Not EOF(1) ' Loop until end of text file.
        Line Input #1, strLine    ' assign text file line to variable
	intCnt = intCnt = 1	  ' increment index counter
	rst(intCnt) = strLine     ' update with value from text file
    Loop

    rst.Update
    rst.Close
    set rst = Nothing

    Close #1
End Sub

Now, strLine could contain a date, a number, or text. Depending on the field type, you may need to use CLng, CInt, CDate, etc to convert the value from a string to number or date when updating the field.
.

Relevant Pages

  • Re: Looping through Query to create multiple sheets in excel- Just need the loop
    ... I always get to this point and I can't figure out how to loop through ... I have put the string in the query at the bottom. ... Optional strWorkSheet As String, Optional strRange As ... Dim objXLSheet As Object 'Excel.Worksheet ...
    (microsoft.public.access.forms)
  • Re: How is this conditional able to execute?
    ... inside the loop, then each time the loop iterates then a NEW reference to ... Note that the test of returndata is not a type. ... Dim returndata As String = "" ... Not that 'Changing' is only displayed when returndata is NOT an empty string ...
    (microsoft.public.dotnet.languages.vb)
  • Re: How is this conditional able to execute?
    ... Why is this part out of the loop: ... Maybe I just don't understand the returndata = "" because of the byte array. ... How can I read the string in: ... > Dim t As Thread ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Dynamically Update Text Box on-slide with loop (VBA)
    ... the slide. ... checked and strDiff does not get updated during the loop. ... Dim EventDate As Date, curDate As Date ... Dim sDateFile As String ...
    (microsoft.public.powerpoint)
  • Re: How to make space between bars in a graph?
    ... To get rid of the characters I'd use a simple loop and chop them out one by ... Private Function StringFilter(Strng As String) ... Dim FilteredStrng As String, n As Integer ...
    (microsoft.public.access.modulesdaovba)