Please attach a sample XML file that you’d like to convert to CSV. You’ll probably need to rename it as .txt for the forums to allow the attachment. Also, we’d need to know which fields you want to be in the CSV file and database. (You mentioned IP address and MAC address; is that everything you need?)
I have attached the file in .txt.
From the Lease from this file, i would just retrive the fadresse ip and the mac adress fileds. that’s all
For the example :
192.168.0.0
41-7a-65-72-74-79-75-69-6f-70
can you say me if is possible to convert the xml outpout from the cmd Export-DHCP server in a .cvs file in the script automatically ?
the goal is to have a script that does an export of the converted file and just send the relevant fields in a database.
OK, while you can go this route and export to an XML file first, then extract some of its data as CSV, you don’t actually have to do that. The general approach for converting something to a CSV file in PowerShell is to build some objects in memory which contain the properties you need (and where the property names become the header row of the CSV file), then pipe those objects to Export-Csv.
It so happens that there’s another set of commands in the DhcpServer module which give you the lease information as objects already, eliminating the need for the extra step of saving an XML file, then importing it and manipulating data. Try this:
The Get-DhcpServerv4Lease command gives you all of the same information about the IPv4 leases that you got from the XML file (and none of the extra stuff that you don’t need for this particular operation). The Select-Object command gets rid of all properties on the objects except for ClientId (MAC address) and IPAddress, and the resulting objects are passed on to Export-Csv to produce the file you need. If you want, you can even rename the ClientId property to MacAddress by changing the arguments to Select-Object slightly. This is what’s generally referred to as a “constructed property” in the PowerShell community:
There are a couple of quoting problems with your string. In this case, it’s probably easiest to use a PowerShell here-string (see the about_Quoting_Rules help file for details on these, as well as every other type of string quoting issue you might come across.)
Also, the contents of your LOAD DATA INFILE command might be a bit off. I don’t have a MySQL instance to test this, but according to their documentation, this should be what works for loading a CSV file from Windows:
$sql = @'
LOAD DATA LOCAL INFILE 'C:\Users\Administrateur\mysql.csv'
INTO TABLE dhcp
FIELDS OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
'@
It may be difficult to tell by looking at it, but that ENCLOSED BY ‘"’ bit is a lone double quotation mark surrounded by a pair of single quotation marks.
It looks like something is off about the rest of the script as well. I’m not sure what you’re doing with the DataAdapter or DataSet objects. I would expect to see you calling the ExecuteNonQuery() method on the $command object.
can you say me how to show the log in windows server please ? while the script working but ther is no record in database and i have any error message in the console …
I see that you’ve copied and pasted a lot of code from that blog post, but the problem is that the blog was demonstrating a SELECT command, which retrieves data from the DB. You’ve changed the SQL query to be a LOAD DATA INFILE command instead, which is something completely different. The rest of the code is no longer appropriate to that type of query.
This can be a pretty complicated topic. I’d start by learning about the SQL language itself (and you may as well do this from MySQL’s documentation, since that’s the engine you’re using.) From there, start to learn about the .NET API that they’ve provided (which looks very similar to Microsoft SQL Server classes, by the way, so you could potentially read up on those as well for more information.)
I can’t test this code, since I don’t have an instance of MySQL (or a DHCP server, for that matter), but I think this is what you’re trying to do:
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$connectionString = "server=localhost;uid=admin;pwd=test;database=zabbix;port=3306;"
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $connectionString
Get-DhcpServerv4Lease -ScopeId 192.168.0.0 -ComputerName winserver |
Select-Object -Property ClientId, IPAddress |
Export-Csv -NoTypeInformation -Path C:\Users\Administrateur\mysql.csv
$sql = @'
LOAD DATA LOCAL INFILE 'C:\Users\Administrateur\mysql.csv'
INTO TABLE dhcp
FIELDS 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()
The script work , there two probleme, the first was $connection.Open() was not whrite et the second was the caractere for the sql request, in linux the path is with one / but with windows is \.
Can you juste say me how make a space betwen the field ? i explain me, i have remove the double quotes and now i would remove the, and make a space for the delimieur to MySQL . It is possible ?
Thank you for helping et for all your explain !!
{Foreach {$_ -replace '"',''}}
{[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$connectionString = "server=localhost;uid=admin;pwd=test;database=zabbix;port=3306;"
$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
$extractfile= "C:\Users\Administrateur\mysql.csv"
$delguill = Get-Content C:\Users\Administrateur\mysql.csv | Foreach {$_ -replace '"',''}
$delguill > C:\Users\Administrateur\mysql.csv
$sql = @'
LOAD DATA LOCAL INFILE 'C:\\Users\\Administrateur\\mysql.csv'
INTO TABLE dhcp
FIELDS ENCLOSED BY ','
LINES TERMINATED BY '\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()}
I don’t think you want to remove the quotes and change commas to spaces like that. However, looking over my earlier post and comparing it to the MySQL documentation, I did just notice that part of the SQL query to read a CSV-style file was missing. Try this:
$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;
'@
According to the documentation, the default field terminator is a tab, not a comma. Adding that FIELDS TERMINATED BY ‘,’ bit should address this.