Write to MS Access table from single line CSV.

PowerShell Help
1

Hi folks.

Expert VBA with 20+ years of experience - but very new to actually scripting powershell to do stuff. I have only used powershell for simple single line execution or batch files in the past.

Looking for some advice - here is what I want to do:

Run a powershell script that monitors a folder location. When a new file is added to that location I want it to open the newly added CSV and write the single line of data from the CSV to a MS Access Table.

What Works:

Thanks to some bumbling around with powershell code I have the ‘monitor folder location’ and ‘do something when a file is added’ part working.

I have the ‘loop thru the CSV and save the CSV line as variables in Powershell’ working

I can write those variables to a text file, but not to the access table.

 

What Doesn’t Work:

The data is not being written to the MS Access table, although the powershell code is opening the database and if I manually add a row to the table, I can see the auto key is being incremented (although no record is actually added). For example, If I run the powershell action 10 times, when I manually test add a row in access the auto ID will be “11” even though there are no records in the table.

Powershell is also leaving the database ‘open’ in the background it seem. I am getting the .accdl lock file being left open rather than closed.

 

What might be going wrong?:

Normal
0

false
false
false

EN-AU
X-NONE
X-NONE

/* Style Definitions */ table.MsoNormalTable {mso-style-name:“Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:“”; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:“Calibri”,sans-serif; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:“Times New Roman”; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;}
1: The CSV file will only ever have a single line. I am currently using a ‘foreach’ command which loops thru the file.
Maybe I am populating a 2nd set of blank attributes? This doesn’t seem likely as only a the single line is written into the test text file.
2: When powershell runs more than once it doesn’t seem to close down Access properly and I am left with a acdb lock file. Not sure why but it might be locking up the table and preventing further attempts to write to it.
3: I have done something dumb with the SQL string that does the insert (this is possible, although it looks ok from what I can see). I did try it once with hard coded values and it wrote it into the table, but only once - thus my thoughts about the table being locked by earlier attempts.
4: PBKAC issue - I am clearly missing something and given my lack of experience and skill with powershell I am not surprised.
I like this powershell approach as it runs external with VBA having to have an interval timer or looping to constantly check the folder - it is fast and work seamlessly in the background, but I am bumbling around with the code as I have little experience with doing more complex things in Powershell.
Happy if anyone has any advice.
 

 

 

### SET FOLDER TO WATCH + FILES TO WATCH + SUBFOLDERS YES/NO
    $watcher = New-Object System.IO.FileSystemWatcher
    $watcher.Path = "V:\ReportingQueue\"
    $watcher.Filter = "*.*"
    $watcher.IncludeSubdirectories = $true
    $watcher.EnableRaisingEvents = $true  




### DEFINE ACTIONS AFTER AN EVENT IS DETECTED
    $action = { $path = $Event.SourceEventArgs.FullPath
                $changeType = $Event.SourceEventArgs.ChangeType
                ##$logline = "$(Get-Date), $changeType, $Path"
                ##Add-content "V:\ReportingLog\ReportingLog.txt" -value $logline
               
               
               
               
                $connectstring = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=V:\ReportingLog\ReportingLogDB.accdb"
                $conn = New-Object System.Data.OleDb.OleDbConnection($connectstring)
                $conn.Open()
                ### $File = Import-Csv "V:\ReportingQueue\IMG5029.csv"
                $File = Import-Csv -value “$path"
    
                foreach ($item in $File) 
                { 
                $Chrono =  $item.Chrono
                $Account = $item.Account
                $ProberRun = $item.ProberRun
                $CreateEveID = $item.CreateEveID

               
                Add-content "V:\ReportingLog\ReportingLog.txt" -value “$(Get-Date)"
                Add-content "V:\ReportingLog\ReportingLog.txt" -value “$path"
                Add-content "V:\ReportingLog\ReportingLog.txt" -value “$Chrono”
                Add-content "V:\ReportingLog\ReportingLog.txt" -value “$Account”
                Add-content "V:\ReportingLog\ReportingLog.txt" -value “$ProberRun”
                Add-content "V:\ReportingLog\ReportingLog.txt" -value “$CreateEveID”
                

                
                $cmd = $conn.CreateCommand()
                $cmd.CommandText="INSERT INTO tblReportingQueue(Chrono,Account,ProberRun,CreateEveID) VALUES('$Chrono','$Account','$ProberRun','$CreateEveID')"
                $cmd.ExecuteNonQuery()
                }
                $conn.Close()
              }
              
              
                 
### DECIDE WHICH EVENTS SHOULD BE WATCHED 
    Register-ObjectEvent $watcher "Created" -Action $action
### Register-ObjectEvent $watcher "Changed" -Action $action
### Register-ObjectEvent $watcher "Deleted" -Action $action
### Register-ObjectEvent $watcher "Renamed" -Action $action
    while ($true) {sleep 5}
2

Typically, I test each piece and get it working and tested. I would run the insert code 5 times and see if i have a lock issue. You can just add another $Query line with manual values and remark out the current query with the dynamic values. Once the database validation is done, then you can glue it all together and test. Additionally, you can try ADODB and see if it works better for you as you are defining the lock details with the database. If you know it’s always a single line in the CSV, you can pipe to Select-Object -First 1 to ensure you are only getting a single line and reference the variables with dot notation. Give the below a show and see if it works better for you.

$action = { 
    $database = 'V:\ReportingLog\ReportingLogDB.accdb'
    $path = $Event.SourceEventArgs.FullPath
    $changeType = $Event.SourceEventArgs.ChangeType

    $csv = Import-Csv -Path $path | Select -First 1        
    
    $OpenStatic = 3
    $LockOptimistic = 3
    $connection = New-Object -ComObject ADODB.Connection
    $connection.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source=$database" )
    $RecordSet = New-Object -ComObject ADODB.Recordset
    $Query = "INSERT INTO tblReportingQueue (Chrono,Account,ProberRun,CreateEveID) VALUES ('$($csv.Chrono)','$($csv.Account)','$($csv.ProberRun)','$($csv.CreateEveID)')"
    $RecordSet.Open($Query, $Connection, $OpenStatic, $LockOptimistic)
    $RecordSet.Close()
    $connection.Close()
}
3

Thank you Rob for your time and thoughts on this. I was away for the past couple of days thus the tardy response.

I have managed to get this working well now - the main issue was how I was reading the path / file. I was using -Value when I should have been using -Path. In the actual code the path and file name is a variable rather than a fixed string as in the example I posted.

Tripped up by my own lack of knowledge. As is often the case, I found this not long after posting a help request to this forum.

Many Thanks
Darryl.

4

Interesting. I caught the -Value and it’s updated in my code, but I’m surprised that it worked. Glad you figured your issue out.