Solved

Preping .txt files for import

Posted on 2004-10-08
20
288 Views
Last Modified: 2008-02-01
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
Comment
Question by:JamesCbury
  • 8
  • 4
  • 3
  • +3
20 Comments
 
LVL 6

Expert Comment

by:mcorrente
ID: 12258477
of course... but wouldn't it be easier to modify your import code to account for that line?
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12258484
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
 
LVL 6

Expert Comment

by:mcorrente
ID: 12258564
actually, looking at this, I'm almost positive it would be easier to modify your code.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12258569
can you post an example of the exported file?
0
 

Author Comment

by:JamesCbury
ID: 12258660
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12258758
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12258777
Whoops...
>>>Set DestStream = DestFile.OpenTextFile("C:\NewText.txt", ForWriting)
should be
Set DestStream = DestFile.OpenTextFile(DestinationFilePathName, ForWriting)
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12258796
>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
 
LVL 6

Expert Comment

by:mcorrente
ID: 12258837
Can you make up a quick textfile that doesn't have any sensitive data?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 6

Expert Comment

by:mcorrente
ID: 12258921
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12259744
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
 

Author Comment

by:JamesCbury
ID: 12259800
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12259861
No problem--I viewed it as more an exercise to explore what I'd do in your situation.
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12260025
I don't think anyone can really help you without some sort of example.
0
 

Author Comment

by:JamesCbury
ID: 12260794
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12879662
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12900932
PAQed with points refunded (400)

modulo
Community Support Moderator
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12901376
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now