How to read the file property using a Script?

Question

I need to write either a PowerShell or batch script to read the property of files in the folder on certain time schedule and store it in a SQL Table.

Script will read the name of the file, date time stamp, size and folder directory and save these four fields in the database.

All of this information is part of the `Get-Item` / `Get-ChildItem` cmdlets. Look those up to get started and then if you stuck update your question with what you have tried, what is not working and what you expect to get. We are not a code writing service. I would expect someone that has been here for a while to understand that. — Matt, Nov 15 '15 at 20:03

score 2 · Answer 1 · edited May 23 '17 at 12:30

2

Try like this :

@echo off

for %%a in (*.*) do (
 echo %%~ta
 echo %%~za
 echo %%~da
 echo %%~pa
 echo %%~na
 echo %%~xa
 echo %%~tzdpnxa
 pause)

And use FOR /? to anderstand.

For the SQL query within a BAT, take a look here

edited May 23 '17 at 12:30

Community

1
1

answered Nov 15 '15 at 23:22

SachaDee

9,245
3
23
33

Thanks, I have try with your syntax. Is it possible to include Insert SQL Query in the Script. – goofyui Nov 16 '15 at 03:05

score 0 · Answer 2 · answered Nov 16 '15 at 03:39

Here is a script that may help you with that. First you need to download Powershell MySQL connector from HERE

Since i don't have MySQL installed on this machine i wasn't able to test the code. ( the MySQL connection and insertion is from HERE

function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {

  # Load MySQL .NET Connector Objects
  [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

  # Open Connection
  $connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE"
  $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
  $conn.Open()
  $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
  return $conn

}

function WriteMySQLQuery($conn, [string]$query) {

  $command = $conn.CreateCommand()
  $command.CommandText = $query
  $RowsInserted = $command.ExecuteNonQuery()
  $command.Dispose()
  if ($RowsInserted) {
    return $RowInserted
  } else {
    return $false
  }
}
$user ="test"
$pass = "VeryStrongPassword"
$MySQLHost = "Localhost"
$database = "MyDB"


# Connect to MySQL Database
$conn = ConnectMySQL $user $pass $MySQLHost $database



 Get-ItemProperty C:\file.csv | select Name, CreationTime, Length, DirectoryName | %{

    $query = "INSERT INTO Table SET file_name='"+$PSItem.Name+"', file_size='"+$PSITEM.Length+"', file_creationdate='"+$PSitem.CreationTime+"', file_dir='"+$PSItem.DirectoryName+"'"
    # Insert into MYSQL
    $Rows =  WriteMySQLQuery $conn $query
    Write $Rows " inserted into database"

 }

How to read the file property using a Script?

2 Answers2