{"id":521,"date":"2016-10-17T10:07:08","date_gmt":"2016-10-17T09:07:08","guid":{"rendered":"http:\/\/snakenet.eu\/blog\/?p=521"},"modified":"2016-11-24T18:51:23","modified_gmt":"2016-11-24T17:51:23","slug":"convertir-un-fichier-excel-en-fichier-csv-sans-corruption-des-donnees","status":"publish","type":"post","link":"https:\/\/snakenet.eu\/blog\/convertir-un-fichier-excel-en-fichier-csv-sans-corruption-des-donnees\/521\/","title":{"rendered":"Convertir un fichier Excel en fichier CSV sans corruption des donn\u00e9es"},"content":{"rendered":"

Amusant. Si vous faites \u00ab\u00a0enregistrer sous\u00a0\u00bb et choisissez le format \u00ab\u00a0CSV\u00a0\u00bb et que vous avez des caract\u00e8res bizarres ( Comme \u00e9,\u00e8,&,..) Excel les d\u00e9truira lors de l’enregistrement (pas sympa)<\/p>\n

Si vous faites \u00ab\u00a0fichier > exporter\u00a0\u00bb et choisissez le format CSV, ca va mieux! mais c’est pas encore \u00e7a.. (les champs ne sont pas entre guillemets et on ne peux pas choisir le caract\u00e8re de s\u00e9paration…<\/p>\n

Voici donc un script PowerShell qui fait le boulot…<\/p>\n

Et pour que ce soit GENIAL, faites un raccourci windows avec cette commande:
\nC:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe\u00a0 -ExecutionPolicy Bypass\u00a0\u00a0 -file\u00a0 \u00ab\u00a0H:\\Scripts\\XLS to CSV.ps1\u00a0\u00bb
\n(pensez \u00e0 changer le chemin du script)
\nAinsi, il vous suffira de faire un drag’n drop de votre fichier excel sur ce raccourci et HOP! converti !
\n(on pourait aussi envisager de le mettre dans \u00ab\u00a0send to\u00a0\u00bb mais, ca, je vous laisse faire)<\/p>\n

param([string]$xlFile=’nothing’)
\n$debugMode=$false
\n$ErrorActionPreference=’Stop’<\/div>\n
if ($debugMode) { $xlFile=\u00a0\u00bbH:\\Book1.xlsx\u00a0\u00bb }<\/div>\n
$csvFile=$xlfile.Replace(‘.xlsx’,’.csv’).replace(‘.xls’,’.csv’)<\/div>\n
## Checking…
\nif ( $xlFile -eq ‘nothing’ ) { Write-Host \u00ab\u00a0No Excel file given. aborting…\u00a0\u00bb\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -foregroundcolor red ;$tmp=Read-Host \u00ab\u00a0Press ENTER key to exit\u00a0\u00bb;exit }
\nif ( test-path($xlFile -eq $false)\u00a0 ) { Write-Host \u00ab\u00a0Specified Excel file does not exist.\u00a0\u00bb\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -foregroundcolor red ;$tmp=Read-Host \u00ab\u00a0Press ENTER key to exit\u00a0\u00bb;exit }
\nif ( test-path($csvFile)\u00a0\u00a0 ) { Write-Host \u00ab\u00a0Old converted CSV file exist. Please, delete it before trying to convert it again.\u00a0\u00bb\u00a0\u00a0 -foregroundcolor red ;$tmp=Read-Host \u00ab\u00a0Press ENTER key to exit\u00a0\u00bb;exit }<\/div>\n
# http:\/\/stackoverflow.com\/questions\/687891\/exception-automating-excel-2007-with-powershell-when-calling-workbooks-add
\n& {
\n[threading.thread]::CurrentThread.CurrentCulture = ‘en-US’
\n$xl = New-Object -COM \u00ab\u00a0Excel.Application\u00a0\u00bb
\n$xl.Visible = $debugMode
\nWrite-Host \u00ab\u00a0Opening source file\u00a0\u00bb -foregroundcolor green
\n$wb = $xl.Workbooks.Open($xlFile)
\n$ws = $wb.Sheets.Item(1)<\/div>\n
\u00a0 $i=1
\nwhile( $ws.Cells.Item(1, $i).value2 -ne $null ) { if ( $debugMode) {Write-host \u00ab\u00a0Debug 1 : counting column $i\u00a0\u00bb}; $i++ }
\n$maxColonne=$i-1<\/div>\n
\u00a0 $i=1
\nwhile( $ws.Cells.Item($i, 1).value2 -ne $null ) { if ( $debugMode) {Write-host \u00ab\u00a0Debug 1 : counting row $i\u00a0\u00bb}; $i++ }
\n$maxLigne=$i<\/div>\n
\u00a0 Write-Host \u00ab\u00a0There will be $maxColonne columns and $MaxLigne lines to convert.\u00a0\u00bb -foregroundcolor green<\/div>\n
\n$object=@()
\n$ligne=2\u00a0\u00a0 # ligne 1 = titre
\n$colonne=1<\/div>\n
\u00a0 while( $ws.Cells.Item($ligne,1).value2 -ne $null ) {
\n$line=New-Object PSObject
\n$colonne=1
\nwhile( $colonne -le $maxColonne) {
\n$cell=$ws.Cells.Item($ligne,$colonne).value2
\nif ($cell -eq $null) { $cell = \u00ab\u00a0\u00a0\u00bb }
\nAdd-Member -InputObject $line -MemberType NoteProperty -Name $ws.Cells.Item(1, $colonne).value2 -Value $cell
\nif ( $debugMode) {Write-host \u00ab\u00a0debug2 \u00a0\u00bb + $ws.Cells.Item($ligne,$colonne).value2 }
\n$colonne++
\n}
\n$ligne++
\n$object += $line
\nwrite-host -NoNewline \u00ab\u00a0`r\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 `r Progres : $ligne\/$maxLigne\u00a0\u00bb
\n}<\/div>\n
\u00a0 $wb.Close() | out-null
\n$xl.Quit() | out-null
\n[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | out-null<\/div>\n
\u00a0 Write-Host \u00ab\u00a0`r`n$ligne rows where converted.\u00a0\u00bb -foregroundcolor green
\nWrite-Host \u00ab\u00a0Saving datas to $csvFile. Please, wait…\u00a0\u00bb -foregroundcolor cyan
\n$object | Export-Csv $csvFile -NoTypeInformation -Delimiter ‘;’ -Encoding UTF8
\nWrite-Host \u00ab\u00a0Save completed.\u00a0\u00bb -foregroundcolor green
\nsleep 3
\n}<\/div>\n
<\/div>\n
<\/div>\n
Tadaaaaa…<\/div>\n","protected":false},"excerpt":{"rendered":"

Amusant. Si vous faites \u00ab\u00a0enregistrer sous\u00a0\u00bb et choisissez le format \u00ab\u00a0CSV\u00a0\u00bb et que vous avez des caract\u00e8res bizarres ( Comme \u00e9,\u00e8,&,..) Excel les d\u00e9truira lors de l’enregistrement (pas sympa) Si vous faites \u00ab\u00a0fichier > exporter\u00a0\u00bb et choisissez le format CSV, ca va mieux! mais c’est pas encore \u00e7a.. (les champs ne sont pas entre guillemets […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[58,44],"tags":[],"class_list":["post-521","post","type-post","status-publish","format-standard","hentry","category-powershell","category-scripting"],"_links":{"self":[{"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/posts\/521","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/comments?post=521"}],"version-history":[{"count":4,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/posts\/521\/revisions"}],"predecessor-version":[{"id":533,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/posts\/521\/revisions\/533"}],"wp:attachment":[{"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/media?parent=521"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/categories?post=521"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/tags?post=521"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}