1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
|
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$connectionString = "server=localhost;uid=test;pwd=test;database=zabbix;port=3306;Allow User Variables=true;Pooling=false;"
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
Get-DhcpServerv4Lease -ScopeId 192.168.0.0 -ComputerName winserver |
Select-Object -Property ClientId, IPAddress, ClientType, HostName |
Export-Csv -NoTypeInformation -Path C:\Users\Administrateur\mysql.csv
$sql = @'
LOAD DATA LOCAL INFILE 'C:\\Users\\Administrateur\\mysql.csv'
INTO TABLE dhcp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
'@
$command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
# If we've done anything wrong up to this point, ExecuteNonQuery() will produce an error.
$rowsAffected = $command.ExecuteNonQuery();
# Now that we're done sending data to the DB, it's time to pull it back out again using the
# sample code you found which started with a SELECT statement. In this case, the table name
# is dhcp (taken from your "LOAD DATA INFILE 'filename' INTO TABLE dhcp" query.)
#$sql = 'SELECT * FROM dhcp'
#$command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
#$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
#$dataSet = New-Object System.Data.DataSet
# Here, "sample_data" is whatever you want to call it, so long as it's the same value in the
# call to Fill(), and in the next line which access that name in $dataSet.Tables.
#$recordCount = $dataAdapter.Fill($dataSet, "sample_data")
#$dataSet.Tables["sample_data"] | Format-Table > C:\inetpub\wwwroot\samples.html
$connection.Close() |
Partager