Export en CSV - Export-csv erreur de "Delimiter"

**leo_archange** · 19/12/2014, 11h26

Bonjour à tous,

je suis presque au bout d'un script PowerShell me permettant d'automatiser la résolution DNS depuis une liste d'adresses IP ou de noms d'hôte stocker dans un fichier txt.

Je précise tout de suite, je suis en PowerShell 1.0 et je n'ai pas de possibilité de mettre à jour.

Mon problème se situe dans la création du fichier de sortie. J'étais au début partie sur l'utilisation d'outfile mais comme j'ai besoin d'exporter au format csv, je suis passé à l'utilisation d'Export-Csv et là je n'arrive pas à comprendre où est mon erreur. Au moment d'écrire le fichier, je me retrouve avec l'erreur suivante :

Export-Csv : Impossible de lier le paramètre « Delimiter ». Impossible de convertir la valeur « 20141219_10:47_DnsQuesriesResults.csv » en type « System.Char ». Erreur : « La chaîne doit avoir une longueur d'un et un seul caractère. »
Au niveau de \\<chemin d'accès réseau>\02 - Script\02 - PowerShell\01 - Réseau\01-NSLOOKUP\nslookup depuis adresse IP.PS1 : 112 Caractère : 25
+ $ResultList | Export-Csv <<<< -Path $currentLocation $newName -NoTypeInformation -Encoding ascii
+ CategoryInfo : InvalidArgument: (

[Export-Csv], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.ExportCsvCommand

Pour mon code :

Code powershell :

Sélectionner tout - Visualiser dans une fenêtre à part

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
#Lets set variable, array, etc... to nothing cause that how we are
$ResultList = $null
$ip = $null
$result = $null
$listofIPs = $null
$iphost = $null
$resultSplit = $null
$testIorH = $null
[String]$dateAndTime = $null
[String]$currentLocation = $null
#$ouputFile = $null
 
#Let's create a function so the script is easier to use (not so sure about that)
 
Function Get-DnsEntry($ipHost)
{
    #if iphost is like an IP address let's resolve IP to hostname
    If($ipHost -match "^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$")
    {
        [System.Net.Dns]::GetHostEntry($ipHost).HostName
    }
    #Else, if iphost is like an hostname, let's resolve hostname to IP
    ElseIf( $ipHost -match "^.*\.\.*")
    {
        [System.Net.Dns]::GetHostEntry($ipHost).AddressList[0].IPAddressToString
    }
    #else you've entered anything different from an IP address or a hostname, go get a new job cause you're doing this one wrong
    else
    {
        Throw "Specify either an IP V4 address or a hostname" 
    }
} 
 
# So we created a PowerShell function here under are the instruction to use it (if you can't undestand go get a new job)
# Get-DnsEntry(<IP address>) for retrieving hostname or Get-DnsEntry(<hostname>) to retreive IP
 
#Getting IP address or hostname from text file
#The file where your IPs are must be named nslookuplist and place in the same directory as your script if you don't like it do your own script
$filePath = Resolve-Path "nslookuplist.txt"
 
echo $filePath
 
#getting content file nslookuplist.txt
$listofIPs = Get-Content $filePath
 
#Or comment the 3 previous lines of code and uncomment the next one to provide address or hostname manually
#$listofIPs = "173.136.234.58","173.136.234.59","173.136.234.60" 
 
#Let's create a blank array for the resolved names or IP that 
$ResultList = @()
 
#Testing if we are retrieving IPs or hostnames with the first line if you mixed all of them I can't do nothing for you
$testIorH = $listofIPs | where { $_ -ne "$null" } | Select-Object -Index 0
 
#if it is like an IP address
If($testIorH -match "^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$")
{
    $resultList = "IP Address;DNS hostname;"
}
#else it's like an hostname so
Else
{
    $resultList = "DNS hostname;IP Address;"
}
 
#Yeah, I like to echo all the time to see what's happening    
echo $resultList
 
# Lets resolve each of these addresses or hostnames
foreach ($ip in $listofIPs)
{ 
    #No idea of what's done here but the original script seems to say that it avoid some function default
    $currentEAP = $ErrorActionPreference
    $ErrorActionPreference = "silentlycontinue"
    $ErrorActionPreference = $currentEAP
 
    #Let's use that function of yours
    $result = Get-DnsEntry($ip)
 
    #if no answer from DNS then tell it to me
    If($result -match "^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$")
    {
        echo "$ip;No answer from DNS;"
        $result = "`n$ip;No answer from DNS;"
        $resultList += $result
    }
    #else tell it to me also but don't give me all the sh*t after, I just want a hosname
    Else
    {        
        #Print the result to the screen cause I like to master what I'm doing and echo all the time my work
        $resultSplit = $result.split('.')
        $resultSplit = $resultSplit[0]
        echo "$ip;$resultSplit;"
 
        #Store the seult in an array but without crushing the previous results
        $resultList += "`n$ip;$resultSplit;"
    }
}
 
#Formating date to YYYYMMDD format
[String]$dateAndTime = $(Get-Date -UFormat "%Y%m%d_%R")
 
#Naming file according to what the script does
[String]$newName = $dateAndTime + '_DnsQuesriesResults.csv'
 
[String]$currentLocation = Get-Location
 
#$outputFile = Join-Path -path $currentLocation $newName
 
#and last but not least write result to csv file with a separated semi colon so Excel Sheet can be used to import data
$ResultList | Export-Csv -Path $currentLocation $newName -NoTypeInformation -Encoding ascii

Voilà ! Si un scripteur charitable peut m'aider, je lui en serai grandement reconnaissant.

**leo_archange** · 19/12/2014, 14h01

Alors je suis juste un immense boulet et j'avais oublié qu'on ne peut pas mettre de ":" dans un titre de fichier. J'ai changé le format de la date à la ligne 101 du script et je suis repassé à la commande Out-File et tout fonctionne à merveille.

Pour ceux que ça intéressent, voici le script qui permet de récupérer automatiquement un nom DNS depuis une adresse IP ou l'inverse à partir d'un fichier texte nommé nslookuplist.txt. Je l'ai conçu pour qu'il soit exploitable en l'état (i.e le script dans un répertoire, le nslookuplist.txt dans le même répertoire, on le lance et tout se fait tout seul).

Code powershell :

Sélectionner tout - Visualiser dans une fenêtre à part

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
#Lets set variable, array, etc... to nothing cause that how we are
$ResultList = $null
$ip = $null
$result = $null
$listofIPs = $null
$iphost = $null
$resultSplit = $null
$testIorH = $null
[String]$dateAndTime = $null
[String]$currentLocation = $null
[String]$ouputFile = $null
 
#Let's create a function so the script is easier to use (not so sure about that)
 
Function Get-DnsEntry($ipHost)
{
    #if iphost is like an IP address let's resolve IP to hostname
    If($ipHost -match "^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$")
    {
        [System.Net.Dns]::GetHostEntry($ipHost).HostName
    }
    #Else, if iphost is like an hostname, let's resolve hostname to IP
    ElseIf( $ipHost -match "^.*\.\.*")
    {
        [System.Net.Dns]::GetHostEntry($ipHost).AddressList[0].IPAddressToString
    }
    #else you've entered anything different from an IP address or a hostname, go get a new job cause you're doing this one wrong
    else
    {
        Throw "Specify either an IP V4 address or a hostname" 
    }
} 
 
# So we created a PowerShell function here under are the instruction to use it (if you can't undestand go get a new job)
# Get-DnsEntry(<IP address>) for retrieving hostname or Get-DnsEntry(<hostname>) to retreive IP
 
#Getting IP address or hostname from text file
#The file where your IPs are must be named nslookuplist and place in the same directory as your script if you don't like it do your own script
$filePath = Resolve-Path "nslookuplist.txt"
 
echo $filePath
 
#getting content file nslookuplist.txt
$listofIPs = Get-Content $filePath
 
#Or comment the 3 previous lines of code and uncomment the next one to provide address or hostname manually
#$listofIPs = "173.136.234.58","173.136.234.59","173.136.234.60" 
 
#Let's create a blank array for the resolved names or IP that 
$ResultList = @()
 
#Testing if we are retrieving IPs or hostnames with the first line if you mixed all of them I can't do nothing for you
$testIorH = $listofIPs | where { $_ -ne "$null" } | Select-Object -Index 0
 
#if it is like an IP address
If($testIorH -match "^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$")
{
    $resultList = "IP Address;DNS hostname;"
}
#else it's like an hostname so
Else
{
    $resultList = "DNS hostname;IP Address;"
}
 
#Yeah, I like to echo all the time to see what's happening    
echo $resultList
 
# Lets resolve each of these addresses or hostnames
foreach ($ip in $listofIPs)
{ 
    #No idea of what's done here but the original script seems to say that it avoid some function default
    $currentEAP = $ErrorActionPreference
    $ErrorActionPreference = "silentlycontinue"
    $ErrorActionPreference = $currentEAP
 
    #Let's use that function of yours
    $result = Get-DnsEntry($ip)
 
    #if no answer from DNS then tell it to me
    If($result -match "^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$")
    {
        echo "$ip;No answer from DNS;"
        $result = "`n$ip;No answer from DNS;"
        $resultList += $result
    }
    #else tell it to me also but don't give me all the sh*t after, I just want a hosname
    Else
    {        
        #Print the result to the screen cause I like to master what I'm doing and echo all the time my work
        $resultSplit = $result.split('.')
        $resultSplit = $resultSplit[0]
        echo "$ip;$resultSplit;"
 
        #Store the seult in an array but without crushing the previous results
        $resultList += "`n$ip;$resultSplit;"
    }
}
 
#Formating date to YYYYMMDD format
[String]$dateAndTime = $(Get-Date -UFormat "%Y%m%d_%H%M%S")
 
#Naming file according to what the script does
[String]$newName = $dateAndTime + '_DnsQuesriesResults.csv'
 
[String]$currentLocation = Get-Location
 
$outputFile = Join-Path -path $currentLocation $newName
 
#and last but not least write result to csv file with a separated semi colon so Excel Sheet can be used to import data
Out-file -filePath $outputFile -inputobject "$ResultList"

Export en CSV - Export-csv erreur de "Delimiter" [PowerShell]

Scripts/Batch

Vue hybride

Discussions similaires

Partager

Partager