• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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?

Thanks,
0
JamesCbury
Asked:
JamesCbury
  • 8
  • 4
  • 3
  • +3
1 Solution
 
mcorrenteCommented:
of course... but wouldn't it be easier to modify your import code to account for that line?
0
 
mcorrenteCommented:
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?
0
 
mcorrenteCommented:
actually, looking at this, I'm almost positive it would be easier to modify your code.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mcorrenteCommented:
can you post an example of the exported file?
0
 
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
0
 
pique_techCommented:
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)
   
    SourceStream.SkipLine
   
    Do
        DestStream.WriteLine SourceStream.ReadLine
    Loop Until SourceStream.AtEndOfStream
   
    DestStream.Close
    SourceStream.Close
End Sub
0
 
pique_techCommented:
Whoops...
>>>Set DestStream = DestFile.OpenTextFile("C:\NewText.txt", ForWriting)
should be
Set DestStream = DestFile.OpenTextFile(DestinationFilePathName, ForWriting)
0
 
mcorrenteCommented:
>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.
0
 
mcorrenteCommented:
Can you make up a quick textfile that doesn't have any sensitive data?
0
 
mcorrenteCommented:
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)
>>>
>>>SourceStream.SkipLine
>>>
>>>Do
>>>   DestStream.WriteLine SourceStream.ReadLine
>>>Loop Until SourceStream.AtEndOfStream
>>>DestStream.close
  FileName = Dir()
Loop

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.
0
 
shanesuebsahakarnCommented:
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?
0
 
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)

Thanks
0
 
pique_techCommented:
No problem--I viewed it as more an exercise to explore what I'd do in your situation.
0
 
mcorrenteCommented:
I don't think anyone can really help you without some sort of example.
0
 
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?

Thanks
0
 
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?
0
 
moduloCommented:
PAQed with points refunded (400)

modulo
Community Support Moderator
0
 
pique_techCommented:
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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