Conversión de archivos CSV en libros de Excel

Artículo
02/07/2024

Muchos servicios exportan datos como archivos de valores separados por comas (CSV). Esta solución automatiza el proceso de conversión de esos archivos CSV en libros de Excel en el formato de archivo .xlsx. Usa un flujo de Power Automate para buscar archivos con la extensión .csv en una carpeta de OneDrive y un script de Office para copiar los datos del archivo .csv en un nuevo libro de Excel.

Solución

Almacene los archivos .csv y un archivo de .xlsx "Plantilla" en blanco en una carpeta de OneDrive.
Cree un script de Office para analizar los datos CSV en un intervalo.
Cree un flujo de Power Automate para leer los archivos .csv y pasar su contenido al script.

Archivos de ejemplo

Descargue convert-csv-example.zip para obtener el archivo Template.xlsx y dos archivos de .csv de ejemplo. Extraiga los archivos en una carpeta de OneDrive. En este ejemplo se supone que la carpeta se denomina "output".

Agregue el siguiente script al libro de ejemplo. En Excel, use AutomateNew Script (Automatizar >nuevo script) para pegar el código y guardar el script. Guárdelo como Convertir CSV y pruebe el ejemplo usted mismo!

Código de ejemplo: insertar valores separados por comas en un libro

/**
 * 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.
}

Flujo de Power Automate: creación de nuevos archivos de .xlsx

Inicie sesión en Power Automate y cree un nuevo flujo de nube programado.
Establezca el flujo en Repetir cada "1" "Día" y seleccione Crear.
Obtenga el archivo de Excel de plantilla. Esta es la base de todos los archivos .csv convertidos. En el generador de flujos, seleccione el + botón y Agregar una acción. Seleccione la acción Obtener contenido de archivo del conector de OneDrive para la Empresa. Proporcione la ruta de acceso del archivo al archivo "Template.xlsx".
- Archivo: /output/Template.xlsx
Cambie el nombre del paso Obtener contenido de archivo . Seleccione el título actual, "Obtener contenido de archivo", en el panel de tareas de acción. Cambie el nombre a "Obtener plantilla de Excel".
Agregue una acción que obtiene todos los archivos de la carpeta "output". Elija la acción Lista de archivos en carpeta del conector de OneDrive para la Empresa. Proporcione la ruta de acceso de la carpeta que contiene los archivos .csv.
- Carpeta: /output
Agregue una condición para que el flujo solo funcione en .csv archivos. Agregue la acción control Condición . Use los siguientes valores para la condición.
- Elija un valor: Nombre (contenido dinámico de Archivos de lista en la carpeta). Tenga en cuenta que este contenido dinámico tiene varios resultados, por lo que un control For each rodea la condición.
- termina con (de la lista desplegable)
- Elija un valor: .csv
El resto del flujo se encuentra en la sección Si sí , ya que solo queremos actuar en .csv archivos. Para obtener un archivo .csv individual, agregue una acción que use la acción Obtener contenido de archivo del conector de OneDrive para la Empresa. Use el identificador del contenido dinámico de los archivos de lista de la carpeta .
- Archivo: Identificador (contenido dinámico del paso Lista de archivos en la carpeta )
Cambie el nombre del nuevo paso Obtener contenido de archivo a "Obtener .csv archivo". Esto ayuda a distinguir este archivo de la plantilla de Excel.
Cree el nuevo archivo .xlsx, usando la plantilla de Excel como contenido base. Agregue una acción que use la acción Crear archivo del conector de OneDrive para la Empresa. Use los siguientes valores.
- Ruta de acceso de carpeta: /output
- Nombre de archivo: nombre sin.xlsx de extensión (elija el nombre sin contenido dinámico de extensión de los archivos de lista en la carpeta y escriba manualmente ".xlsx" después de él)
- Contenido del archivo: contenido de archivo (contenido dinámico de la plantilla Obtener Excel)
Ejecute el script para copiar datos en el nuevo libro. Agregue la acción Ejecutar script del conector de Excel Online (Empresa). Use los siguientes valores para la acción.
- Ubicación: OneDrive para la Empresa
- Biblioteca de documentos: OneDrive
- Archivo: Identificador (contenido dinámico de Crear archivo)
- Script: Conversión de CSV
- csv: contenido del archivo (contenido dinámico de Obtener archivo .csv)
Guarde el flujo. El diseñador de flujo debe tener un aspecto similar al de la imagen siguiente.
Use el botón Probar de la página del editor de flujo o ejecute el flujo a través de la pestaña Mis flujos . Asegúrese de permitir el acceso cuando se le solicite.
Debe encontrar nuevos archivos de .xlsx en la carpeta "salida", junto con los archivos de .csv originales. Los nuevos libros contienen los mismos datos que los archivos CSV.

Solución de problemas

Pruebas de script

Para probar el script sin usar Power Automate, asigne un valor a csv antes de usarlo. Agregue el código siguiente como primera línea de la main función y seleccione Ejecutar.

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

Archivos separados por punto y coma y otros separadores alternativos

Algunas regiones usan punto y coma para separar los (';') valores de celda en lugar de comas. En este caso, debe cambiar las líneas siguientes en el script.

Reemplace las comas por punto y coma en la instrucción de expresión regular. Esto comienza por let row = value.match.
```
let row = value.match(/(?:;|\n|^)("(?:(?:"")*[^"]*)*"|[^";\n]*|(?:\n|$))/g);
```
Reemplace la coma por un punto y coma en la comprobación de la primera celda en blanco. Esto comienza por if (row[0].charAt(0).
```
if (row[0].charAt(0) === ';') {
```
Reemplace la coma por un punto y coma en la línea que quita el carácter de separación del texto mostrado. Esto comienza por row[index] = cell.indexOf.
```
   row[index] = cell.indexOf(";") === 0 ? cell.substr(1) : cell;
```

Nota:

Si el archivo usa pestañas o cualquier otro carácter para separar los valores, reemplace en ; las sustituciones anteriores por \t o cualquier carácter que se esté usando.

Archivos CSV grandes

Si el archivo tiene cientos de miles de celdas, podría alcanzar el límite de transferencia de datos de Excel. Tendrá que forzar que el script se sincronice con Excel periódicamente. La manera más fácil de hacerlo es llamar console.log a después de que se haya procesado un lote de filas. Agregue las siguientes líneas de código para que esto suceda.

Antes de rows.forEach((value, index) => {, agregue la siguiente línea.
```
  let rowCount = 0;
```
Después de range.setValues(data);, agregue el código siguiente. Tenga en cuenta que, en función del número de columnas, es posible que tenga que reducir 5000 a un número inferior.
```
  rowCount++;
  if (rowCount % 5000 === 0) {
    console.log("Syncing 5000 rows.");
  }
```

Advertencia

Si el archivo CSV es muy grande, es posible que tenga problemas para agotar el tiempo de espera en Power Automate. Tendrá que dividir los datos CSV en varios archivos antes de convertirlos en libros de Excel.

Acentos y otros caracteres Unicode

Los archivos con caracteres específicos de Unicode, como vocales acentuadas como é, deben guardarse con la codificación correcta. La creación de archivos del conector de OneDrive de Power Automate tiene como valor predeterminado ANSI para los archivos .csv. Si va a crear los archivos de .csv en Power Automate, deberá agregar la marca de orden de bytes (BOM) antes de los valores separados por comas. Para UTF-8, reemplace el contenido del archivo para la operación de escritura .csv archivo por la expresión concat(uriComponentToString('%EF%BB%BF'), <CSV Input>) (donde están los <CSV Input> datos CSV originales).

Tenga en cuenta que este ejemplo no crea los archivos .csv en el flujo, por lo que este cambio debe producirse en la parte personalizada del flujo. También puede leer y reescribir los archivos .csv con bom, si no controla cómo se crean esos archivos.

Comillas circundantes

En este ejemplo se quitan las comillas ("") que rodean los valores. Normalmente se agregan a valores separados por comas para evitar que las comas de los datos se traten como tokens de separación. Un archivo de .csv que se abre en Excel y, a continuación, se guarda como un archivo .xlsx, nunca mostrará esas comillas al lector. Si desea mantener las comillas y hacer que se muestren en las hojas de cálculo finales, reemplace las líneas 27-30 del script por el código siguiente.

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