Tengo una carpeta grande de archivos CSV grandes (alrededor de 25,000 archivos en la carpeta y aumentará aún más), es decir, casi todos los archivos tienen más filas que el límite de filas para Excel (supongo que el límite era de 1 millón). Todos estos archivos CSV tienen 5 elementos delimitados por comas en cada fila con un número variable de filas (información) en todos los archivos.

One CSV File:
a1,b1,c1,d1,e1
a2,b2,c2,d2,e2
.
.
.
a3152685,b3152685,c3152685,d3152685,e3152685

My Reference File:
x1,y1
x2,y2
x3,y3
.
.
.
x397,y397

Esencialmente, necesitaré acceder solo a algunas de estas filas (alrededor de 400) desde cada archivo CSV basado en mi archivo de referencia. Siempre que pueda hacer coincidir la pareja xy con la pareja ab en cualquier archivo CSV, guardaré la fila a, b, c, d, e con el título del archivo CSV en otro lugar, preferiblemente un archivo Excel pero estoy abierto a ideas .

Puedo trabajar con Matlab, Python 2.7, MS Access (convertir archivos CSV en archivos de base de datos parecía una buena idea si no tuviera que hacerlo para cada archivo, ¿hay una versión por lotes para hacerlo?) O MS Excel . Nunca he hecho nada de VBA, pero si tiene alguna solución de VBA para este problema, también estoy dispuesto a escuchar eso.

Avíseme si necesita más aclaraciones en caso de que no estuviera lo suficientemente claro.

1
Umut C. 16 feb. 2017 a las 09:54

3 respuestas

La mejor respuesta

En caso de que alguien necesite una respuesta, la manejé con MATLAB.

La función de almacenamiento de datos en MATLAB es lo que estaba buscando.

ds = datastore(MyReferenceFile);
TableExtracted = readall(ds);

Entonces el resto fue simplemente find(ismember) hacerse cargo.

También existe la función de búsqueda de lotes (el tamaño del lote se asigna en ReadSize) de datastore; Sin embargo, fue predeterminado a 20000 y supongo que ese era también el límite. Era demasiado lento para mi gusto, así que recurrí a readall y todavía fue bastante rápido.

1
Umut C. 18 mar. 2017 a las 13:33

Puede encontrar los límites de la oficina productos aquí

Matlab es bueno para trabajar con estos archivos grandes y grandes conjuntos de archivos. La versión 2014 tiene muchas mejoras para ese intrincado almacén de datos para csv, ahora también funciona bastante bien con archivos de Excel.

Echa un vistazo a este tutorial:

http://blogs.mathworks.com/loren/2014/12/03/reading-big-data-into-matlab/

Tengo 3 archivos csv (archivo [1-3] .csv) que contiene esto:

a1,b1,c1,d1,e1
a2,b2,c2,d2,e2
a3,b3,c3,d3,e3
a4,b4,c4,d4,e4
a5,b5,c5,d5,e5
a6,b6,c6,d6,e6
a7,b7,c7,d7,e7
a8,b8,c8,d8,e8
a9,b9,c9,d9,e9
a10,b10,c10,d10,e10

Y un archivo varnames para los nombres de las columnas:

A B C D E

Leamos los archivos:

>> datafile = 'csv-files/file1.csv';
>> headerfile = 'csv-files/varnames.txt'

>> fileID = fopen(headerfile);
>> varnames = textscan(fileID,'%s');
>> varnames = varnames{:};

ds = datastore(datafile,'ReadVariableNames',false);

>> ds.VariableNames = varnames


ds = 

  TabularTextDatastore with properties:

                      Files: {
                             '/home/anquegi/learn/matlab/stackoverflow/csv-files/file1.csv'
                             }
               FileEncoding: 'UTF-8'
          ReadVariableNames: false
              VariableNames: {'A', 'B', 'C' ... and 2 more}

  Text Format Properties:
             NumHeaderLines: 0
                  Delimiter: ','
               RowDelimiter: '\r\n'
             TreatAsMissing: ''
               MissingValue: NaN

  Advanced Text Format Properties:
            TextscanFormats: {'%q', '%q', '%q' ... and 2 more}
         ExponentCharacters: 'eEdD'
               CommentStyle: ''
                 Whitespace: ' \b\t'
    MultipleDelimitersAsOne: false

  Properties that control the table returned by preview, read, readall:
      SelectedVariableNames: {'A', 'B', 'C' ... and 2 more}
            SelectedFormats: {'%q', '%q', '%q' ... and 2 more}
                   ReadSize: 20000 rows


>> preview(ds)

ans = 

     A       B       C       D       E  
    ____    ____    ____    ____    ____

    'a1'    'b1'    'c1'    'd1'    'e1'
    'a2'    'b2'    'c2'    'd2'    'e2'
    'a3'    'b3'    'c3'    'd3'    'e3'
    'a4'    'b4'    'c4'    'd4'    'e4'
    'a5'    'b5'    'c5'    'd5'    'e5'
    'a6'    'b6'    'c6'    'd6'    'e6'
    'a7'    'b7'    'c7'    'd7'    'e7'
    'a8'    'b8'    'c8'    'd8'    'e8'

Si miramos el parámetro ReadSize que tomamos es ReadSize: 20000 filas, por lo que matlab lee cada 20000 filas y puede procesar. Como los datos solo tienen 10 filas, los cambiaré a tres:

>> ds.ReadSize=3

ds = 

  TabularTextDatastore with properties:

                      Files: {
                             '/home/anquegi/learn/matlab/stackoverflow/csv-files/file1.csv'
                             }
               FileEncoding: 'UTF-8'
          ReadVariableNames: false
              VariableNames: {'A', 'B', 'C' ... and 2 more}

  Text Format Properties:
             NumHeaderLines: 0
                  Delimiter: ','
               RowDelimiter: '\r\n'
             TreatAsMissing: ''
               MissingValue: NaN

  Advanced Text Format Properties:
            TextscanFormats: {'%q', '%q', '%q' ... and 2 more}
         ExponentCharacters: 'eEdD'
               CommentStyle: ''
                 Whitespace: ' \b\t'
    MultipleDelimitersAsOne: false

  Properties that control the table returned by preview, read, readall:
      SelectedVariableNames: {'A', 'B', 'C' ... and 2 more}
            SelectedFormats: {'%q', '%q', '%q' ... and 2 more}
                   ReadSize: 3 rows

>> reset(ds)
while hasdata(ds)
      T = read(ds);
      T.A
end

ans = 

    'a1'
    'a2'
    'a3'


ans = 

    'a4'
    'a5'
    'a6'


ans = 

    'a7'
    'a8'
    'a9'


ans = 

    'a10'

Entonces, la variable T es una tabla que puede escribir donde desee: tenga en cuenta que cada vez que lee (ds) mueve el número de líneas asignadas por readsie, este parámetro puede ser filas o archivos

>> reset(ds)
>> T = read(ds);
>> T

T = 

     A       B       C       D       E  
    ____    ____    ____    ____    ____

    'a1'    'b1'    'c1'    'd1'    'e1'
    'a2'    'b2'    'c2'    'd2'    'e2'
    'a3'    'b3'    'c3'    'd3'    'e3'

>> writetable(T,'mySpreadsheet','FileType','spreadsheet')
>> reset(ds)
1
anquegi 16 feb. 2017 a las 12:28

Esto puede estar fuera de tema, pero creo que debería considerar SQL Server y SSIS. Puede recorrer fácilmente todos los archivos de una carpeta, cargarlos todos en SQL Server y luego mover los archivos fuera de la carpeta. La próxima vez que los archivos se vuelquen en su carpeta, ejecute el proceso nuevamente en estos archivos nuevos. Vea el enlace a continuación para todos los detalles.

https://www.mssqltips.com/sqlservertip/2874/loop-through-flat-files-in-sql-server-integration-services/

O use SQL puro para hacer el trabajo.

--BULK INSERT MULTIPLE FILES From a Folder 

--a table to loop thru filenames drop table ALLFILENAMES
CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))

--some variables
declare @filename varchar(255),
        @path     varchar(255),
        @sql      varchar(8000),
        @cmd      varchar(1000)


--get the list of files to process:
SET @path = 'C:\Dump\'
SET @cmd = 'dir ' + @path + '*.csv /b'
INSERT INTO  ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null


--cursor loop
declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.csv%'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
  begin
  --bulk insert won't take a variable name, so make a sql and execute it instead:
   set @sql = 'BULK INSERT Temp FROM ''' + @path + @filename + ''' '
       + '     WITH ( 
               FIELDTERMINATOR = '','', 
               ROWTERMINATOR = ''\n'', 
               FIRSTROW = 2 
            ) '
print @sql
exec (@sql)

  fetch next from c1 into @path,@filename
  end
close c1
deallocate c1


--Extras

--delete from ALLFILENAMES where WHICHFILE is NULL
--select * from ALLFILENAMES
--drop table ALLFILENAMES

Desde aquí:

Importar múltiples archivos CSV a SQL Server desde una carpeta

Access no manejará esta cantidad de fecha y, como ya sabe, Excel ni siquiera se acercará.

Una cosa más a considerar es usar R, que es totalmente gratuito y muy rápido.

A menudo nos encontramos con situaciones en las que tenemos datos en múltiples archivos, a diferentes frecuencias y en diferentes subconjuntos de observaciones, pero nos gustaría unirlos entre sí de la manera más completa y sistemática posible. En R, el comando merge () es una excelente manera de unir dos marcos de datos.

Solo lea los dos marcos de datos en R

mydata1 = read.csv(path1, header=T)
mydata2 = read.csv(path2, header=T)

Entonces, fusionar

myfulldata = merge(mydata1, mydata2)

Siempre que mydata1 y mydata2 tengan al menos una columna común con un nombre idéntico (que permita hacer coincidir las observaciones en mydata1 con las observaciones en mydata2), esto funcionará de maravilla. También toma tres líneas.

¿Qué sucede si tengo 20 archivos con datos que quiero hacer coincidir observación con observación? Suponiendo que todos tengan una columna común que permita la fusión, todavía tendría que leer 20 archivos en (20 líneas de código) y merge () funciona dos por dos ... para poder fusionar los 20 marcos de datos junto con 19 declaraciones de fusión Me gusta esto:

mytempdata = merge(mydata1, mydata2)
mytempdata = merge(mytempdata, mydata3)
.
.
.
mytempdata = merge(mytempdata, mydata20)

Eso es tedioso Quizás estés buscando una manera más simple. Si es así, escribí una función para resolver sus problemas llamada multmerge (). * Aquí está el código para definir la función:

multmerge = function(mypath){
filenames=list.files(path=mypath, full.names=TRUE)
datalist = lapply(filenames, function(x){read.csv(file=x,header=T)})
Reduce(function(x,y) {merge(x,y)}, datalist)

Después de ejecutar el código para definir la función, ya está todo listo para usarlo. La función toma un camino. Esta ruta debe ser el nombre de una carpeta que contiene todos los archivos que le gustaría leer y fusionar, y solo aquellos archivos que le gustaría fusionar. Con esto en mente, tengo dos consejos:

Antes de usar esta función, mi sugerencia es crear una nueva carpeta en un directorio corto (por ejemplo, la ruta de esta carpeta podría ser "C: // R // mergeme") y guardar todos los archivos que desea fusionarse en esa carpeta. Además, asegúrese de que la columna que hará la coincidencia esté formateada de la misma manera (y tenga el mismo nombre) en cada uno de los archivos. Suponga que guardó sus 20 archivos en la carpeta mergeme en "C: // R // mergeme" y desea leerlos y fusionarlos. Para usar mi función, usa la siguiente sintaxis:

mymergeddata = multmerge(“C://R//mergeme”)

Después de ejecutar este comando, tiene un marco de datos completamente fusionado con todas sus variables combinadas entre sí. Por supuesto, la mayoría de los detalles en la coincidencia y combinación de datos se reduce a asegurarse de que la columna común se especifique correctamente, pero dado que esta función puede ahorrarle mucho tipeo.

Una vez que todo se haya fusionado en 1 marco de datos, expórtelo a un archivo de texto o un archivo CSV y cárguelo en masa en SQL Server.

0
Community 23 may. 2017 a las 10:29