Program to import multiple text files 1

[perrymans]

I have a script that outputs hundreds of individual files. The files are text, but have the extensions:

.Result
.log
.Examples

I want to create a routine that imports all of the .Result files in a folder into a table in the database.

I know how to with Excel, but not text files. I suppose I could just use Excel.Application to open each file maybe? But is that the best way?

The text file contents look like:

PDI Number: GEN000020
Finding Category: CAT II
Reference: UNIX STIG: 2.5.1.1
Description: The UNIX host is bootable in single user mode without a password.
Status: Open

For example:
GEN000020: sulogin is not in /etc/inittab.

And I am going to dump into a table like:

STIGID    Category Reference  Description   Status   Comments 
GEN000020 CAT II   UNIX STIG  The UNIX host  Open    sulogin is
                               bootable              not in
                                                     /etc/inittab

So two questions:

1) Text only or Excel opening text
2) If text only, where is a good reference for importing automatically?

Thanks. Sean.

 

[SgtJarrow]

I would recommend just using the text files. Excel will add a bunch more overhead you don't really need.

There are many examples here in the forums on how to do this but basically you need the following pseudocode:

Create a variable for each of the columns in the table
For each file
    Open the file
        Do
            Read one line
            Determine which line it is (check value before
                                        the colon)
            Put the value into the appropriate variable
            Create the statement to insert the variables
              in the correct order
              (stored procedure/module?)
            Execute the statement
        Loop until end of the file
    Close the file
Next file

All should be fairly straight forward, but I notice the last columns is actually going to retrieve values from the line after the identifier (For Example = Comments???). You may have to work a bit to get that to work out properly.

If you get stuck, let us know what part is giving you problems and what you have tried....

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer