New Member (1 Viewer)

wmsalamanca

New member
Local time
Today, 18:22
Joined
May 27, 2014
Messages
8
Presently used MS to do file comparison by creating queries. MY objective is to create a vba code that will be able to filter data from the file before importing to a table. Example importing a file with a million records, with a field indicator for the type of record. The indicator values are A and B, and I will like to only import only the A type records.
 

Jon

Access World Site Owner
Staff member
Local time
Today, 23:22
Joined
Sep 28, 1999
Messages
4,067
Welcome to the forums!

To help us better understand your needs, I would be very grateful if you could complete this 2 minute survey: Click here for the survey

All surveys are anonymous and not linked to your account.

I want to keep this the very best place for Access and your feedback helps us!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:22
Joined
Oct 29, 2018
Messages
13,680
Hi. Welcome to AWF!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:22
Joined
Feb 28, 2001
Messages
18,680
@wmsalamanca - please present an overview of what those input files look like and at least a summary of how they get to windows. However, do this in a thread under General or VBA, either one. In order for us to give you good advice, we need a decent picture of the resources on which you are working.
 

wmsalamanca

New member
Local time
Today, 18:22
Joined
May 27, 2014
Messages
8
@wmsalamanca - please present an overview of what those input files look like and at least a summary of how they get to windows. However, do this in a thread under General or VBA, either one. In order for us to give you good advice, we need a decent picture of the resources on which you are working.
I copy text files from the server to my desktop. I'm using windows 7 and 2010 msaccess. the files are pipe delimiter. Sorry I'm new to using forum and I was not able to find a thread General or VBA.

CorpID|EmpID|GrantNum|GrantDate|Grant/Award|Granted
100|9996532|45687|27721|G|1000
100|9997214|78957|33261|A|100
 

arnelgp

error reading drive A:
Local time
Tomorrow, 07:22
Joined
May 7, 2009
Messages
11,110
Code:
''''''''''''''''''''''''
'arnelgp
'
'note:
'
'structure of table/textfile must be the same
'
Public Function TextFileToTable(ByVal pstrTextFile As String, ByVal pstrTable As String, Optional ByVal delim As String = ",")
    Dim arrColumn() As String
    Dim db As DAO.Database
    Dim rs As DAO.recordSet
    Dim intFile As Integer
    Dim strData As String
    Dim i As Integer
    Dim var As Variant
    Dim varValue As Variant
    
    '* check if the textfile exists
    If Len(Dir(pstrTextFile)) = 0 Then
        Exit Function
    End If
    '* open the table
    Set db = CurrentDb
    If InStr(pstrTable, "SELECT ") > 0 Then
        Set rs = db.OpenRecordset( _
            "SELECT * FROM (" & pstrTable & ") " & _
            "WHERE (1=0);", dbOpenDynaset)
    Else
        Set rs = db.OpenRecordset( _
            "SELECT * FROM [" & pstrTable & "] " & _
            "WHERE (1=0);", dbOpenDynaset)
    End If
    Set db = Nothing
    intFile = FreeFile
    '* open the text file
    Open pstrTextFile For Input As #intFile
    '* assume that the first line on text file is the header
    Input #intFile, strData
    var = Split(strData, delim)
    '* save the header to dictionary
    ReDim arrColumn(0 To UBound(var))
    For i = 0 To UBound(var)
        arrColumn(i) = var(i)
    Next
    '* step through each line in textfile
    Do Until EOF(intFile)
        Input #intFile, strData
        var = Split(strData, delim)
        rs.AddNew
        For i = 0 To UBound(var)
            Select Case rs.fields(arrColumn(i)).Type
            Case dbByte
                varValue = CByte(var(i))
            Case dbInteger
                varValue = CInt(var(i))
            Case dbSingle
                varValue = CSng(var(i))
            Case dbDouble
                varValue = CDbl(var(i))
            Case dbLong
                 varValue = CLng(var(i))
            Case dbDate
                varValue = CDate(var(i))
            Case dbCurrency, dbDecimal
                varValue = CCur(var(i))
            Case Else
                varValue = var(i)
            End Select
            rs.fields(arrColumn(i)) = varValue
        Next
        rs.Update
    Loop
    Close #intFile
    rs.Close
    Set rs = Nothing
    Erase var
    Erase arrColumn
End Function
 

wmsalamanca

New member
Local time
Today, 18:22
Joined
May 27, 2014
Messages
8
Code:
''''''''''''''''''''''''
'arnelgp
'
'note:
'
'structure of table/textfile must be the same
'
Public Function TextFileToTable(ByVal pstrTextFile As String, ByVal pstrTable As String, Optional ByVal delim As String = ",")
    Dim arrColumn() As String
    Dim db As DAO.Database
    Dim rs As DAO.recordSet
    Dim intFile As Integer
    Dim strData As String
    Dim i As Integer
    Dim var As Variant
    Dim varValue As Variant
   
    '* check if the textfile exists
    If Len(Dir(pstrTextFile)) = 0 Then
        Exit Function
    End If
    '* open the table
    Set db = CurrentDb
    If InStr(pstrTable, "SELECT ") > 0 Then
        Set rs = db.OpenRecordset( _
            "SELECT * FROM (" & pstrTable & ") " & _
            "WHERE (1=0);", dbOpenDynaset)
    Else
        Set rs = db.OpenRecordset( _
            "SELECT * FROM [" & pstrTable & "] " & _
            "WHERE (1=0);", dbOpenDynaset)
    End If
    Set db = Nothing
    intFile = FreeFile
    '* open the text file
    Open pstrTextFile For Input As #intFile
    '* assume that the first line on text file is the header
    Input #intFile, strData
    var = Split(strData, delim)
    '* save the header to dictionary
    ReDim arrColumn(0 To UBound(var))
    For i = 0 To UBound(var)
        arrColumn(i) = var(i)
    Next
    '* step through each line in textfile
    Do Until EOF(intFile)
        Input #intFile, strData
        var = Split(strData, delim)
        rs.AddNew
        For i = 0 To UBound(var)
            Select Case rs.fields(arrColumn(i)).Type
            Case dbByte
                varValue = CByte(var(i))
            Case dbInteger
                varValue = CInt(var(i))
            Case dbSingle
                varValue = CSng(var(i))
            Case dbDouble
                varValue = CDbl(var(i))
            Case dbLong
                 varValue = CLng(var(i))
            Case dbDate
                varValue = CDate(var(i))
            Case dbCurrency, dbDecimal
                varValue = CCur(var(i))
            Case Else
                varValue = var(i)
            End Select
            rs.fields(arrColumn(i)) = varValue
        Next
        rs.Update
    Loop
    Close #intFile
    rs.Close
    Set rs = Nothing
    Erase var
    Erase arrColumn
End Function


Thank you for the code. As I mention I'm just learning VBA. I understand some of the code. For example I know this code is for a comma delimiter file and that case statement is where the fields are being populated. I kind of understand the if statement, the one thing that is throwing me off is the 1=0, in my case this is where I will change the Grant/Award = 'G'?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:22
Joined
Feb 28, 2001
Messages
18,680
The use of "1=0" in a query is to turn off output. It is a query that opens (as a recordset) but doesn't attempt to retrieve data. If all you are going to do is append but the table is not initially empty, there is no need to waste time returning data that you wont' read anyway.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 07:22
Joined
May 7, 2009
Messages
11,110
the code i gave is a Utility to save the text file to an existing table (with same structure).
if your textfile is delimited with "normal" delimiter (, <tab>, ; <space>), then it would be
easy to just create a Link table from the file.

but since you are using "|" as delimiter, you will find it hard to import your data using
ms access Import wizard.

first create the Table (temporary) with same structure as with your textfile if it does not already exists.
use the function to import the data.

then create a Query that will import it to another table (only with indicator "A")
 

Users who are viewing this thread

Top Bottom