Preping .txt files for import

Hi All,

I run a utility that outputs reports in a tab delimited .txt format.  It includes the column headers, but it also includes one line of text with the utility name and date.  In order to use the import macro that I set up in access by saving my import spec I need to delete out that first line of text.

Is there a way that write a module in VBA that can open each .txt file in a specified directory and delete the first line?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

of course... but wouldn't it be easier to modify your import code to account for that line?
I'll post the code, but quick question: is the line always just one line, or is it a set length, or does it change?
actually, looking at this, I'm almost positive it would be easier to modify your code.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

can you post an example of the exported file?
JamesCburyAuthor Commented:
The text is always the first line of the document, it is not a specific length.
I tired to get Access to ignore the first line, and take the second line as the column headers, but I couldn't figure it out... That solution would also work.

I'm going to add another twist... I normally have about 12 files to import, each is has the same table format, but I rename then the name of the file.  I then run a few queries that I have set up to determine the differences in the tables.  Is there a way that I can import all the tables at once?

Sorry, but I can't post the files... they are the output of a clients Active Directory user settings
A conceptual suggestion that seems to work with minimal testing:  open the source file, skip the first line, write all the other lines to a different file (then run your import from the second file):

There are probably a thousand reasons why this is not optimal, but I started working on it before others responded and at least I learned a thing or two.  FWIW.  ; )

Public Sub HandleTextfile(SourceFilePathName as String, DestinationFilePathName as String)

    Dim SourceFile As Scripting.FileSystemObject:  Set SourceFile = New Scripting.FileSystemObject
    Dim DestFile As Scripting.FileSystemObject: Set DestFile = New Scripting.FileSystemObject
    Dim SourceStream As Scripting.TextStream
    Dim DestStream As Scripting.TextStream
    Set DestFile = New Scripting.FileSystemObject
    DestFile.CreateTextFile (DestinationFilePathName)
    Set SourceStream = SourceFile.OpenTextFile(SourceFilePathName)
    Set DestStream = DestFile.OpenTextFile("C:\NewText.txt", ForWriting)
        DestStream.WriteLine SourceStream.ReadLine
    Loop Until SourceStream.AtEndOfStream
End Sub
>>>Set DestStream = DestFile.OpenTextFile("C:\NewText.txt", ForWriting)
should be
Set DestStream = DestFile.OpenTextFile(DestinationFilePathName, ForWriting)
>I normally have about 12 files to import, each is has the same table format, but I >rename then the name of the file.  I then run a few queries that I have set up to >determine the differences in the tables.  Is there a way that I can import all the tables at >once?

I'm not sure what you mean here.  Determine what differences in the tables?  What do you mean import tables?  Are you talking about the data in your export files?

People would disagree with me, but I find that the best thing to do in these situations is to import the files using code.  Without an example it would be tough to show you how to do this.
Can you make up a quick textfile that doesn't have any sensitive data?
pique's code might work, but it's an extra step that probably doesn't need to be taken.  I'm not sure how practiced you are with code, but basically he's creating a new file that contains all of the old file except the first line.  If you would like to use it, just put it in a loop to cycle through the directory where you save the initial files.

dim tmpDirectory as string

FileName = Dir(tmpDirectory)
Do until FileName=""
>>>Set SourceStream = SourceFile.OpenTextFile(tmpDirectory & FileName)
>>>Set DestStream = DestFile.OpenTextFile("C:\NewText.txt", ForWriting)
>>>   DestStream.WriteLine SourceStream.ReadLine
>>>Loop Until SourceStream.AtEndOfStream
  FileName = Dir()

Be sure to include his declarations at the start of the code.

If you accept this as an answer, be sure to split points, as everything next to carrots is pique's code.
How about importing the file into a temporary table (or linking to the text file) and then running an append query that only appends data from record 2 onwards into your real table?
JamesCburyAuthor Commented:
I would rather not create a seperate temp file as I am trying to keep this as simple (and small) as possible. This code also dosen't help me import all the tables to Access (sorry pique, I know that I added that after the fact)

No problem--I viewed it as more an exercise to explore what I'd do in your situation.
I don't think anyone can really help you without some sort of example.
JamesCburyAuthor Commented:
I'm not sure how to attach a sample file to this question, but my .txt files are very simple.

they go something like this:

10/8/2004 12:51 PM - Somarsoft DumpSec (formerly DumpAcl) - \\pr_mf_06
UserName      Groups      GroupComment      GroupType      FullName      AccountType      Comment            
Anon000      Anonymous      Users granted anonymous access to the computer.      Local            User      Built-in account for anonymous access to the computer                                    

The first line is the one that I need deleted, the second line contains the column headers (tab delimited) and the third and all the succeding lines contain the data (also tab delimited).  **Note: I did not include all the columns for this example, there are 27 total columns.

Does this help?

Steve BinkCommented:
Do not accept this as an answer.

pique_tech's solution will work admirably, and unless someone knows of a way to just delete the first line (as opposed to not writing the first line), it is probably the most efficiency you'll see out of a routine like this.  Also, it can be easily adapted to run the same import for 12 (or more) tables by calling it from a loop.

I still kind of like mcorrente's solution, though I don't know if it applies...CAN you change the way the utility outputs the file?
PAQed with points refunded (400)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I am curious why routinet's recommended disposition was not accepted by modulo.  I did provide a solution that would work, as pointed out by routinet, and there was no feedback about why that solution was unacceptable.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.