Importing a file with my own specification

[nyrob609]

Hi,

I am trying to import a text file using my own Impoort Specification the DoCMD transfertext works but it gives me to many blank rows because the way the report was created. I would like access to get the file and appy the code below and so that all I get is rows where ":" is listed and import create the file. I have no training in programing I learn as I go and the code below I have from an old excel file which I think will work. I thought I will be able to do

DoCMD.TransferText, acimport, ???????? (this area where I would put save specification but how do I tell it to do code below), "TBl_Name", "H:\FileName"

Any help will be greatly appreciated

Dim TextLine1
FileN1 = FreeFile
FileN2 = FileN1 + 1
Open THISFILE For Input As #FileN1 'Open file.
Open "Insfile.txt" For Output As #FileN2 'Open file for Output
Do While Not EOF(FileN1) 'Loop unit end of file.
Line Input #FileN1, TextLine1 'Read line into variable.
CK1$ = Mid(TextLine1, 16, 1)

If CK1$ = ":" Then
Print #FileN2, TextLine1; Spc(1); TextLine2

End If
Loop
Close #FileN1 'Close File
Close #FileN2 'Close File

 

[HiTechCoach]

I regular pre-process a file before importing. It works great an also delimitations bloat in the database if you import the data into a table before you clean it up.

I would do this:

' clean up the file before importing

Dim TextLine1
FileN1 = FreeFile

Open THISFILE For Input As #FileN1 'Open file.

FileN2 = FreeFile

Open "Insfile.txt" For Output As #FileN2 'Open file for Output

Do While Not EOF(FileN1) 'Loop unit end of file.
Line Input #FileN1, TextLine1 'Read line into variable.
CK1$ = Mid(TextLine1, 16, 1)

If CK1$ = ":" Then
Print #FileN2, TextLine1; Spc(1); TextLine2

End If
Loop
Close #FileN1 'Close File
Close #FileN2 'Close File

' import the cleaned up file

DoCMD.TransferText, acimport, , "TBl_Name", "H:\FileName"

You will need to modify the code to use the correct file names

 

[Vassago]

Do this instead. Use the transfertext to import the file exactly as you do above, but THEN, run a query via vba to delete the rows where ":" is listed. It might work much better than stepping through the file like you wish to above.