Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1539
  • Last Modified:

Import text file into Access 2003, skip first 8 rows.

Hi,

I have a DB that is used to import text files that are produced from a corporate HR system. The issue is that these text files often contain several lines of crap before that data that I want actually starts. I have been manually deleting the header data (crap) for some time now, however, I want to be able to tell access to ignore it on the import and start at a specific line (lets say line 9). Here is the code I have so far:

Function Import_PMKEYS_AIRN()
On Error GoTo ImportError_Err
'Imports Individual Readiness data from PMKeyS Output Text File
    Call SetDBFilePath
    DoCmd.SetWarnings False

'Delete current data set
    DoCmd.RunSQL "DELETE tblpmkPersAIRN.* FROM tblpmkPersAIRN;"
'Import new data set
    DoCmd.TransferText acImportDelim, "ImportSpec_PMKeyS_AIRN_Data", "tblpmkPersAIRN", DBFilePath & "AIRN.txt", True, ""
    Form_frmMenuMain.AIRNBy.Value = Form_frmMenuMain.varCurrentUser
    Form_frmMenuMain.AIRNUpdate.Value = Now()
    MsgBox "Data Transfer Complete"
   
ImportError_Exit:
    DoCmd.SetWarnings True
    Exit Function

ImportError_Err:
    DoCmd.SetWarnings True
    MsgBox Error$
    Resume ImportError_Exit
End Function  

Any help would be greatly appreciated.

Kev
0
Kev
Asked:
Kev
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try cleaning the file first, save to a folder "C:\newFile" then import with your codes


Sub removeBadLines()
Dim fName As String, fName1 As String, s, j
fName = "C:\AIRN.txt"
fName1 = "C:\newFile\AIRN.txt"
Open fName For Input As #1
For j = 1 To 8
    Line Input #1, s
Next
Open fName1 For Output As #2
Do Until EOF(1)
Line Input #1, s

    Print #2, s

Loop
Close #1
Close #2
End Sub


0
 
AkisCCommented:
First clear the file with a function (as you did manually)

if createNewFile(DBFilePath & "AIRN.txt")
    Import_PMKEYS_AIRN()
end if

    Private Function createNewFile(ByVal thisFilePath As String) As Boolean
        Dim retStr As Boolean = False
        If System.IO.File.Exists(thisFilePath) Then
            Try
                FileClose()
                Dim F1 As Integer = FreeFile(), rLine As String = ""
                FileOpen(F1, thisFilePath, OpenMode.Input, OpenAccess.Default)
                Dim F2 As Integer = FreeFile(), cnt As Integer = 0
                FileOpen(F1, thisFilePath & ".bak", OpenMode.Output, OpenAccess.Default)
                While Not EOF(F1)
                    cnt += 1
                    If cnt > 9 Then
                        rLine = LineInput(F1)
                        Print(F2, rLine)
                        Print(F2, vbNewLine)
                    End If
                End While
                FileClose()
                F1 = Nothing : F2 = Nothing
                System.IO.File.Delete(thisFilePath)
                System.IO.File.Copy(thisFilePath & ".bak", thisFilePath)
                System.IO.File.Delete(thisFilePath & ".bak")
                retStr = True
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
        Return retStr
    End Function
0
 
KevAuthor Commented:
Hi,

I have tried both of the above with no success.

Capricorn1, I get errors like "file already open" & "Input beyond end of file"

AkisC, when I copy and past your code into a module in access, most of the text turns red, indicating there is an issue with it.

Thanks, I will keep trying the options above.

Kev
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Rey Obrero (Capricorn1)Commented:
<Capricorn1, I get errors like "file already open" & "Input beyond end of file">

did you modify the codes i posted? it is working here without errors.


upload a zip copy of your text file here  www.ee-stuff.com
0
 
KevAuthor Commented:
Capricorn1

I have not changed the code. I can not access that site from within my work firewall (Defence). I will post to it from home when I finish work (apprx 5 hours).

The text file will be changed a little to omit any sensitive data. I will also put a DB up with only the tables/modules that are required to get this to work.  

Thanks

Kev
0
 
Rey Obrero (Capricorn1)Commented:
ok then see you in the morning. zzzzleeping time.
0
 
AkisCCommented:
Sorry you were Access and I was "speaking" VB2005
replace the function with this (insert an error condition if you wish)
Private Function createNewFile(ByVal thisFilePath As String) As Boolean
        Dim retStr As Boolean
        retStr = False
            Close
            Dim F1 As Integer
            F1 = FreeFile()
            Dim rLine As String
            rLine = ""
            Open thisFilePath For Input As F1
            Dim F2 As Integer
            F2 = FreeFile()
            Dim cnt As Integer
            cnt = 0
            Open thisFilePath & ".bak" For Output As F2
            Do
                If EOF(F1) Then Exit Do
                cnt = cnt + 1
                Line Input #F1, rLine
                If cnt > 9 Then
                    Print #F2, rLine
                End If
            Loop
            Close
            Kill (thisFilePath)
            FileCopy thisFilePath & ".bak", thisFilePath
            Kill (thisFilePath & ".bak")
            retStr = True
        createNewFile = retStr
    End Function
0
 
ZuZuPetalsCommented:
In these situations, I usually allow the 8 lines to come in, but then execute a delete statement against them.  (Looks for a column that has values you can uniquely target in these first 8 junk rows).

Easier than trying to prevent them from coming in.

Good Luck!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now