En mi entrada de blog anterior JMP y Excel: ¿una historia sin fin? - Parte 1 Terminé con un ejemplo de importación de hojas de cálculo con encabezados jerárquicos y celdas combinadas. Esta vez me concentro en importar un archivo de hoja de cálculo con muchas hojas de cálculo, así como en leer varias hojas de cálculo (ubicadas en una carpeta específica) en JMP. No quiero hacerlo demasiado complicado, por lo tanto, asumo que la estructura y los nombres de los parámetros en las hojas de cálculo, así como en las diferentes hojas de trabajo, son los mismos.
(Nota: si no es lo mismo, deberá especificar la configuración para cada libro e importarlos como tablas separadas. Se pueden combinar posteriormente utilizando las funciones de unión/concatenación o JMP Query Builder para tablas de datos JMP. Si desea automatizar este tipo de pasos, generalmente se requieren secuencias de comandos adicionales para verificar la función de cada variable, hoja de trabajo y/o archivo que desea leer).
MÚLTIPLES HOJAS DE TRABAJO: IMPORTAR Y AUTOMATIZAR
Entonces, primero veamos los datos en Excel:
Se pueden ver los diferentes libros de trabajo, todos con la misma forma y estructura no trivial. Subrayados en rojo se pueden ver los diferentes números de lote en cada libro. Quiero leer los datos pero no en tablas separadas. Más bien me gustaría tener una sola tabla y usar el Asistente de importación de Excel de JMP es bastante fácil de lograr:
- Arrastre la hoja de Excel a cualquier ventana JMP y se abrirá el Asistente de importación de Excel:
- Realice cambios en la configuración del encabezado y los campos de datos cuando corresponda. Tenemos un encabezado que no queremos incluir en los datos y el encabezado real comienza en la línea 4 y abarca tres filas. Luego hay una fila con campos vacíos. Por lo tanto comenzamos los datos una línea más tarde (8 en lugar de 7). La primera columna no tiene datos para el análisis, por eso la omitimos.
- Al hacer clic en el botón "Siguiente", podemos utilizar la configuración de Última fila, indicando que no queremos incluir la información de la marca de tiempo. Seleccionando la fila donde terminan los datos y presionando el signo más (+) no tienes que preocuparte por qué número de línea es el correcto.
- Lo restante es hacer uso de la misma estructura dentro de todas las hojas de trabajo. Podemos marcar el marcador "Usar para todas las hojas de trabajo", así como el marcador "Concatenar hojas de trabajo e intentar hacer coincidir columnas". Esto cambiará la vista previa de 30/30 filas a 90/90 filas. Teniendo en cuenta que cada una de las tres hojas de trabajo proporciona 30 filas, esto parece funcionar como se esperaba. Al concatenar, se recomienda encarecidamente marcar también la marca "Crear columna con el nombre de la hoja de trabajo al concatenar". De lo contrario, será difícil reflejar más adelante de dónde proceden los datos.
- El último paso es importar y por supuesto comprobar siempre los datos importados.
Si quieres ver esto en acción te lo recomiendo. video corto (usando JMP 12). Una nota en caso de que recién haya comenzado a usar el Asistente de importación: guardará la última configuración, lo que puede generar vistas sorprendentes de los datos al importar datos nuevos, pero diferentes. Usando el botón "Restaurar configuración predeterminada" puede restablecer su configuración en el asistente.
En cada tabla de datos importados encontrará un script fuente para rehacer la importación con la configuración que haya elegido previamente. Esto permite leer una tabla de datos actualizada, en caso de que tenga nuevos datos de lote en la hoja de cálculo. Bueno, cuando recopilo datos con frecuencia probablemente consideraría usar bases de datos en lugar de hojas de cálculo, pero esa es una historia diferente.
IMPORTACIÓN AUTOMATIZADA DE VARIAS HOJAS DE CÁLCULO: DIFERENTE DESAFÍO
Ahora ya sabe cómo leer una hoja de cálculo con varias hojas de trabajo con la misma estructura en JMP. Sin embargo, es posible que también haya enfrentado el desafío “¿Cómo leo varias hojas de cálculo con la misma estructura en JMP sin hacer esto manualmente para cada archivo, es decir, hacerlo automáticamente? La respuesta corta, utilizando JMP Scripting Language. Utilizando el script de origen como base, puede volver a ejecutar este script para cada hoja de cálculo o utilizar un bucle for que recorra todos los archivos de una carpeta. Hay un script compartido en la comunidad File Exchange que podrías usar (tal vez con algunos ajustes), que hace exactamente esto: Combinar varios archivos txt o csv
En resumen, siga los siguientes pasos (solo se muestran partes del script):
- Elija el directorio y filtre los archivos que sean archivos de Excel.
prefilepath = munger(Pick Directory( "Browse to the Directory of the Target Excel Files " ),1,"/","");
filepath = Convert File Path( prefilepath, Windows );
prefilelist = Files In Directory( filepath );
n2=nitems(prefilelist);
//filter out any non-Excel files
For( i2 = 1, n2 >= i2, i2++,
file=(prefilelist[i2]);
If( Item( 2, prefilelist[i2], "." ) == "xls" | Item( 2, prefilelist[i2], "." ) == "xlsx",
Insert Into( filelist,file),
//show(file)
)
);
- Recorra los archivos e impórtelos. Para ahorrar memoria, abra solo dos y actualice la primera tabla importada con la nueva, cierre esta última y abra la siguiente. De lo contrario, puede tener problemas en Windows debido a la cantidad de identificadores de ventana utilizados, así como a la memoria utilizada (en el caso de muchas o muy grandes hojas de cálculo).
n=nitems(filelist); //number of items in the working list
cctable= New Table( "Combined data table");//make an empty table
ExcelSheetName = "Sheet1"; // change this if you have a special sheet name you want to read the data from
//Iterate through building the SQL string to query the Excel files
For( i = 1, i < n+1, i++,
filenow = ( filelist[i] );
fileopen=(filepath||filenow);
dt=open(fileopen, Worksheets( ExcelSheetName ) );
dt << New Column( "File Name", formula( filenow ) );
cctable << Concatenate( Data Table( dt ), Append to first table );
Close( dt, NoSave );
);//end of for loop
Hasta ahora ha leído sobre varias formas de importar datos estructurados triviales, así como sobre cómo manejar conjuntos de datos jerárquicos y/o combinados utilizando el Asistente de importación de Excel en JMP. Finalmente, le mostré formas de importar varias hojas de trabajo a la vez y cómo leer automáticamente varias hojas de Excel con un script JSL. En mi próxima publicación hablaré sobre posibles desafíos basados en el formato de los datos y otros.
Espero sus comentarios y experiencias compartidas.
