Convertir des fichiers CSV en classeurs Excel

Article
02/07/2024

De nombreux services exportent des données sous forme de fichiers de valeurs séparées par des virgules (CSV). Cette solution automatise le processus de conversion de ces fichiers CSV en classeurs Excel au format de fichier .xlsx. Il utilise un flux Power Automate pour rechercher des fichiers avec l’extension .csv dans un dossier OneDrive et un script Office pour copier les données du fichier .csv dans un nouveau classeur Excel.

Solution

Stockez les fichiers .csv et un fichier de .xlsx « Modèle » vide dans un dossier OneDrive.
Créez un script Office pour analyser les données CSV dans une plage.
Créez un flux Power Automate pour lire les fichiers .csv et transmettre leur contenu au script.

Exemples de fichiers

Téléchargez convert-csv-example.zip pour obtenir le fichier Template.xlsx et deux exemples de fichiers .csv. Extrayez les fichiers dans un dossier de votre OneDrive. Cet exemple suppose que le dossier est nommé « output ».

Ajoutez le script suivant à l’exemple de classeur. Dans Excel, utilisez Automatiser>le nouveau script pour coller le code et enregistrer le script. Enregistrez-le sous Convertir csv et essayez l’exemple vous-même !

Exemple de code : Insérer des valeurs séparées par des virgules dans un classeur

/**
 * Convert incoming CSV data into a range and add it to the workbook.
 */
function main(workbook: ExcelScript.Workbook, csv: string) {
  let sheet = workbook.getWorksheet("Sheet1");

  // Remove any Windows \r characters.
  csv = csv.replace(/\r/g, "");

  // Split each line into a row.
  // NOTE: This will split values that contain new line characters.
  let rows = csv.split("\n");

  /*
   * For each row, match the comma-separated sections.
   * For more information on how to use regular expressions to parse CSV files,
   * see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753
   */
  const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
  rows.forEach((value, index) => {
    if (value.length > 0) {
      let row = value.match(csvMatchRegex);
    
      // Check for blanks at the start of the row.
      if (row[0].charAt(0) === ',') {
        row.unshift("");
      }
  
      // Remove the preceding comma and surrounding quotation marks.
      row.forEach((cell, index) => {
        cell = cell.indexOf(",") === 0 ? cell.substring(1) : cell;
        row[index] = cell.indexOf("\"") === 0 && cell.lastIndexOf("\"") === cell.length - 1 ? cell.substring(1, cell.length - 1) : cell;
      });
    
      // Create a 2D array with one row.
      let data: string[][] = [];
      data.push(row);
  
      // Put the data in the worksheet.
      let range = sheet.getRangeByIndexes(index, 0, 1, data[0].length);
      range.setValues(data);
    }
  });

  // Add any formatting or table creation that you want.
}

Flux Power Automate : créer des fichiers .xlsx

Connectez-vous à Power Automate et créez un flux cloud planifié.
Définissez le flux sur Répéter tous les « 1 » « jour », puis sélectionnez Créer.
Obtenez le fichier Excel du modèle. C’est la base de tous les fichiers .csv convertis. Dans le générateur de flux, sélectionnez le + bouton et Ajouter une action. Sélectionnez l’action Obtenir le contenu du fichier du connecteur OneDrive Entreprise. Indiquez le chemin d’accès au fichier « Template.xlsx ».
- Fichier : /output/Template.xlsx
Renommez l’étape Obtenir le contenu du fichier . Sélectionnez le titre actuel, « Obtenir le contenu du fichier », dans le volet Office action. Remplacez le nom par « Obtenir le modèle Excel ».
Ajoutez une action qui obtient tous les fichiers du dossier « output ». Choisissez l’action Lister les fichiers dans le dossier du connecteur OneDrive Entreprise. Indiquez le chemin d’accès au dossier qui contient les fichiers .csv.
- Dossier : /output
Ajoutez une condition afin que le flux fonctionne uniquement sur les fichiers .csv. Ajoutez l’action de contrôle Condition . Utilisez les valeurs suivantes pour condition.
- Choisissez une valeur : Nom (contenu dynamique à partir de Lister les fichiers dans le dossier). Notez que ce contenu dynamique a plusieurs résultats. Par conséquent, un contrôle For each entoure la condition.
- se termine par (dans la liste déroulante)
- Choisissez une valeur : .csv
Le reste du flux se trouve sous la section Si oui , car nous voulons uniquement agir sur .csv fichiers. Obtenez un fichier .csv individuel en ajoutant une action qui utilise l’action Obtenir le contenu du fichier du connecteur OneDrive Entreprise. Utilisez l’ID du contenu dynamique de la liste des fichiers dans le dossier .
- Fichier : Id (contenu dynamique de l’étape Lister les fichiers dans le dossier )
Renommez la nouvelle étape Obtenir le contenu du fichier en « Obtenir .csv fichier ». Cela permet de distinguer ce fichier du modèle Excel.
Créez le nouveau fichier .xlsx en utilisant le modèle Excel comme contenu de base. Ajoutez une action qui utilise l’action Créer un fichier du connecteur OneDrive Entreprise. Utilisez les valeurs ci-après.
- Chemin du dossier : /output
- Nom de fichier : nom sans extension.xlsx (choisissez le contenu dynamique Nom sans extensiondans lister les fichiers dans le dossier et tapez manuellement « .xlsx » après celui-ci)
- Contenu du fichier : contenu du fichier (contenu dynamique à partir du modèle Obtenir Excel)
Exécutez le script pour copier des données dans le nouveau classeur. Ajoutez l’action Exécuter le script du connecteur Excel Online (Business). Utilisez les valeurs suivantes pour l’action.
- Emplacement : OneDrive Entreprise
- Bibliothèque de documents : OneDrive
- Fichier : ID (contenu dynamique à partir de Créer un fichier)
- Script : Convertir un fichier CSV
- csv : Contenu du fichier (contenu dynamique à partir de l'.csv fichier)
Enregistrez le flux. Le concepteur de flux doit ressembler à l’image suivante.
Utilisez le bouton Test de la page de l’éditeur de flux ou exécutez le flux via votre onglet Mes flux . Veillez à autoriser l’accès lorsque vous y êtes invité.
Vous devez trouver de nouveaux fichiers .xlsx dans le dossier « output », ainsi que les fichiers .csv d’origine. Les nouveaux classeurs contiennent les mêmes données que les fichiers CSV.

Résolution des problèmes

Test de script

Pour tester le script sans utiliser Power Automate, affectez une valeur à csv avant de l’utiliser. Ajoutez le code suivant comme première ligne de la main fonction et sélectionnez Exécuter.

  csv = `1, 2, 3
         4, 5, 6
         7, 8, 9`;

Fichiers séparés par des points-virgules et autres séparateurs alternatifs

Certaines régions utilisent des points-virgules (';') pour séparer les valeurs de cellule au lieu des virgules. Dans ce cas, vous devez modifier les lignes suivantes dans le script.

Remplacez les virgules par des points-virgules dans l’instruction d’expression régulière. Cela commence par let row = value.match.
```
let row = value.match(/(?:;|\n|^)("(?:(?:"")*[^"]*)*"|[^";\n]*|(?:\n|$))/g);
```
Remplacez la virgule par un point-virgule dans le case activée pour la première cellule vide. Cela commence par if (row[0].charAt(0).
```
if (row[0].charAt(0) === ';') {
```
Remplacez la virgule par un point-virgule dans la ligne qui supprime le caractère de séparation du texte affiché. Cela commence par row[index] = cell.indexOf.
```
   row[index] = cell.indexOf(";") === 0 ? cell.substr(1) : cell;
```

Remarque

Si votre fichier utilise des onglets ou tout autre caractère pour séparer les valeurs, remplacez dans ; les substitutions ci-dessus par \t ou tout caractère utilisé.

Fichiers CSV volumineux

Si votre fichier comporte des centaines de milliers de cellules, vous pouvez atteindre la limite de transfert de données Excel. Vous devez forcer le script à se synchroniser régulièrement avec Excel. Le moyen le plus simple d’effectuer cette opération consiste à appeler console.log après le traitement d’un lot de lignes. Ajoutez les lignes de code suivantes pour y parvenir.

Avant rows.forEach((value, index) => {, ajoutez la ligne suivante.
```
  let rowCount = 0;
```
Après range.setValues(data);, ajoutez le code suivant. Notez qu’en fonction du nombre de colonnes, vous devrez peut-être réduire 5000 à un nombre inférieur.
```
  rowCount++;
  if (rowCount % 5000 === 0) {
    console.log("Syncing 5000 rows.");
  }
```

Avertissement

Si votre fichier CSV est très volumineux, vous pouvez rencontrer des problèmes d’expiration dans Power Automate. Vous devez diviser les données CSV en plusieurs fichiers avant de les convertir en classeurs Excel.

Accents et autres caractères Unicode

Les fichiers avec des caractères unicode spécifiques, tels que des voyelles accentuées comme é, doivent être enregistrés avec l’encodage correct. La création du fichier de connecteur OneDrive de Power Automate est définie par défaut sur ANSI pour les fichiers .csv. Si vous créez les fichiers .csv dans Power Automate, vous devez ajouter la marque d’ordre d’octet (BOM) avant les valeurs séparées par des virgules. Pour UTF-8, remplacez le contenu du fichier pour l’opération d’écriture .csv fichier par l’expression concat(uriComponentToString('%EF%BB%BF'), <CSV Input>) (où <CSV Input> sont vos données CSV d’origine).

Notez que cet exemple ne crée pas les fichiers .csv dans le flux. Cette modification doit donc se produire dans votre partie personnalisée du flux. Vous pouvez également lire et réécrire les fichiers .csv avec la nomenclature, si vous ne contrôlez pas la façon dont ces fichiers sont créés.

Guillemets environnants

Cet exemple supprime les guillemets (« ») qui entourent les valeurs. Celles-ci sont généralement ajoutées à des valeurs séparées par des virgules pour empêcher que les virgules dans les données soient traitées comme des jetons de séparation. Un fichier .csv ouvert dans Excel, puis enregistré en tant que fichier .xlsx, ne verra jamais les guillemets affichés au lecteur. Si vous souhaitez conserver les guillemets et les afficher dans les feuilles de calcul finales, remplacez les lignes 27 à 30 du script par le code suivant.

// Remove the preceding comma.
row.forEach((cell, index) => {
  row[index] = cell.indexOf(",") === 0 ? cell.substring(1) : cell;
});

Partager via