Cómo dividir una hoja de cálculo de Excel CSV enorme en archivos separados

Excel es excelente en tantas tareas del día a día. Pero de vez en cuando, encuentras uno de sus defectos: el tamaño de una hoja de cálculo. Si necesita saber cómo hacer que una hoja de cálculo de Excel sea más pequeña o cómo dividir un CSV grande, lo tenemos cubierto..

Alcanzar el límite de filas de la hoja de cálculo es una tarea bastante seria en sí misma. El límite actual de la fila de la hoja de cálculo de Excel es de 1.048.576. Así es, más de 1 millón de filas, y la misma cantidad de columnas, también.

Pero alcanzar ese límite de fila es realmente increíblemente fácil, durante ciertas tareas. Si está comercializando por correo electrónico, puede importar un archivo CSV Cómo importar datos a sus hojas de cálculo de Excel de la manera fácil y sencilla Cómo importar datos a sus hojas de cálculo de Excel de la manera clara y fácil ¿Alguna vez le ha costado importar o exportar datos a ¿una hoja de cálculo? Este tutorial lo ayudará a dominar el arte de mover datos entre Microsoft Excel, CSV, HTML y otros formatos de archivo. Leer más que contiene millones de direcciones de correo electrónico. El único problema es cómo gestionas realmente una hoja de cálculo con tantas direcciones? Además, ¿qué pasa si alguien le envía un CSV que ya está por encima del límite (de otro programa)??

Bueno, voy a mostrarles cómo dividir ese enorme CSV en trozos fácilmente manejables..

(¿No tiene un CSV grande a mano? Hay algunos conjuntos de datos de CSV bastante grandes disponibles. 15 Bases de datos en línea masivas que debe conocer 15 Bases de datos en línea masivas que debe saber sobre Internet documenta todo. Algunos de esos datos se concentran en bases de conocimiento masivas. Enciclopedias o bases de datos en línea: la pregunta es: ¿sabe cuáles son las mejores? Lea más en data.gov. Voy a usar la lista de comparación de hospitales de 260,000 filas, para que pueda usar el mismo archivo.)

1. Usa un programa

Hay una serie de programas divisores CSV útiles por ahí. Te mostraré dos de los mejores.

CSV Chunker

CSV Chunker es un divisor de código abierto CSV. Puede manejar archivos masivos, dividiéndolos rápidamente en trozos de su elección. Tomó aproximadamente 3 segundos dividir nuestro Hospital Compare CSV en 106 porciones, que contenían 2,500 filas cada una..

Divisor CSV

CSV Splitter es nuestra segunda herramienta. Ofrece la misma funcionalidad de CSV Chunker, aunque con un diseño ligeramente más elegante. El separador de CSV tomó aproximadamente 4 segundos para dividir nuestro CSV de comparación de hospitales en 106, nuevamente con 2.500 filas cada una.

2. Utilice un archivo por lotes

A continuación, un archivo por lotes programable Cómo escribir un archivo de lote simple (BAT) Cómo escribir un archivo de lote simple (BAT) Los archivos de lote pueden automatizar las tareas diarias de la computadora. Así es como funcionan los archivos por lotes y cómo escribir su primer script por lotes. Lee mas . Podemos usar un archivo por lotes para procesar el CSV en trozos más pequeños, personalizando el archivo para entregar diferentes trozos.

Abra un nuevo documento de texto. Copia y pega lo siguiente:

@echo off setlocal ENABLEDELAYEDEXPANSION REM Edite este valor para cambiar el nombre del archivo que necesita división. Incluye la extensión. SET BFN = HCAHPSHospital.csv REM Edite este valor para cambiar el número de líneas por archivo. SET LPF = 2500 REM Edite este valor para cambiar el nombre de cada archivo corto. Le seguirá un número que indica dónde se encuentra en la lista. SET SFN = HosptialSplitFile REM No cambie más allá de esta línea. SET SFX =% BFN: ~ -3% SET / A LineNum = 0 SET / A FileNum = 1 Para / F "delimita ==" %% l in (% BFN%) Do (SET / A LineNum + = 1 echo %% l >>% SFN%! FileNum!.% SFX% if! LineNum! EQU! LPF! (SET / A LineNum = 0 SET / A FileNum + = 1)) Endlocal Pause

Tendrá que configurar el archivo por lotes antes de ejecutar. Le diré lo que hace cada comando y puede modificarlo para que se adapte al tamaño de su archivo por lotes, así como a la salida requerida..

• “SET BFN =” debe apuntar a la CSV que necesita para romper
• “SET LPF =” es el número de filas a las que le gustaría limitar su nuevo archivo a
• “SET SFN =” es el nuevo esquema de nombres para tus archivos divididos

Una vez que haya ingresado sus variables, diríjase a Archivo> Guardar como. Elija el nombre de archivo y seleccione Salvar. Luego, seleccione su archivo de texto recién guardado y presione F2 para renombrarlo Reemplace la extensión TXT con BAT y presione DE ACUERDO cuando aparece la advertencia. Ahora puedes dividir tu gran CSV!

Sin embargo, te advierto que este método lleva algún tiempo. Probablemente estés mejor usando uno de los divisores antes mencionados..

El script y el hilo original se pueden encontrar aquí.

3. Utilice un script de PowerShell

Los archivos por lotes son útiles. Puede utilizarlos para una amplia gama de tareas diarias. Pero los scripts de PowerShell son, por lo general, más rápidos, especialmente para este tipo de procesamiento y división..

La siguiente secuencia de comandos cortará rápidamente su CSV grande en archivos más pequeños. Una vez más, he usado el Hospital Compare CSV para mi ejemplo. El comando tardó unos 3 segundos en ejecutarse. Abra el ISE de Windows PowerShell. Si no está seguro de cómo hacer esto, escriba powerhell ise en su búsqueda del menú Inicio y seleccione la opción correspondiente. Cuando el ISE de PowerShell está abierto, seleccione el Mostrar guión Flecha, ubicada en la esquina superior derecha (resaltada en rojo en la imagen de abajo).

Copie y pegue el siguiente comando en el panel de script, en la parte superior de su pantalla.

$ i = 0; Get-Content C: \ Users \ Gavin \ Downloads \ Hospital_Revised_Flatfiles \ HCAHPSHospital.csv -ReadCount 2500 | % $ i ++; $ _ | Archivo de salida C: \ Users \ Gavin \ Downloads \ Hospital_Revised_Flatfiles \ split \ splitfile_ $ i.csv

Primero, necesitas cambiar la ubicación de tu archivo de entrada. En segundo lugar, debe especificar cuántas filas deben contener sus archivos de salida. Por último, especifique la ubicación del archivo de salida. He dejado mis rutas de archivo allí para ilustrar la situación. Además, su nombre de archivo de salida debe tener la “_ $ i.csv”sufijo coincidente con el mio. De lo contrario, sus archivos de salida no se numerarán correctamente.

Cuando haya editado el archivo con sus propios datos, presione CTRL + S para guardar tu guion.

El script original se puede encontrar aquí..

4. Cargarlo en un modelo de datos

Nuestra solución final no es realmente dividir el CSV grande en partes más pequeñas. Verá, José Barreto, miembro del equipo de OneDrive, recientemente ilustró exactamente cómo puede cargar un archivo CSV grande en Excel. En este caso, medios grandes sobre el límite de fila de 1,048,567. Explica en un tutorial que, en lugar de cargar el archivo, Excel 2016 puede crear un enlace de datos al archivo CSV..

Los datos del CSV original estarán disponibles para el usuario dentro de un Modelo de datos. Barreto creó una tabla dinámica utilizando “Hasta 8.5 millones de filas sin ningún problema..” La imagen de arriba está tomada de la publicación del blog de Barreto (enlazada a continuación), mostrando un total de 2 millones de filas en Excel. (Aprenda a usar una tabla dinámica para el análisis de datos Cómo usar una tabla dinámica de Excel para el análisis de datos Cómo usar una tabla dinámica de Excel para el análisis de datos) La tabla dinámica es una de las herramientas más poderosas en el repertorio de Excel 2013. Es se utiliza con frecuencia para el análisis de datos extensos. Siga nuestra demostración paso a paso para obtener más información al respecto. Lea más.

Si esta es la solución que más le convenga, siga el tutorial detallado de José Barreto aquí. Recuerde, esto no es dividir el CSV, pero significa que puede manipular sus datos dentro de ciertas herramientas de Excel. Cómo visualizar su análisis de datos con Power Tools de Excel. Cómo visualizar su análisis de datos con Power Tools Excel de Excel lo está matando con su avanzada. Funciones de gestión de datos. Una vez que haya utilizado una de las nuevas herramientas, las querrá todas. ¡Conviértete en un maestro de tu análisis de datos con herramientas de poder! Lee mas . Si accede a sus datos para interactuar con ellos directamente, podría valer la pena intentarlo..

Descomponerlo!

Hemos enumerado cuatro soluciones para desglosar sus archivos CSV masivos, incluidas dos herramientas diferentes.

De las cuatro soluciones, el archivo por lotes es probablemente el más lento. 5 razones por las que debe usar PowerShell en lugar de los lotes. 5 razones por las que debe usar PowerShell en lugar de los lotes. en algunas características adicionales, y lo pateó todo en varias muescas. Aquí hay varias razones por las que debe intentarlo. Lee mas . La secuencia de comandos de PowerShell es rápida y fácilmente personalizable, mientras que las dos aplicaciones son fáciles de usar listas para usar. Finalmente, tiene la opción de importar directamente sus datos a Excel, por los millones de filas.

¿Qué método eliges? ¿Y tienes algún otro consejo? Por favor comparte con nosotros en los comentarios.!

Crédito de la imagen: lucadp /

Explorar más sobre: ​​Microsoft Excel, Hoja de cálculo.