Solved

Preping .txt files for import

Posted on 2004-10-08
20
298 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

862 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

23 Experts available now in Live!

Get 1:1 Help Now