asked on

Need to parse 100 csv files into one usable excel document or access database for review

Hi,

I ran Dumpsec on around 100 servers. The results were sent to an individual csv file (servernamedriveletter).
I need some sort of script to take 100 .csv files and parse all the data into 1 excel spreadsheet, and place it in a way that I can easily review it for the details for each server. Also, when I have it in on sheet, I really need a format so that I can choose one server or multiple.
I believe I willl need multiple worksheets also, as there is well over thr 65k line of an excel sheet. Maybe an access database with a frontend? maybe a macro? you guys are the experts help please

This is a snippit from one of the reports:
\\ServeName\d$\DHCP list\ ServeName\Administrators o all all
\\ServeName\d$\DHCP list\ SYSTEM all all
\\ServeName\d$\DHCP list\ ServeName\Administrators o all
\\ServeName\d$\DHCP list\ CREATOR OWNER all
\\ServeName\d$\DHCP list\ ServeName\Users R X R X
\\ServeName\d$\DHCP list\ ServeName\Users ax00000004
\\ServeName\d$\DHCP list\ ServeName\Users ax00000002

\\ServeName\d$\Install\ ServeName\Administrators o all all
\\ServeName\d$\Install\ SYSTEM all all
\\ServeName\d$\Install\ ServeName\Administrators o all
\\ServeName\d$\Install\ CREATOR OWNER all
\\ServeName\d$\Install\ ServeName\Users R X R X
\\ServeName\d$\Install\ ServeName\Users ax00000004
\\ServeName\d$\Install\ ServeName\Users ax00000002

Paolo Santiangeli

Hi,
have a look to this link:
http://72.32.210.42/board/Forum10/HTML/000239.html

ASKER

I have all the reports done. Now I have 100 csv files I need to parse

Zach

Paolo Santiangeli

I found this code on Internet, i hope it will be useful as a little ms access function

Dim InputDir, ImportFile As String, tblName As String
 
InputDir = "c:\SOURCEFOLDER\"
 
ImportFile = Dir(InputDir & "\*.csv")
 
Do While Len(ImportFile) > 0
 
tblName = "MY TABLE NAME" 'This is to import all files into one table.
 
DoCmd.TransferText acImportDelim, , tblName, InputDir & ImportFile, True
 
ImportFile = Dir
Loop

Open in new window

Can you show us a snippet from one of the csv files?

Paolo Santiangeli

send me a sample file with some row of data

ASKER

I am attaching a file with a sample of the data.
The files are really a csv, but i had t change it to xls to upload here.

Again all files have the sameing naming convention of servername(followed by drive letter) for example server_c$

Thanks for the help
servername.xls

The file that you uploaded was an Excel spreadsheet, not a text/csv file.

Try right clicking the file, do an Open With and select Notepad to edit it, then change the extension to txt to upload.

After we get a good copy of the file, then we can work a routine out that can import them into an Access DB easily.

ASKER

Hi,

Ok so here is the CSV as a txt.
But all my reports are CSV's I dont know if that matters

Thanks

servername.txt

>> But all my reports are CSV's I dont know if that matters

All that the CSV extension does is indicates it is a text file with a comma as the delimiter between columns as opposed to semi-colons, tabs or some other character.

A CSV or text file is easy to parse directly into Access (or other office app) using VB/VBA. Excel you have to handle differently.

From here we can work with it.

ASKER

So the file I attached is ok?

Yes. I've been busy with my real job. And am having a hard time to getting back to this.

Check out the code below. I'm breaking it into 2 functions. It makes it easier to control the inner and outer loops.

Public Function Import_Dumpsec_Files(FilePathAndName As String)
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
 
Dim StringArray(5) As String
 
Dim FileOwner As Boolean
Dim DirRights As String
Dim FileRights As String
 
Dim I As Integer
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
'Table Desc below
'Index_Num   PathName    User_Acct   Owner   Dir_Rights  File_Rights
' AutoNum    Text field  Text field  Yes/No  Text field  Text field
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Dumpsec_Data")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = FilePathAndName        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
 
Line Input #FileNum, InputString     '<--- To throw away the header line
Line Input #FileNum, InputString     '<--- To throw away the 2nd header line
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString      'Read the data in
    If Trim(InputString) <> "" Then
    StringArray() = Split(InputString, ",")
    If Trim(Left(StringArray(2), 2)) = "o" Then FileOwner = True Else FileOwner = False
    DirRights = Trim(Mid(StringArray(2), 4, 10))
    FileRights = Trim(Mid(StringArray(2), 15, 10))
 
    With RS                                 'the input is an empty string write it
        .AddNew
        !PathName = StringArray(0)
        !User_Acct = StringArray(1)
        !Owner = FileOwner
        !Dir_Rights = DirRights
        !File_Rights = FileRights
        .Update
    End With
    I = 0
    For I = 0 To 4
        StringArray(I) = ""
    Next I
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function
'-------------------------------------------------------
Public Function RunAllInDir()
 
Dim FilesPath As String
Dim FileName As String
 
FilesPath = "C:\MyDir\"
 
FileName = Dir(FilesPath & "*.csv")
 
Do While FileName <> ""
    Call Import_Dumpsec_Files(FilesPath & FilesName)
    FileName = Dir
Loop
 
End Function

Open in new window

ASKER

Thanks for getting back to it.

This is going to sound real dumb, but how do I use this code?
Where do I put it.

Also folder path and name.. is that the folder will all the csv files i have?

Thanks
...

Create a table -- I'm using Dumpsec_Data for the table name -- in Access as described below.

Then go to the Modules tab and create a new module and copy the code into it.

Then on the RunAllInDir function change the FilesPath = "C:\MyDir\" to the folder will all the csv files i have.


'Index_Num   PathName    User_Acct   Owner   Dir_Rights  File_Rights
' AutoNum    Text field  Text field  Yes/No  Text field  Text field

Open in new window

ASKER

Ok
then I saved the module...

How do I run it?

Put your cursor somewhere in the RunAllInDir function and then hit the <F5> key.

ASKER

nothing shows in the macro box

ASKER

when i do it, and hit f5 in the rundll one nothign happens

ASKER

I tried again..

and got an error

compile error user defined type not defined
Dim I As Integer

Dim DB As Database

I'm using DAO not ADO so your references are off.

In the module/VB window go to Tools --> References and de-select anything that says Microsoft ActiveX Data Objects. Then Go down the list and find highest numbers Microsoft DAO #.# Object Library (should be 3.6) and check it on, and try it again.

ASKER

Ok did that
now I get,

Cant assign to array
StringArray() = Split(InputString, ",")

Try changing the code like this:

    Line Input #FileNum, InputString      'Read the data in
    If Trim(InputString) <> "" Then
    Debug.Print InputString
    If Instr(1, InputString,",")> 0 Then    
          StringArray() = Split(InputString, ",")
    ELSE 
          StringArray(0) = InputString
          StringArray(1) = " "
          StringArray(2) = " "
          StringArray(3) = " "
          StringArray(4) = " "
    End if
    If Trim(Left(StringArray(2), 2)) = "o" Then FileOwner = True Else FileOwner = False

Open in new window

I'm running A2003 and have to first Dim StringArray as Variant then make the assignment:

StringArray = Split(InputString, ",")

If I use StringArray() = Split(InputString, ",") it throws an error - subscript out of range.

ASKER

I get a compile error

cant assign to array

StringArray() =

Change line 7: Dim StringArray as Variant
Change line 39: StringArray = Split(InputString, ",")

You cannot use an existing array with the Split function. It needs to create its own Variant type array so StringArray must be dimensioned as per my modified line 7 above.

ASKER

ok,

Now I get a compile error
Loop without do

>> You cannot use an existing array with the Split function......

Thanks Ray.

Can you repost the code?

ASKER

The code you gave me with the adjustments is below

Thanks

Public Function Import_Dumpsec_Files(FilePathAndName As String)
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
 
Dim StringArray As variant 
Dim FileOwner As Boolean
Dim DirRights As String
Dim FileRights As String
 
Dim I As Integer
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
'Table Desc below
'Index_Num   PathName    User_Acct   Owner   Dir_Rights  File_Rights
' AutoNum    Text field  Text field  Yes/No  Text field  Text field
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Dumpsec_Data")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = FilePathAndName        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
 
Line Input #FileNum, InputString     '<--- To throw away the header line
Line Input #FileNum, InputString     '<--- To throw away the 2nd header line
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString      'Read the data in
    If Trim(InputString) <> "" Then
    StringArray = Split(InputString, ",")
    If Trim(Left(StringArray(2), 2)) = "o" Then FileOwner = True Else FileOwner = False
    DirRights = Trim(Mid(StringArray(2), 4, 10))
    FileRights = Trim(Mid(StringArray(2), 15, 10))
 
    With RS                                 'the input is an empty string write it
        .AddNew
        !PathName = StringArray(0)
        !User_Acct = StringArray(1)
        !Owner = FileOwner
        !Dir_Rights = DirRights
        !File_Rights = FileRights
        .Update
    End With
    I = 0
    For I = 0 To 4
        StringArray(I) = ""
    Next I
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function
'-------------------------------------------------------
Public Function RunAllInDir()
 
Dim FilesPath As String
Dim FileName As String
 
FilesPath = "C:\MyDir\"
 
FileName = Dir(FilesPath & "*.csv")
 
Do While FileName <> ""
    Call Import_Dumpsec_Files(FilesPath & FilesName)
    FileName = Dir
Loop
 
End Function
 
Open in New Window Accept Multiple Solutions Accept as Solution

Open in new window

Where's the End If for line 37?

Okay, caught it now. This is all aircode so I see where I messed up.

Missed an End If statement for the loop.

>> You cannot use an existing array with the Split function.

You can't use a defined one with a pre-listed amount of variables.

Public Function Import_Dumpsec_Files(FilePathAndName As String)
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
 
Dim StringArray() As String
 
Dim FileOwner As Boolean
Dim DirRights As String
Dim FileRights As String
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
'Table Desc below
'Index_Num   PathName    User_Acct   Owner   Dir_Rights  File_Rights
' AutoNum    Text field  Text field  Yes/No  Text field  Text field
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Dumpsec_Data")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = FilePathAndName        '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
 
Line Input #FileNum, InputString     '<--- To throw away the header line
Line Input #FileNum, InputString     '<--- To throw away the 2nd header line
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString      'Read the data in
    Debug.Print InputString
    If Trim(InputString) <> "" Then
        StringArray() = Split(InputString, ",")
        If Trim(Left(StringArray(2), 2)) = "o" Then FileOwner = True Else FileOwner = False
        DirRights = Trim(Mid(StringArray(2), 4, 10))
        FileRights = Trim(Mid(StringArray(2), 15, 10))
    
        With RS
            .AddNew
            !PathName = StringArray(0)
            !User_Acct = StringArray(1)
            !Owner = FileOwner
            !Dir_Rights = DirRights
            !File_Rights = FileRights
            .Update
        End With
    End If
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function

Open in new window

ASKER

So I just copied and pasted the code above to a module
There is no RunAllInDir function now

The RunAllInDir didn't change -- You need to grab that back again.

>> Where's the End If for line 37?

Yes -- I missed it causing the loop without do.

ASKER

Ok so this is the code I am using

I click on runallindir and click f5 and nothing is happening now.

I check the table nothing created

Public Function Import_Dumpsec_Files(FilePathAndName As String)
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
 
Dim StringArray() As String
 
Dim FileOwner As Boolean
Dim DirRights As String
Dim FileRights As String
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
'Table Desc below
'Index_Num   PathName    User_Acct   Owner   Dir_Rights  File_Rights
' AutoNum    Text field  Text field  Yes/No  Text field  Text field
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Dumpsec_Data")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = "c:\ntfs\scans"       '<-- Change to your folder and path
Open InputFile For Input Access Read Shared As #FileNum
 
 
Line Input #FileNum, InputString     '<--- To throw away the header line
Line Input #FileNum, InputString     '<--- To throw away the 2nd header line
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString      'Read the data in
    Debug.Print InputString
    If Trim(InputString) <> "" Then
        StringArray() = Split(InputString, ",")
        If Trim(Left(StringArray(2), 2)) = "o" Then FileOwner = True Else FileOwner = False
        DirRights = Trim(Mid(StringArray(2), 4, 10))
        FileRights = Trim(Mid(StringArray(2), 15, 10))
    
        With RS
            .AddNew
            !PathName = StringArray(0)
            !User_Acct = StringArray(1)
            !Owner = FileOwner
            !Dir_Rights = DirRights
            !File_Rights = FileRights
            .Update
        End With
    End If
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function
 
'-------------------------------------------------------
Public Function RunAllInDir()
 
Dim FilesPath As String
Dim FileName As String
 
FilesPath = "C:\NTFS\Scans"
 
FileName = Dir(FilesPath & "*.csv")
 
Do While FileName <> ""
    Call Import_Dumpsec_Files(FilesPath & FilesName)
    FileName = Dir
Loop
 
End Function

Open in new window

Change this --
FilesPath = "C:\NTFS\Scans"
to this --
FilesPath = "C:\NTFS\Scans\"
on line 67.

Without the trailing backslash it is essentially doing

DIR C:\NTFS\Scans*.csv

ASKER

Now is it saysing path not found

Open InputFile For Input Access Read Shared As #FileNum

What happens if you type - RunAllInDir - in the Immediate Pane and press Enter. At line 72 before the call, insert:

MsgBox "About to Call"

At Line 38 insert:

MsgBox "About to Split"

Use the Msgbox to troubleshoot the code. In addition you can click on the left side of the code window on the line where you want the code to break. This will put a small red circle on the vertical bar beside the line. When you run the code, the code will pause at that location and you can examine all the variables.

ASKER

I dont know what I would be looking for

Change line 27 to:
InputFile = FilePathAndName '<--- This is coming from the Input of the function

I had a comment left in from some other code where I was doing something similar.

ASKER

Getting a path not founf
Open InputFile For Input Access Read Shared As #FileNum

Below os the code

Option Compare Database
 
Public Function Import_Dumpsec_Files(FilePathAndName As String)
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
 
Dim StringArray() As String
 
Dim FileOwner As Boolean
Dim DirRights As String
Dim FileRights As String
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
'Table Desc below
'Index_Num   PathName    User_Acct   Owner   Dir_Rights  File_Rights
' AutoNum    Text field  Text field  Yes/No  Text field  Text field
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Dumpsec_Data")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = FilePathAndName          '<--- This is coming from the Input of the function
Open InputFile For Input Access Read Shared As #FileNum
 
 
Line Input #FileNum, InputString     '<--- To throw away the header line
Line Input #FileNum, InputString     '<--- To throw away the 2nd header line
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString      'Read the data in
    Debug.Print InputString
    If Trim(InputString) <> "" Then
        StringArray() = Split(InputString, ",")
        If Trim(Left(StringArray(2), 2)) = "o" Then FileOwner = True Else FileOwner = False
        DirRights = Trim(Mid(StringArray(2), 4, 10))
        FileRights = Trim(Mid(StringArray(2), 15, 10))
    
        With RS
            .AddNew
            !PathName = StringArray(0)
            !User_Acct = StringArray(1)
            !Owner = FileOwner
            !Dir_Rights = DirRights
            !File_Rights = FileRights
            .Update
        End With
    End If
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function
 
'-------------------------------------------------------
Public Function RunAllInDir()
 
Dim FilesPath As String
Dim FileName As String
 
FilesPath = "C:\NTFS\Scans\"
 
FileName = Dir(FilesPath & "*.csv")
 
Do While FileName <> ""
    Call Import_Dumpsec_Files(FilesPath & FilesName)
    FileName = Dir
Loop
 
End Function

Open in new window

There was a disconnect on line 71 to line 74 with the file name variable.

Try this code:

Option Compare Database
Option Explicit
 
Public Function Import_Dumpsec_Files(FilePathAndName As String)
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
 
Dim StringArray() As String
 
Dim FileOwner As Boolean
Dim DirRights As String
Dim FileRights As String
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
'Table Desc below
'Index_Num   PathName    User_Acct   Owner   Dir_Rights  File_Rights
' AutoNum    Text field  Text field  Yes/No  Text field  Text field
 
'The dumpsec file outputs in a mixed comma-delimited/fixed width _
 format. The fields are are path/filename, the owner, then the _
 rights are a fixed width colums.
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Dumpsec_Data")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = FilePathAndName          '<--- This is coming from the Input of the function
Debug.Print "Reading: " & InputFile
Open InputFile For Input Access Read Shared As #FileNum
 
 
Line Input #FileNum, InputString     '<--- To throw away the header line
Line Input #FileNum, InputString     '<--- To throw away the 2nd header line
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString      'Read the data in
    Debug.Print InputString
    If Trim(InputString) <> "" Then
        StringArray() = Split(InputString, ",")
        If Trim(Left(StringArray(2), 2)) = "o" Then FileOwner = True Else FileOwner = False
        DirRights = Trim(Mid(StringArray(2), 4, 10))
        FileRights = Trim(Mid(StringArray(2), 15, 10))
    
        With RS
            .AddNew
            !PathName = Trim(StringArray(0))
            !User_Acct = Trim(StringArray(1))
            !Owner = FileOwner
            !Dir_Rights = DirRights
            !File_Rights = FileRights
            .Update
        End With
    End If
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function
 
'-------------------------------------------------------
Public Function RunAllInDir()
 
Dim FilesPath As String
Dim FileName As String
 
FilesPath = "C:\NTFS\Scans\"
 
FileName = Dir(FilesPath & "*.csv")
 
Do While FileName <> ""
    Debug.Print "RunAll Path: " & FilesPath & FileName
    Call Import_Dumpsec_Files(FilesPath & FileName)
    FileName = Dir
Loop
 
End Function

Open in new window

ASKER

Hi,

I get subscript out of range

If Trim(Left(StringArray(2), 2)) = "o" Then

Hit Ctrl + G and you should see a pane pop up that says Immediate in the title.

Can you copy and paste the last line that is in there?

ASKER

RunAll Path: C:\NTFS\Scans\server1_c$.csv
Reading: C:\NTFS\Scans\server1_c$.csv

\\server1\c$\

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

ASKER

This is what I am putting in for teh code now and getting errrors..

did I put somthing in the wrong place?

option Compare Database
 
 
Option Explicit
 
Public Function Import_Dumpsec_Files(FilePathAndName As String)
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
 
Dim StringArray() As String
 
Dim FileOwner As Boolean
Dim DirRights As String
Dim FileRights As String
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
'Table Desc below
'Index_Num   PathName    User_Acct   Owner   Dir_Rights  File_Rights
' AutoNum    Text field  Text field  Yes/No  Text field  Text field
 
'The dumpsec file outputs in a mixed comma-delimited/fixed width _
 format. The fields are are path/filename, the owner, then the _
 rights are a fixed width colums.
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Dumpsec_Data")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = FilePathAndName          '<--- This is coming from the Input of the function
Debug.Print "Reading: " & InputFile
Open InputFile For Input Access Read Shared As #FileNum
 
 
Line Input #FileNum, InputString     '<--- To throw away the header line
Line Input #FileNum, InputString     '<--- To throw away the 2nd header line
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString      'Read the data in
    Debug.Print InputString
      If Trim(InputString) <> "" Then
        StringArray() = Split(InputString, ",")
        If UBound(StringArray() =>2 Then
            If Trim(Left(StringArray(2), 2)) = "o" Then FileOwner = True Else FileOwner = False
             DirRights = Trim(Mid(StringArray(2), 4, 10))
             FileRights = Trim(Mid(StringArray(2), 15, 10))
        Else
             FileOwner = False
             DirRights = ""
             FileRights = ""
        End If
 
        With RS
            .AddNew
            !PathName = Trim(StringArray(0))
            !User_Acct = If UBound(StringArray() => 1 Then  Trim(StringArray(1)) Else ""
            !Owner = FileOwner
            !Dir_Rights = DirRights
            !File_Rights = FileRights
            .Update
        End With
    End If
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function
 
'-------------------------------------------------------
Public Function RunAllInDir()
 
Dim FilesPath As String
Dim FileName As String
 
FilesPath = "C:\NTFS\Scans\"
 
FileName = Dir(FilesPath & "*.csv")
 
Do While FileName <> ""
    Debug.Print "RunAll Path: " & FilesPath & FileName
    Call Import_Dumpsec_Files(FilesPath & FileName)
    FileName = Dir
Loop
 
End Function

Open in new window

In lines 12 and 48 you are back to making the same mistake you made at the beginning.

ASKER

Made the canges to the string array with the code below

If UBound(StringArray() =>2 Then

and

!User_Acct = If UBound(StringArray() => 1 Then Trim(StringArray(1)) Else ""

are in red -- syntax error

option Compare Database
 
 
Option Explicit
 
Public Function Import_Dumpsec_Files(FilePathAndName As String)
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
 
Dim StringArray as Variant
 
Dim FileOwner As Boolean
Dim DirRights As String
Dim FileRights As String
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
'Table Desc below
'Index_Num   PathName    User_Acct   Owner   Dir_Rights  File_Rights
' AutoNum    Text field  Text field  Yes/No  Text field  Text field
 
'The dumpsec file outputs in a mixed comma-delimited/fixed width _
 format. The fields are are path/filename, the owner, then the _
 rights are a fixed width colums.
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Dumpsec_Data")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = FilePathAndName          '<--- This is coming from the Input of the function
Debug.Print "Reading: " & InputFile
Open InputFile For Input Access Read Shared As #FileNum
 
 
Line Input #FileNum, InputString     '<--- To throw away the header line
Line Input #FileNum, InputString     '<--- To throw away the 2nd header line
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString      'Read the data in
    Debug.Print InputString
      If Trim(InputString) <> "" Then
        StringArray = Split(InputString, ",")
        If UBound(StringArray() =>2 Then
            If Trim(Left(StringArray(2), 2)) = "o" Then FileOwner = True Else FileOwner = False
             DirRights = Trim(Mid(StringArray(2), 4, 10))
             FileRights = Trim(Mid(StringArray(2), 15, 10))
        Else
             FileOwner = False
             DirRights = ""
             FileRights = ""
        End If
 
        With RS
            .AddNew
            !PathName = Trim(StringArray(0))
            !User_Acct = If UBound(StringArray() => 1 Then  Trim(StringArray(1)) Else ""
            !Owner = FileOwner
            !Dir_Rights = DirRights
            !File_Rights = FileRights
            .Update
        End With
    End If
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function
 
'-------------------------------------------------------
Public Function RunAllInDir()
 
Dim FilesPath As String
Dim FileName As String
 
FilesPath = "C:\NTFS\Scans\"
 
FileName = Dir(FilesPath & "*.csv")
 
Do While FileName <> ""
    Debug.Print "RunAll Path: " & FilesPath & FileName
    Call Import_Dumpsec_Files(FilesPath & FileName)
    FileName = Dir
Loop
 
End Function

Open in new window

When you create an array as you have done, StringArray, you cannot then refer to it as StringArray()

You can only use the paraentheses when referring to a specifiic element of the array. Thus Line49:

If UBound(StringArray) =>2 Then

ASKER

ok i made that change.. Line 62 is still syntax problem

!User_Acct = If UBound(StringArray() => 1 Then Trim(StringArray(1)) Else ""

Are you not listening to anything I am trying to tell you?

!User_Acct = If UBound(StringArray() => 1 Then Trim(StringArray(1)) Else ""
--------------------------------------------^-what are these?---------------^-you need it here because you are referring to a specific element of the array

ASKER

Im sorry,

I just dont know anything about VB

i tried to make it this:
!User_Acct = If UBound(StringArray) => 1 Then Trim(StringArray) Else ""

Still bad syntax... sorry i really just dont understand vb

ASKER

Then I try this and get compile error
Expected:expression
!User_Acct = If UBound(StringArray() => 1 Then Trim(StringArray(1)) Else ""

<off-topic>
We want EE to be a learning experience for the asker, not just a write me a solution that the asker doesn't really understand.

I know you are in a rush, but sometimes slowing down and applying some logic will make the VB more understandable.
</off-topic>

Try it as:

!User_Acct = If UBound(StringArray) => 1 Then  Trim(StringArray(1)) Else ""
'the ubound need to be run  ^^^^         the assignment ^^^^
'without parentheses                     if valid needs the 
'                                        array number it is reading.

Open in new window

ASKER

I tried doing that ..

I get Compile error
expected"expression

ASKER

Hi,

I posted the code below with the changes

!User_Acct = If UBound(StringArray) => 1 Then Trim(StringArray(1)) Else "" is still red with a compile error: syntax error

thanks for helping me with this, I really appreciate it

option Compare Database
 
 
Option Explicit
 
Public Function Import_Dumpsec_Files(FilePathAndName As String)
 
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String
 
Dim StringArray as Variant
 
Dim FileOwner As Boolean
Dim DirRights As String
Dim FileRights As String
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
'Table Desc below
'Index_Num   PathName    User_Acct   Owner   Dir_Rights  File_Rights
' AutoNum    Text field  Text field  Yes/No  Text field  Text field
 
'The dumpsec file outputs in a mixed comma-delimited/fixed width _
 format. The fields are are path/filename, the owner, then the _
 rights are a fixed width colums.
 
'Opening the table to write to
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Dumpsec_Data")    '<-- Change to your tablename
 
'Opening the file to read from
FileNum = FreeFile()
InputFile = FilePathAndName          '<--- This is coming from the Input of the function
Debug.Print "Reading: " & InputFile
Open InputFile For Input Access Read Shared As #FileNum
 
 
Line Input #FileNum, InputString     '<--- To throw away the header line
Line Input #FileNum, InputString     '<--- To throw away the 2nd header line
 
Do Until EOF(FileNum) = True
    Line Input #FileNum, InputString      'Read the data in
    Debug.Print InputString
      If Trim(InputString) <> "" Then
        StringArray = Split(InputString, ",")
        If UBound(StringArray) =>2 Then
            If Trim(Left(StringArray(2), 2)) = "o" Then FileOwner = True Else FileOwner = False
             DirRights = Trim(Mid(StringArray(2), 4, 10))
             FileRights = Trim(Mid(StringArray(2), 15, 10))
        Else
             FileOwner = False
             DirRights = ""
             FileRights = ""
        End If
 
        With RS
            .AddNew
            !PathName = Trim(StringArray(0))
            !User_Acct = If UBound(StringArray) => 1 Then  Trim(StringArray(1)) Else ""
            !Owner = FileOwner
            !Dir_Rights = DirRights
            !File_Rights = FileRights
            .Update
        End With
    End If
Loop
 
Close #FileNum                  'close files and tables
Set RS = Nothing
Set DB = Nothing
 
End Function
 
'-------------------------------------------------------
Public Function RunAllInDir()
 
Dim FilesPath As String
Dim FileName As String
 
FilesPath = "C:\NTFS\Scans\"
 
FileName = Dir(FilesPath & "*.csv")
 
Do While FileName <> ""
    Debug.Print "RunAll Path: " & FilesPath & FileName
    Call Import_Dumpsec_Files(FilesPath & FileName)
    FileName = Dir
Loop
 
End Function

Open in new window

Try this:

If UBound(StringArray) => 1 Then !User_Acct = Trim(StringArray(1)) Else  !User_Acct = ""

Open in new window

ASKER

Ok

I get Run time error 3163
the field is too small to accept the amount of data you attempted to add. try inserting or pasting less data

!PathName = Trim(StringArray(0))

This line get highlighted

You'll need to stop the process, go into the table design and change the size of the field -- The largest a text field can be is 255 characters.

If you are running into it as an issue that you need longer than 255 characters you may need to change it into a memo field. That has some issues in itself, but we can address that later.

Line 62:

!User_Acct = Iff(UBound(StringArray) => 1,Trim(StringArray(1)), "")

ASKER

SWEET it worked..

Is there any way for the highlighted rows I have to keep the highlight in here?

>> Is there any way for the highlighted rows I have to keep the highlight in here?

Huh? What the highlight in VB? or the selected rows in the table?

ASKER

Ok..

I am going to have to convert all my csv files to xls before I put them into the database, so that I can apply a macro to highlight (background color) rows based on a condition. so for example, if column b has /users change the background color to yellow

Once I do that, when I import them into the database (with your module) will the the background color remain

ASKER

?

ASKER

Thanks for the Great Help

The colors are dependent on Excel or whatever app you are using. The code is built on reading CSV files, not Excel. After they are imported, you can do queries for users and groups. Or export it back to Excel and work it there.

Glad to be of assistance. May all your days get brighter and brighter.