asked on

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AkisC

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

Kev

ASKER

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

Rey Obrero (Capricorn1)

<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

Kev

ASKER

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

Rey Obrero (Capricorn1)

ok then see you in the morning. zzzzleeping time.

AkisC

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

ZuZuPetals

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!