Discussion :

[Tetranos]

Bonjour,

Je ne sais pas si je suis dans la bonne section pour poser cette question puisqu'il est question à la fois d'Excel, OleDB, PowerShell et Windows Server.

J'utilise dans un script PowerShell une requête SQL via OleDb pour exporter en CSV des données contenues dans un fichier XLSX.
Le but est de rendre cet export possible sans avoir à utiliser Excel.
Le code s'appuie sur "Microsoft.ACE.OLEDB.12.0".

Le code a fait ses preuves depuis un moment puisqu'il tourne chaque jour grâce à une tâche planifiée lancée sur un Windows Server 2016 (VM).

Je suis actuellement en train de migrer vers un Windows Server 2022 (VM) et je constate depuis des erreurs aléatoires d'export.

J'ai fait de nombreux tests pour tenter de comprendre ce problème mais sans succès.

En l'état, j'ai un script qui boucle 100 fois sur un même fichier Excel pour en exporter les mêmes données vers un CSV.

A chaque exécution sur le Windows Server 2022, sur les 100 boucles, quelques unes provoquent une erreur "External table is not in the expected format.".
Toutes les recherches que j'ai pu faire sur cette erreur me mènent à des articles qui ne correspondent pas à mon contexte, le plus souvent il est dit que c'est un problème du driver Jet.OleDB et qu'il faut utiliser ACE...ce que je fais déjà.

Les 100 boucles passent sans pb si je lance le script depuis le Windows Server 2016.

Enfin, sur le Windows Server 2022, j'ai pu constater que si le fichier est sur un disque local (et non pas sur un emplacement réseau) alors toutes les boucles passent bien.

J'ai pensé à un moment à une intervention de l'antivirus, mais en le désactivant c'est pareil.
J'ai aussi testé Microsoft.ACE.OLEDB.16.0...même résultat.

Code de la fonction d'export :

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
function Exporter-CSV-Excel {
    param (
        [string] $fournisseur = 'Microsoft.ACE.OLEDB.12.0',
        [parameter(Mandatory=$true)] [string] $excel,
        [string] $proprietesEtendues = '',
        [parameter(Mandatory=$true)] [string] $requete,
        [parameter(Mandatory=$true)] [string] $csv,
        [string] $delimiteur = (Get-Culture).TextInfo.ListSeparator
    )
 
    "Export de $requete depuis $excel vers $csv."
 
    if ($proprietesEtendues.Equals('')) {
 
        if ($excel.ToLower().EndsWith('.xls')) {
            $proprietesEtendues = 'Excel 8.0; HDR=Yes; IMEX=1; Mode=Read'
        }
        else {
            $proprietesEtendues = 'Excel 12.0 Xml; HDR=Yes; IMEX=1; Mode=Read'
        }
    }
 
    $chaineConnexion = "Provider=$fournisseur; Data Source=$excel; Extended Properties=`"$proprietesEtendues`";"
 
    $connexion = [Data.OleDb.OleDbConnection]::new($chaineConnexion)
    $commande = [Data.OleDb.OleDbCommand]::new($requete, $connexion)
    $adapteur = [Data.OleDb.OleDbDataAdapter]::new($commande)
    $resultat = [Data.DataTable]::new()
 
    $adapteur.Fill($resultat)
 
    if (Test-Path $csv) { Remove-Item -LiteralPath $csv }
    New-Item -ItemType Directory -Force -Path (Split-Path -Path $csv)
    $resultat | Export-Csv -Path $csv -Encoding UTF8 -Delimiter $delimiteur -NoTypeInformation
 
    $resultat.Dispose()
    $commande.Dispose()
    $connexion.Close()
 
    [GC]::Collect()
}

Boucle d'appel

Code PowerShell :

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

1
2
3
for ($i = 1; $i -lt 100; $i++) {
    Exporter-CSV-Excel -excel "$dossierSource\test.xlsx" -requete 'select Id from [Types de recueil$]' -csv "$dossierDestination\Route+ - Types de recueil.csv"
}

Auriez-vous une idée ? Merci,

[jurassic pork]

Hello,
Si tu fais le test avec un fichier xls (pas xlsx) est-ce que cela fonctionne-t-il ?
Si tu mets une temporisation dans la boucle, cela fonctionne-t-il?
Ami calmant, J.P

[Tetranos]

Bonjour,

Merci pour l'intérêt porté au sujet.

J'avais déjà fait le test avec une tempo d'1 seconde, ça ne changeait rien.
Je re-testé en allant jusqu'à 5 secondes de pause, l'erreur se produit quand même.

Je n'avais pas testé avec un XLS...j'ai donc fait le test en partant du même fichier Excel que j'ai enregistré en XLS.
Dans ce cas, l'erreur ne se produit pas. J'ai lancé 3-4 fois pourtant (soit 300-400 boucles) alors qu'avec le XLSX j'ai au moins une erreur à chaque exécution des 100 boucles.

Sur ce constat, j'ai essayé de refaire des tests avec les XLSX mais en changeant les paramètres de propriétés étendues ; l'erreur survient quelque soit le paramètres passés.

Cordialement,

[jurassic pork]

hello,
tu peux regarder ici : excel-external-table-is-not-in-the-expected-format pour voir si il n'y a pas une des solutions proposées qui fonctionne.
Ami calmant, J.P

[Tetranos]

Je n'ai pas trouvé de solution dans le lien proposé.

J'ai finalement mis en place dans le code une copie locale du fichier avant traitement.
C'est un peu cracra mais ça fonctionne.

Je testerai d'ici quelques temps de désactiver cette copie pour voir si les futures MAJ de l'OS ne viennent pas corriger le pb.

Merci encore.