XL 2013 Manipulations sur fichiers très volumineux

Aurelien74

XLDnaute Nouveau
Bonjour,

Je travaille souvent sur de gros fichiers Excel, avec plusieurs centaines de millier de lignes. Cela devient vite rédhibitoire pour effectuer des opérations très simples comme par exemple remplir une colonne B avec la valeur "1" pour des éléments filtrés (contenant une chaine de caractères) en colonne A. J'utilise en temps normal 2 techniques :
- cliquer-glisser de la 1ère cellule (avec la valeur 1) vers la dernière cellule de la colonne (B). Bon celle là, on l'oubli vite.
- Double clic sur la croix située dans le bord inférieur droit de la 1ère cellule. C'est le plus rapide que je connaisse, mais hélas le temps d'attente pour qu'Excel me rende la main dépasse bien souvent l'heure, ce qui se termine bien souvent par un kill.

J'aimerai savoir si vous aviez des techniques permettant d'écourter ce cas précis, et d'une manière plus générale pour gérer de gros fichiers Excel.

Merci pour votre retour,
Aurélien
 

bonoboas

XLDnaute Occasionnel
Re : Manipulations sur fichiers très volumineux

Bonjour Aurélien
Il pèse combien ton fichier ? Il y a des photos ? Comment est-il agencé ?

On pourrait avoir un extrait ?

Cordialement
 

Aurelien74

XLDnaute Nouveau
Re : Manipulations sur fichiers très volumineux

Bonjour bonoboas,

J'ai par exemple un fichier avec 10 colonnes et... 993137 lignes. Un peu plus de 200mo sur disque en csv, et environ 70mo en xlsx compression oblige.
Pour faire simple, ce sont des logs serveur avec URLs, code retour, valeurs divers. En exemple, j'ai attaché un fichier de 10 lignes anonymisé pour que tu puisses apprécier la structure. Dans mon fichier qui fait 993137 lignes, je souhaite créer par exemple une colonne K avec la valeur 1 pour toute les URLs ayant .html comme extension.

Merci pour l'aide...
 

Fichiers joints

bonoboas

XLDnaute Occasionnel
Re : Manipulations sur fichiers très volumineux

Tu veux obligatoirement travailler sur ce fichier ?
Tu ne veux pas faire des exports de données ou travailler en liaisons ?
 

Aurelien74

XLDnaute Nouveau
Re : Manipulations sur fichiers très volumineux

Oui je préfère travailler directement sur mon fichier de départ.
J'ai du mal à voir quelle serait ton approche avec les liaisons/exports... Peux-tu préciser (ça peut être une bonne piste) ?

Merci
 

bonoboas

XLDnaute Occasionnel
Re : Manipulations sur fichiers très volumineux

Re
Excuse moi Aurelien mais je ne comprends pas trop le but, ou plutôt la finalité. Tu veux exporter les lignes contenant un .html d'une colonne. C'est ça ?
 

job75

XLDnaute Barbatruc
Re : Manipulations sur fichiers très volumineux

Bonjour Aurelien74, bonoboas,

Pour modifier de très grands tableaux la méthode la plus rapide est d'utiliser des tableaux VBA.

Voyez le fichier joint et cette macro :

Code:
Sub Traiter()
Dim colsource$, coldest$, critere$, v, defaut$, t, i&
colsource = "F" 'colonne étudiée
coldest = "B" 'colonne à remplir
critere = "*.html" 'à adapter
v = 1
defaut = "-" 'valeur par défaut
t = Cells(1, colsource).Resize(Cells(Rows.Count, colsource).End(xlUp).Row + 1)
ReDim rest(1 To UBound(t), 1 To 1)
rest(1, 1) = Cells(1, coldest) 'titre
For i = 2 To UBound(t) - 1
  rest(i, 1) = IIf(t(i, 1) Like critere, v, defaut)
Next
Cells(1, coldest).Resize(UBound(t) - 1) = rest
End Sub
J'ai testé avec un tableau de 1 000 000 de lignes (tableau original copié 100 000 fois).

Le fichier pèse alors 35 Mo.

Sur Win 8 - Excel 2013 la macro s'exécute en 3,6 secondes.

A+
 

Fichiers joints

job75

XLDnaute Barbatruc
Re : Manipulations sur fichiers très volumineux

Re,

Si le tableau est filtré la macro précédente donne un résultat erroné.

Il faut préalablement afficher toutes les lignes :

Code:
Sub Traiter()
Dim colsource$, coldest$, critere$, v, defaut$, t, i&
colsource = "F" 'colonne à étudier
coldest = "B" 'colonne à remplir
critere = "*.html" 'à adapter
v = 1
defaut = "-" 'valeur par défaut
On Error Resume Next
ActiveSheet.ShowAllData 'si un filtre est appliqué
On Error GoTo 0
t = Cells(1, colsource).Resize(Cells(Rows.Count, colsource).End(xlUp).Row + 1)
ReDim rest(1 To UBound(t), 1 To 1)
rest(1, 1) = Cells(1, coldest) 'titre
For i = 2 To UBound(t) - 1
  rest(i, 1) = IIf(t(i, 1) Like critere, v, defaut)
Next
Cells(1, coldest).Resize(UBound(t) - 1) = rest
End Sub
Fichier (2).

A+
 

Fichiers joints

job75

XLDnaute Barbatruc
Re : Manipulations sur fichiers très volumineux

Re,

Une variante (2 bis) qui se contente de modifier les valeurs existantes en colonne B :

Code:
Sub Traiter()
Dim colsource$, coldest$, critere$, v, t, rest, i&
colsource = "F" 'colonne à étudier
coldest = "B" 'colonne à modifier
critere = "*.html" 'à adapter
v = 1
On Error Resume Next
ActiveSheet.ShowAllData 'si un filtre est appliqué
On Error GoTo 0
t = Cells(1, colsource).Resize(Cells(Rows.Count, colsource).End(xlUp).Row + 1)
rest = Cells(1, coldest).Resize(UBound(t))
rest(1, 1) = Cells(1, coldest) 'titre
For i = 2 To UBound(t) - 1
  If t(i, 1) Like critere Then rest(i, 1) = v
Next
Cells(1, coldest).Resize(UBound(t) - 1) = rest
End Sub
La durée d'exécution est inchangée.

A+
 

Fichiers joints

Discussions similaires


Haut Bas