Line Import question 1

[tnayfeh]

Here's code I have to import one line of text into an access table:

   strBuffer = f.readline
   strValue = Mid(strBuffer, 1, 6)
     Select Case strValue
         Case Is = "Source"   
          rs.AddNew
          rs!Field1 = Mid(strBuffer, 10, 30)
          Datasource = rs!Field1
          rs.Update
     End Select

The problem I have is that sometimes the first line is Source_Data which I don't want read into the table.

The file looks something like this:
Source "Customers.xls"
Source_Data "100240"
Source "Products.xls"
Source_Data "1230"

The text file that it reads is space separated rather than fixed width. How can I change my code to read and import space separated values rather than a fixed amount of characters?

Thanks in advance.
TN

 

[MacCaillean]

Hi TN

If you change your Case statement to look for "Source " (where the last character is a space), does that achieve what you want?

Regards

Mac

 

[stephenk1973]

Could you put an if statement looking at the strBuffer to

strBuffer = f.readline

If left(strBuffer,11)='Source_Data' then
'Do nothting
Else
'set up and add your field
strValue = Mid(strBuffer, 1, 6)
Select Case strValue
Case Is = "Source"
rs.AddNew
rs!Field1 = Mid(strBuffer, 10, 30)
Datasource = rs!Field1
rs.Update
End Select
End if

Stephen

 

[tnayfeh]

Hi Mac,
It could work, but I'd also like to read other values from this file and they may not necessarily be in the first character position of each line.

Any ideas of how to read the entire line then import values separated by spaces?
Ex:
Source "Value" ID "Value".....
Source_Data "Value" ID "Value"....

If I wanted to import the "Value" after ID from both lines how would I do that?

Thanks,
TN

 

[Remou]

Perhaps:
strBuffer = Split(f.readline, " ")
strValue = strBuffer(0)
Do you not wish to import this file with a specification and then process it?

 

[tnayfeh]

Ideally I would like to read line by line, search for specific character strings, like "Source" or "Source_Data", etc. then populate Field1 with the "Value" that immediately follows the strings I specify.

 

[Remou]

Split will create an array separated at anything you choose; a space in the above example. You could then examine each element of the array.

 

[tnayfeh]

   Do While f.AtEndOfStream <> True
        strBuffer = Split(f.readline, " ")
        strValue = strBuffer(1)

        If strValue = "Datasource" Then
                rs.AddNew
                fkPK = funGetPrimKey()
                rs!ID = fkPK
                rs!Column = strValue
                rs!field1 = strBuffer(2)
                rs.Update
         End If
    Loop

I keep getting a subscript out of range error message. Am I doing something wrong here? How should I declare my variables?

 

[tnayfeh]

    Dim fs As Variant
    Dim f As Variant
    Dim strBuffer, strValue As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String

    strSql = "SELECT * FROM Cons"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile(strTextName, ForReading, 0)
        
    Do While f.AtEndOfStream <> True
       strBuffer = Split(f.readline, " ")
       strValue = strBuffer(1)
      
       If strValue = "Datasource" Then
         rs.AddNew
         rs!Column = "DataSource"
         rs!Field1 = strBuffer(2)
         rs.Update
       End If
    Loop

Arrrgh...I must be missing something here. Not sure why I keep getting subscript out of range error.
Any help is much appreciated!

Thanks,
TN

 

[Remou]

Variable is good. The array starts at 0. Your sample data above would have 0 and 1 (it only has one space), so strBuffer(2) will not work.

 

[tnayfeh]

Remou,
Even with 0 and 1 it does not work.

 

[Remou]

I made a few changes, some to match with the sample data above, some for neatness, some that may make a difference! This works for me:

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Dim fs As Object
    Dim f As Object
    Dim strBuffer, strValue As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strTextName
    
    strTextName = "TestImp.txt"
    strSql = "SELECT * FROM Cons"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile(strTextName, ForReading, False)
        
    Do While f.AtEndOfStream <> True
       strBuffer = Split(f.readline, " ")
       strValue = strBuffer(0)
      
       If strValue = "Source_Data" Then
         rs.AddNew
         rs!Column = "DataSource"
         rs!Field1 = strBuffer(1)
         rs.Update
       End If
    Loop
End Sub

 

[tnayfeh]

Hi Remou,
Thanks!!! Yeah, looks like this code will work for me (sometimes). Unfortunately the file I have has more to it than just 2 columns, which is why I suspect I was getting subscript errors.

What kind of changes are needed to get this code to work if there are more than 2 columns of data, empty rows, etc.. It's a very ugly file format. The only thing that is consistent is that each column is separated by a space.

Here's an example of what I'm dealing with:
------------------------------------------------------
DataSource 429 "Map_company" Separator "," SourceType FlatFile_ColNames
CharacterSet Ansi DecimalSep " " Thousandsep " " Columns True Timing PopYesCreateDefault
Source "d:\excel\mcomp.csv" EnableMultiProcess True SetCurrent False
ServerSource False Speed False Presummarized False

DataSource 475163 "Map_centre" Separator "," SourceType FlatFile_ColNames
CharacterSet Ansi DecimalSep " " Thousandsep " " Columns True Timing PopYesCreateDefault
Source "d:\excel\mcent.csv" EnableMultiProcess True SetCurrent False
ServerSource False Speed False Presummarized False

---------------------------------------

 

[Remou]

The snippet below includes a check for blank lines, but I suspect you need something fairly hefty in the way of error checking!

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs As Object
    Dim f As Object
    Dim strBuffer As String, astrBufferArray As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strTextName
    
    strTextName = "C:\TestImp.txt"
    strSql = "SELECT * FROM Cons"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile(strTextName, ForReading, False)
        
    Do While f.AtEndOfStream <> True
    strBuffer = f.readline
        If strBuffer <> "" Then
           astrBufferArray = Split(strBuffer, " ")
           strValue = astrBufferArray(0)
          
           If strValue = "Source_Data" Then
             rs.AddNew
             rs!Column = "DataSource"
             rs!Field1 = astrBufferArray(1)
             rs.Update
           End If
        End If
    Loop