How to import text file line by line?

[hclifford]

Hi guys,

I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:

Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
 
Open "C:\Test\Test.txt" For Input As #1
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
 
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError     'Clear tblResults
 
With rst
  .AddNew       'for 1st Block of Data only
    Do While Not EOF(1)
      Line Input #1, strLine            'Read line into variable.
      intLineNum = intLineNum + 1       'Line Counter
        If InStr(strLine, "Sample:") > 0 Then
          ![sample] = Mid$(strLine, InStr(strLine, "Sample:") + 8)
        ElseIf InStr(strLine, "Additional Info:") > 0 Then
          ![AdditionalInfo] = Mid$(strLine, InStr(strLine, "Additional Info:") + 17)
        ElseIf InStr(strLine, "Reference:") > 0 Then
          ![Reference] = Mid$(strLine, InStr(strLine, "Reference:") + 11)
        ElseIf InStr(strLine, "Analyte") > 0 Then
          'Debug.Print Mid$(strLine, InStr(strLine, "Analyte") + 8)
        ElseIf Left$(strLine, 1) = "%" Then
          ![Percent] = Mid$(strLine, 3)
        ElseIf InStr(strLine, "Scaling Ref. :") > 0 Then
          ![ScalingRef] = Mid$(strLine, InStr(strLine, "Scaling Ref. :") + 15)
        ElseIf intLineNum Mod 14 = 0 Then
          intLineNum = 0        'RESET Line Counter for next Block of Data
           ![Date] = Date
            .Update             'Record Separator, time to Save Record
            .AddNew             'Return to ADD Mode for next Block
    End If
Loop
End With
 
Close #1
rst.Close
Set rst = Nothing

Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.

I think using the Left$ function would help, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.

Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1
Line 2 is placed in Row 2 Column 1
Line 3 is placed in Row 3 Column 1
.
.
.
Line X is placed in Row X Column 1.

Can somebody help me with this please? Especially the part about the character count?

 

[Galaxiom]

Without the optional thrid parameter, Mid should return the rest of the line after the Start position no matter how long.

However the record will truncate at 255 characters if the table field is text.

There are no rows in Access. They are records and there is no way to control the order they appear in the table. If you need them ordered then you must include a field that can be used to order them.

Write the loop counter variable to that field of the recordset.

 

[smig]

If you simply need to import the full line what do you need the Mid or Left functions for?
What criteria do you have?
This code make a lot of manipulations on the lines imported. I hardly believe you need these manipulations too.

why not simply use the built in Import routine ?

Rows and Columns are in Excel.
In Access you use Records and Fields.

 

[hclifford]

Without the optional thrid parameter, Mid should return the rest of the line after the Start position no matter how long.

However the record will truncate at 255 characters if the table field is text.

There are no rows in Access. They are records and there is no way to control the order they appear in the table. If you need them ordered then you must include a field that can be used to order them.

Write the loop counter variable to that field of the recordset.

So I should so-called use line count as the ID number to make sure that my text file is not rearranged?

 

[hclifford]

If you simply need to import the full line what do you need the Mid or Left functions for?
What criteria do you have?
This code make a lot of manipulations on the lines imported. I hardly believe you need these manipulations too.

why not simply use the built in Import routine ?

Rows and Columns are in Excel.
In Access you use Records and Fields.

Sorry about that, I mean

Line 1 to Row 1 Field 1
Line 2 to Row 2 Field 1
Line 3 to Row 3 Field 1
.
.
.
Line X to Row X Field 1

I did. But the import function rearranged my data. I even tried importing the same data, with the same settings to tables with the same settings, but when I looked through the records, some of them were ordered differently. I need it to be in the same exact order as the text file.

 

[Galaxiom]

I did. But the import function rearranged my data. I even tried importing the same data, with the same settings to tables with the same settings, but when I looked through the records, some of them were ordered differently. I need it to be in the same exact order as the text file.

As I said, the order of records in an Access table cannot be controlled. You will need to import the line counter if the order is required and there is nothing else to order them by.

Then display them in the required order by using a query.

 

[hclifford]

As I said, the order of records in an Access table cannot be controlled. You will need to import the line counter if the order is required and there is nothing else to order them by.

Then display them in the required order by using a query.

Yup, thanks a lot! Can't believe I didn't think of that solution:banghead:
I'm trying to code it now.

But how do I store the entire line of text too?

 

[Galaxiom]

In the loop add a line:

![field1] = strLine

 

[hclifford]

I managed to do it like this

Private Sub Command0_Click()
    Dim LineCount As Integer
    Dim Line As String
    Dim strSQL As String
 
    Open "C:\Users\hclifford\Downloads\oct\Oct2.txt" For Input As #1
 
    While Not EOF(1)
        LineCount = LineCount + 1
        Line Input #1, Line
        strSQL = "INSERT INTO Table2(ID, Field1) VALUES ('" & LineCount & "', '" & Line & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    Wend
 
    Close #1
End Sub

It works for the first 16 lines but ends up in a "Runtime Error 3075: Syntax error (Missing operator) in query expression" on line 17.

How do I resolve this?

PS, My table contains 2 fields: ID (number) and Field1 (memo).

The text on line 17 is:

CRT_ulSSO_CBGSSO_AUTH_HK[(Log_Path="/app/CA/tempfolder/cbgsso/log/" AND Log_Name="auth_HK.log" AND STRSCAN(Description_U, N"AuthTimeout Error initializing DSSS") = 1 ) OR ( STRSCAN=1 OR STRSCAN(Description_U, N"AuthTimeoutException") = 1 ) OR ( STRSCAN=1 OR STRSCAN(Description_U, N"Error initializing DSSS") = 1 ) OR ( STRSCAN=1 OR STRSCAN(Description_U, N"MQException") = 1 ) OR ( STRSCAN=1 OR STRSCAN(Description_U, N"NoServerException") = 1 ) OR ( STRSCAN=1 OR STRSCAN(Description_U, N"System exception error") = 1 ) ) ) ;ULLOGEN=1 ) ON x01gssoapp1a:KUL (Log_Path=/app/CA/tempfolder/cbgsso/log/ Log_Name=auth_HK.log Description_U=28 Oct 14 13:37:54, 868 ERROR [Thread-6687412]: (GCGUtil: getTokenInfo, AGENT ID: ibr_ibapp1a, TransRef: 10000000000506603349) [GCG] Exception:com.ibm.mq.MQException: MQJE001: Completion Code '2', Reason '2033'.: )]~

 

[Galaxiom]

The problem is the quotes inside the text. These prematurely close the SQL string.

One way around it is to escape the double quotes by doubling them.

Apostrophes/single quotes are going to give you a similar problem.

I would use the recordset.

 

[hclifford]

the problem is the quotes inside the text. These prematurely close the sql string.

One way around it is to escape the double quotes by doubling them.

Apostrophes/single quotes are going to give you a similar problem.

I would use the recordset.

i did it man! It works perfectly now. Thanks a lot galaxiom! couldn't have done it without you!

 

[gemma-the-husky]

in passing, note that when you say an import (I presume docmd.transfertexct) brings the line in jumbled - I presume you mean with a seemingly random order - this is not a problem at all, unless the items need to have a specific order for some reason.

each line needs to contain all the information about that line, and then the order of the lines shouldn't matter.

 

[shawnntjr]

in passing, note that when you say an import (I presume docmd.transfertexct) brings the line in jumbled - I presume you mean with a seemingly random order - this is not a problem at all, unless the items need to have a specific order for some reason.

each line needs to contain all the information about that line, and then the order of the lines shouldn't matter.

Hi Gemma!

I do need the rows to be in the right order as I'll be parsing data from it.

Anyway, the method that Galaxiom worked at importing the file, but I'm still not sure why some of the rows are jumbled :/

 

[shawnntjr]

in passing, note that when you say an import (I presume docmd.transfertexct) brings the line in jumbled - I presume you mean with a seemingly random order - this is not a problem at all, unless the items need to have a specific order for some reason.

each line needs to contain all the information about that line, and then the order of the lines shouldn't matter.

Hi Gemma!

I do need the rows to be in the right order as I'll be parsing data from it.

Anyway, the method that Galaxiom worked at importing the line number and text, but I'm still not sure why some of the rows are jumbled :/

 

[gemma-the-husky]

Hi Gemma!

I do need the rows to be in the right order as I'll be parsing data from it.

Anyway, the method that Galaxiom worked at importing the line number and text, but I'm still not sure why some of the rows are jumbled :/

I appreciate that Galaxiom showed you how to process data a line at a time.

The point is in general, you should not need a particular order. That isn't the way a database works. A table in a database consists of a set (the key word) of data. a set is essentially an unordered collection. The whole of database theory is based on set theory. that's why the order appears to be random. To a database, it doesn't matter.

the only reason you might want to process items in a sequential file in a sequential order, is if the records have some sort of inter-relatedness, so that some get added to one table, and others to another table. you could resolve this in other ways. Another way of managing your data is to add a column with a sort order to the data - then you would not need to load the data a line at a time.

 

[shawnntjr]

the only reason you might want to process items in a sequential file in a sequential order, is if the records have some sort of inter-relatedness, so that some get added to one table, and others to another table. you could resolve this in other ways. Another way of managing your data is to add a column with a sort order to the data - then you would not need to load the data a line at a time.

You're right. My data does have some inter-relations. I need it to be in the exact sequence due to the fact that I need to use another code to scan through and remove faulty data. After this, I also need to rearrange it in another table such that every 6 rows is placed within 6 columns.

Maybe there's another more direct way of doing it, but I admit that it's really due to my incompetence in VBA that I'm unable to figure out.

 

[namliam]

The better yet solution is to either
1) add a autonumber to your table and use docmd.transfer
2) process the file immediately instead of adding it to a temporary table to process later.