XL 2016 Séparateurs décimales & milliers sur un import csv

[sabrina_]

Bonjour à tous,

Voilà mon tout premier post !
Je débute en VBA, et le forum a déjà répondu à plusieurs problématiques rencontrées, mais là je coince.

J'ai créé un outil Excel qui permet d'importer 2 fichiers csv via Power Query et qui va "analyser" le tout.
Les fichier sont importés de nos systèmes US.

Dans ma société nous avons plusieurs environnement différents avec des langues US, FR, etc..
Mon PC est en Français, séparateurs systèmes cochés, mais quand j'extrais mes csv de nos systèmes encodés en 12:00, les décimales sont des points.
J'ai dupliqué ma macro en créant un import Français & un Anglais, en utilisant "Application.International(xlCountryCode)" pour diriger les personnes ayant une version US (leur csv sont encodés en 12:52) vers la bonne macro.
Jusque là, ça fonctionne bien.

En revanche, pour certaines personnes qui ont des cvs 12:00 comme moi, et PC français, l'import des chiffres ne se fait pas car leur séparateur de décimal est en virgule. Du coup les colonnes ne se remplissent pas... L"Application.International(xlCountryCode)" ne fonctionne pas comme ils sont aussi en Français.

J'ai modifié la query avec leur paramètres, et cela fonctionne.
Mais...J'arrive pas à "détecter" en amont le "séparateur" pour lui dire :
- Si version anglaise alors macro N°Z
- Si version française mais séparateur "." alors macro N°X
- Si version française mais séparateur "," alors macro N°Y

Je me suis penchée sur Application.DecimalSeparator & Application.ThousandsSeparator mais j'arrive pas "diriger" ma macro. Je crois que j'arrive pas à m'en servir tout court.
Impossible de remplacer en amont la "," par le "." en amont comme cela passe directement par Power Query.

Voici le code qui fonctionne chez moi :

Public Sub ImportSPRFR()
Dim FileToLoad As String
Dim spr_sheet As String
Dim ConnexionName As String
Dim StrFormula As String

FileToLoad = SelectedExcel  'chemin du fichier SPR à charger - Demande à l'utilisateur de choisir le fichier SPR
spr_sheet = "SPR" 'Nom de la feuille contenant les données du SPR


'Ne pas modifier les lignes suivantes
ConnexionName = "SPR"
StrFormula = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & FileToLoad & """),[Delimiter=""#(tab)"", Columns=9, Encoding=1200, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Utiliser la première ligne pour les en-têtes"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Modifier le type"" = Ta" & _
        "ble.TransformColumnTypes(#""Utiliser la première ligne pour les en-têtes"",{{""Territory"", type text}, {""Payout Measure Name"", type text}, {""Detail Measure Name"", type text}, {""QTD Posted Revenue"", type number}, {""PY Qtr Rev (Gate Goal)"", type number}, {""Gate Attainment %"", type number}, {""Estimated YOY Growth"", type number}, {""Quarterly Goal"", type n" & _
        "umber}, {""Quarterly Attainment %"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Fractionner la colonne par délimiteur"" = Table.SplitColumn(#""Modifier le type"", ""Territory"", Splitter.SplitTextByDelimiter("" | "", QuoteStyle.Csv), {""Territory.1"", ""Territory.2"", ""Territory.3""})," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(#""Fractionner la colonne par délimiteur"",{{""" & _
        "Territory.1"", type text}, {""Territory.2"", type text}, {""Territory.3"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Colonnes renommées"" = Table.RenameColumns(#""Type modifié"",{{""Territory.1"", ""Territory""}, {""Territory.2"", ""Employee Name""}, {""Territory.3"", ""ID FedEx""}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Colonnes renommées"""
 

On Error GoTo ErrMgt

'On essaie de créer une nouvelle connexion
    ActiveWorkbook.Queries.Add Name:=ConnexionName, Formula:=StrFormula
   
    With Sheets(spr_sheet).ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SPR;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SPR]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = "_SPR"
        .Refresh BackgroundQuery:=True
    End With
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=True
Exit Sub

ErrMgt:
If Err.Number = -2147024809 Then
'Si la requete existe déjà et provoque une erreur, alors on modifie la requete existante

ActiveWorkbook.Queries(ConnexionName).Formula = StrFormula
   
Worksheets(spr_sheet).ListObjects("_SPR").Refresh

Else
    MsgBox Err.Number & " - " & Err.Description
    Exit Sub
End If

A chaque fois, c'est le "StrFormula" qui pose problème.

En remplaçant cette partie pour les autres versions, ça fonctionne bien chez les utilisateurs.
Reste à savoir comment combiner tout ça.

Version anglaise :

StrFormula = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & FileToLoad & """),[Delimiter="";"", Columns=27, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Utiliser la première ligne pour les en-têtes"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Modifier le" & _
        " type"" = Table.TransformColumnTypes(#""Utiliser la première ligne pour les en-têtes"",{{""TERR_ID"", type text}, {""VPNAME"", type text}, {""DIRECTORNAME"", type text}, {""MANAGERNAME"", type text}, {""EMPLOYEENAME"", type text}, {""FISCAL YEAR QUARTER"", type text}, {""GLBL_ENTI_LEVEL_NBR"", type number}, {""CNTRY_LEVEL_NBR"", type number}, {""FAC_LEVEL_NBR"", typ" & _
        "e number}, {""ENTITY_NAME"", type text}, {""FAC_NM"", type text}, {""ADDRESS"", type text}, {""CITY"", type text}, {""STATE"", type text}, {""COUNTRY"", type text}, {""FAC_PSTL"", Int64.Type}, {""EAN_LEVEL_NBR"", type number}, {""EAN_NBR"", Int64.Type}, {""MEASURE"", type text}, {""CYQ_REVENUE"", type text}, {""QUARTERLY_PY_REVENUE"", type text}, {""YOY +/- Total""," & _
        " type text}, {""YOY +/- ADNR"", type text}, {""QUARTERLY_POSTED_DAYS"", type text}, {""QUARTERLY_TOTAL_DAYS"", Int64.Type}, {""ACTIVE_OPP_FLG"", type text}, {""LAST_CALL_DATE"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionaux"" = Table.TransformColumnTypes(#""Modifier le type"", {{""CYQ_REVENUE"", type number}}, ""en-US"")," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec par" & _
        "amètres régionaux1"" = Table.TransformColumnTypes(#""Type modifié avec paramètres régionaux"", {{""QUARTERLY_PY_REVENUE"", type number}}, ""en-US"")," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionaux2"" = Table.TransformColumnTypes(#""Type modifié avec paramètres régionaux1"", {{""YOY +/- Total"", type number}}, ""en-US"")," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionau" & _
        "x3"" = Table.TransformColumnTypes(#""Type modifié avec paramètres régionaux2"", {{""YOY +/- ADNR"", type number}}, ""en-US"")," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionaux4"" = Table.TransformColumnTypes(#""Type modifié avec paramètres régionaux3"", {{""QUARTERLY_POSTED_DAYS"", type number}}, ""en-US"")," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionaux5"" = Table.Tr" & _
        "ansformColumnTypes(#""Type modifié avec paramètres régionaux4"", {{""QUARTERLY_TOTAL_DAYS"", type number}}, ""en-US"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionaux5"""

Et la version Française (séparateur ",")

StrFormula = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & FileToLoad & """),[Delimiter=""#(tab)"", Columns=9, Encoding=1200, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Utiliser la première ligne pour les en-têtes"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Modifier le type"" = Ta" & _
        "ble.TransformColumnTypes(#""Utiliser la première ligne pour les en-têtes"",{{""Territory"", type text}, {""Payout Measure Name"", type text}, {""Detail Measure Name"", type text}, {""QTD Posted Revenue"", type number}, {""PY Qtr Rev (Gate Goal)"", type number}, {""Gate Attainment %"", type number}, {""Estimated YOY Growth"", type number}, {""Quarterly Goal"", type n" & _
        "umber}, {""Quarterly Attainment %"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Fractionner la colonne par délimiteur"" = Table.SplitColumn(#""Modifier le type"", ""Territory"", Splitter.SplitTextByDelimiter("" | "", QuoteStyle.Csv), {""Territory.1"", ""Territory.2"", ""Territory.3""})," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(#""Fractionner la colonne par délimiteur"",{{""" & _
        "Territory.1"", type text}, {""Territory.2"", type text}, {""Territory.3"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Colonnes renommées"" = Table.RenameColumns(#""Type modifié"",{{""Territory.1"", ""Territory""}, {""Territory.2"", ""Employee Name""}, {""Territory.3"", ""ID FedEx""}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Colonnes renommées"""

Désolée pour le post à rallonge ^^
Et merci d'avance pour votre aide !

 

[chris]

Bonjour

Pas tout lu mais quand on importe on type ensuite les données et là on peut préciser le type de la données et notamment indiquer que la source est en mode US, ce qui marchera alors que la machine travaille sur le mode US ou sur le mode FR

On n'a pas à tripatouiller ensuite en VBA

 

[sabrina_]

Bonjour Chris,

Merci d'avoir pris le temps de me répondre.
En revanche, je ne comprends pas (désolée).

Les types de données sont directement implantées dans la query et ne sont pas modifiables. J'ai dû créer d'autres query.

Tu veux dire qu'on peut en amont dire que l'utilisateur utilise telles ou telles données et que ça fonctionnera ?
Via pplication.DecimalSeparator & Application.ThousandsSeparator j'imagine ?

Merci d'avance.

 

[chris]

RE

NON il faut modifier les types dans les requêtes, depuis l'interface PowerQuery (et non VBA)

Sinon tu perds tout l’intérêt des requêtes si tu ne les paramètres pas correctement...

Une fois cela fait la requête restitue des données correctement typées qu'Excel fonctionne en FR ou US

 

[sabrina_]

Bonjour Chris,

Oui, c'est ce que j'ai fait en créant mes requêtes via PowerQuery avec les paramêtres régionaux.

Le seul hic, c'est que suivant la configuration PC des uns & des autres, la Query ne fonctionne pas et ne restitue pas les données (colonnes vides).

Du coup j'ai dû créer 3 requêtes différentes avec les paramêtres de chacun (elles fonctionnent), mais je n'arrive pas à diriger ma macro en lui disant que si l'utilisateur à une Config X alors il va vers la Query X, etc...

Est-ce possible ?

 

[chris]

Bonjour

1200 correspond au type d'encodage pas aux paramètres de typage des données.

Ce sont des notions bien différentes.

Le typage utilisant les paramètres régionaux permet d'indiquer ceux de la source pour une conversion automatique vers ceux du PC donc il est curieux que cela ne fonctionne pas.

Dans ton code je ne vois pas de ligne comportant un typage précisant le typage de la source :
exemple
Table.TransformColumnTypes(#"En-têtes promus", {{"Montant", type number}}, "en-US")

Tu n'as que des typages classiques et donc indépendants du typage de la source

Créer les requêtes par VBA n'est pas la meilleure pratique.

On peut passer des paramètres aux requêtes via des tableaux de paramètres ou des cellules nommées dont le contenu sera utilisé par les requêtes.

Ainsi seul le remplissage de ces cellules est à gérer par VBA...

 

[sabrina_]

Re Chris,

J'ai créé les requêtes avec les fichiers source de mes collègues.

Sur mon PC où les décimales sont en "." & milliers " ", le {{"Montant", type number}} suffit.

Pour ceux dont la config est en anglais, j'ai bien un typage source en :
Table.TransformColumnTypes(#"En-têtes promus", {{"Montant", type number}}, "en-US")
Leur csv est encodé en 12:52 (différent des autres). J'ai été obligée de le préciser dans le type d'encodage sinon la requête ne fonctionnait pas.

Pour les versions FR où les décimales sont en "," & milliers "" idem, les requêtes précédentes ne fonctionnaient pas mais en créant avec leur fichier source ça fonctionne.

On peut passer des paramètres aux requêtes via des tableaux de paramètres ou des cellules nommées dont le contenu sera utilisé par les requêtes.

Ainsi seul le remplissage de ces cellules est à gérer par VBA...

Désolée, j'ai pas compris cette partie

 

[sabrina_]

PS : je viens de modifier les requêtes en typage source : {{"Montant", type number}}, "en-US")
Fonctionne très bien avec les imports que je fais, mais avec le fichier source d'une collègue qui est en FR voilà la restitution de PowerQuery :

- Pour le 1er csv il me donne 6261300 au lieu de 62613,00
- Pour le second, c'est pire : $18 845 800 000 000 000 000 000 000 000

 

[chris]

RE

Est-ce les CSV qui sont différents selon les personnes ou les paramètres régionaux des PC ou les deux ?

Cela n'est pas clair dans les explications...

Peux-tu joindre des extraits de CSV ?

 

[sabrina_]

Désolée

De ce que j'ai pu comprendre, les 2.

Voici les 3 csv différents :

1. - csv_Extract1 pour mon PC
2. -csv_Data CLG anglais
3. -csv-Data JLM français

 

[chris]

RE

Je m'absente une heure, je verrais au retour

 

[sabrina_]

Aucune urgence, je suis dessus depuis des semaines, ça peut bien attendre
Merci Chris.

 

[chris]

RE

Le 1er fichier est UTF16LE soit 1201 et en type US
Le second est en UTF8 soit 65001 et en type US
Le 3ème est comme le premier en 1201 mais en type FR

Je les ai chargé tous les 3 et typés en conséquence.
La recherche du séparateur décimal SD se fait sur le plus petit pourcentage Quarterly Attainment % : s'il contient une , c'est typé en FR sinon en US (sur un pourcentage on n'a pas de milliers)
Le typage est ensuite fait en selon le SD trouvé

J'ai vu que le fichier 2 a une colonne en moins : j'ai aussi fait une variante qui type tout sauf la 1ère colonne en number selon le SD

Si c'est OK on pourrait créer une fonction de typage à partir de ces 2 étapes SD + typages, voire aussi la troisième si ces 2 pourcentages existent toujours, ce qui simplifierait un chouia : voir requête utilisant la fonction

EDIT : j'ai ajouté le chemin en paramètre dans une cellule nommée (en jaune)
A modifier avant d'actualiser. Cela illustre le passage de paramètre que tu n'avais pas compris

Je te laisse tester et voir si tout fonctionne sur les divers PC...

 

[Hasco]

Bonjour,

J'avais commencé à faire une fonction de typage à partir des 7 colonnes.
Je vous la passe si ça vous interesse

(LaTable as table, optional culture as nullable text) =>
let
   Source ={{"Territory", type date}, {"QTD Posted Revenue", Currency.Type}, {"PY Qtr Rev (Gate Goal)", Currency.Type}, {"Gate Attainment %", Percentage.Type}, {"Estimated YOY Growth",Currency.Type}, {"Quarterly Goal", Currency.Type}, {"Quarterly Attainment %", Percentage.Type}},
   Typage = Table.TransformColumnTypes( LaTable, 
       List.Transform(Table.ColumnNames(LaTable),each let t = List.Zip(Source){0} in Source{ List.PositionOf(t,_)}),
       if culture = null then "fr-FR" else culture)
in Typage

@chris pas mal l'idée de détecter la fr-FR sur le pourcentage, je prends

A +

 

[sabrina_]

Merci beaucoup Chris, j'irai pas jusqu'à dire que je comprends , mais je vois la fonction typage dans les requêtes. Je vais regarder ça de plus près et tester.

Merci également Hasco Oui, ça intéresse toujours !