2

I have a access database with a lot of information, I am not that good with access database and I am still a newbie when it comes to powershell. I have been trying to automate the insertion of data from the csv files into the access database as they are done weekly.

after researching I found a powershell code that allows insertion of CSV into databases, However I have not been able to make it work with my current acccess database and CSV file.

   write-host "current path is $PSSCriptRoot"
$datafile="$PSScriptRoot\Input.csv" #trying to import this file into the databse
$dbfile="$PSScriptRoot\database.accdb" #this is the test access database
$connectionString="Provider=Microsoft.Ace.OLEDB.12.0; Data Source=$dbfile" #general info I found researc
$conn = New-Object System.Data.OleDb.OleDbConnection($connectionString)
$conn.Open()
$cmd = $Conn.CreateCommand()
Import-Csv $dataFile | 
         ForEach{
             $cmd.CommandText = "INSERT into [MyTable1]([Name],[First Data Set],[Description],[Enabled],[Last Date,Creation Date],[Modification Date],[Day of The year],[ID type],[ID Date],[Street Address],[date],[type])
        VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}')" -f @(

        $_.Name,
        $_.'First Data Set',
        $_.Description,
        $_.Enabled, 
        if ($_.'Last Logon Date') { ([datetime]$_.'Last Logon Date').ToString('M/d/yyyy H:mm') } else {[System.DBNull]::Value }
        if ($_.'Creation Date') { ([datetime]$_.'Creation Date').ToString('M/d/yyyy H:mm') } else { [System.DBNull]::Value}
        if ($_.'Modification Date') { ([datetime]$_.'Modification Date').ToString('M/d/yyyy H:mm') } else { [System.DBNull]::Value}
        $_.'Day of The year',
        $_.'ID type',
        if ($_.'ID Date') { ([datetime]$_.'Modification Date').ToString('M/d/yyyy H:mm') } else { [System.DBNull]::Value },
        $_.'Street Address',
        if ($_.date) { ([datetime]$_.date).ToString('M/d/yyyy H:mm') } else { [System.DBNull]::Value}
        $_.Type
        )
        $cmd.ExecuteNonQuery()
 }
$conn.Close()

The access database has the corresponding table with the corresponding rows from the csv, so what should happen is that the data from the csv would be inserted into the table.

the input and database files are recognized with the $PSScriptRoot command as they point to the path of the script which is in the same folder.

The error I am getting is the following: "Exception calling "ExecuteNonQuery" with "0" argument(s): "Syntax error in INSERT INTO statement." At C:\1 - database paths\Database1\database.accdb "

yes that is the current testing path, it has spaces and a couple of special characters, this cannot be changed and as other scripts work fine, this should also work fine.

Since I have little knowledge about accdb I am having a hard time finding why is the error, for what it says it seems my syntax for the insert command is not correct, yet I cant find an example of another type of syntax used for that command. I used the -f as rows contain spaces and there is an issue with quotes on powershell. I can not change the path name nor the row names at all.

edit: now I have another problem, where the columns with date time cannot accept a null value, I tried going around it by asetting a DBNull value but this does not work. I need to see those blank values on my database for statistic purposes , is there a way to import those empty values ?

i had to format each value into adata type as powershell passes them a strings, .

Victor J Mytre
  • 364
  • 1
  • 10
  • 3
    Your field names have spaces and should be enclosed in square brackets: `$cmd.CommandText = "INSERT into [MyTable1](Name,[First Data Set],Description,Enabled,[Last Date],[Creation Date],[Modification Date],[Day of The year],[ID type],[ID Number],[Street Address],date,type)` If that change doesn't fix it, try to `Write-Host $cmd.CommandText` before `$cmd.ExecuteNonQuery()` and paste that into an Access query window. – Rich Moss Sep 04 '19 at 16:30
  • 1
    Suggest you also bracket `Name`, `Description`, `date`, and `type` field names. See [Problem names and reserved words in Access](http://allenbrowne.com/AppIssueBadWord.html) – HansUp Sep 04 '19 at 18:04
  • so I wrote the write host, and seems like the issue might be the blank values? for example on one of the commands is VALUES['Jose','j0se', '' , 'TRUE', .... the command looks fine but it keeps saying "Exception calling "ExecuteNonQuery" with "0" argument(s): "Syntax error in INSERT INTO statement." – Victor J Mytre Sep 04 '19 at 18:06
  • I found the issue, is the formatting, on the database each column has different formatting (date, number, true or false), and all the values I am inputting on the code are string or just text so its giving me the error for each one that is a mismatch. Is there a way to format each value input to their correct type? – Victor J Mytre Sep 04 '19 at 18:26
  • 1
    Possible duplicate of [Insert into SQL Server date using Powershell](https://stackoverflow.com/questions/50238784/insert-into-sql-server-date-using-powershell) – June7 Sep 04 '19 at 19:05
  • 1
    Why use PowerShell and not VBA? – June7 Sep 04 '19 at 19:08
  • Managed to get it working but I have one problem now where if the value for date is blank, I cannot insert that data. – Victor J Mytre Sep 04 '19 at 20:50
  • Do you mean blank or missing? – Walter Mitty Sep 05 '19 at 00:39
  • It might be useful to know if the csv file can be imported into an empty access database, resulting in a new table. – Walter Mitty Sep 05 '19 at 00:41
  • 1
    please, please, please also read about SQL Injection and use parameterised queries instead of formatting a sql string... https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements – mclayton Sep 05 '19 at 07:22
  • How do i parametise the queries? I repeat I am trying to insert a csv into an accdb file, not a server, a file, this is just to streamline the importing of the csv weekly into the accdb. – Victor J Mytre Sep 06 '19 at 19:00

0 Answers0