Ouvrir un fichier csv avec plus de 65535 lignes

**mustang-ffw02** · 07/10/2007, 12h08

Bonjour à tous,

J'ai un petit soucis pour traiter un fichier csv de plus de 65536 lignes.
Je voulais utilisé Excel 2003 avec VBA pour récupérer une partie du fichier csv, puis l'afficher sous excel (en passant ou pas par un nouveau fichier csv)

Problème : le nombre de lignes est limité à 65536 lignes.

Je vois quelques solutions:

1- Utiliser Access et VBA pour traiter le fichier et l'exporter sous excel
(Je ne sait absolument pas utilier VBA sous Acess, c'est très différent du VBA d'Excel...)

2- Utiliser un fichier bat (plus rapide dque VBA?)

Pour info, mon fichier csv fait environ 70000 lignes et 49 colonnes. Je veux récupérer les lignes qui dans la 18e colonne commence par "S" par exemple.

J'ai réussit en VBA Excel en utilisant "Set ts = fso.OpenTextFile(fichier)" et en regardant chaque ligne mais cela prend trop de temps (environ 5 minutes).

Avez-vous des solutions?
Merci par avance de votre participation.

**kiki29** · 07/10/2007, 15h58

Jour bon
Un embryon de solution , à adapter

**mustang-ffw02** · 07/10/2007, 19h12

Merci ca marche bien mais ca revient a ce que je faisais en lisant une à une les lignes de mon fichier et en insérant dans une feuille seulement celles qui m'intersse.

Mais ca reste long...
Sachant que j'ai 7 fichiers de 70000 lignes et de 48 colonnes!!!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
    Set ts = fso.OpenTextFile(fichier)
    nb_lignes = 0
    While ts.AtEndOfStream = False
        line_split = Split(ts.ReadLine, ";")
 
        'si la colonne 17 commence par S008 ou S11
        If (Left(line_split(17), 4) = "S008" Or Left(line_split(17), 3) = "S11") And Len(line_split(17)) = 12 Then            nb_lignes= nb_lignes + 1
            'alors je recupere la colonne 17 et 42
            Cells(nb_lignes, 1) = line_split(17)
            Cells(nb_lignes, 2) = line_split(42)
        End If
    Wend

J'ai une solution mais je ne sait pas la coder:
en passant par access, j'ouvre mon csv. je supprime toutes les entrées qui ne satisfons pas mes critères. J'exporte mon fichier ainsi allégé (environ 4000lignes pas plus) dans un classeur excel...

Quelqu'un sait coder en VBA Acess ce genre de scénario.

Merci de m'aider, je n'y connait rien en Acess VBA.

**SilkyRoad** · 07/10/2007, 19h53

bonsoir

tu pourrais par exemple utiliser la bibliothèque DAO pour extraire les données dans tes fichiers csv:

http://excel.developpez.com/sources/...mportRecordset

Ou le modèle ADO

http://excel.developpez.com/sources/...XT#CompilerTxt

http://excel.developpez.com/faq/?pag...sfertCsvAccess

Ou l'objet QueryTables

http://excel.developpez.com/sources/...oupeTxtFeuille

bonne soirée
michel

**mustang-ffw02** · 07/10/2007, 20h19

Bonsoir,

J'ai utilisé le code du premier lien:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Sub tranfertCSV_Vers_NouvelleTableAccess()
    'Transfére un fichier CSV vers une nouvelle table Access
    'depuis une macro Excel.
    '
    'Nécessite d'activer la référence
    '"Microsoft ActiveX Data Objects x.x Library
    '
 
    Dim AccessCn As ADODB.Connection
    Dim AccessRst As ADODB.Recordset
    Dim Csv_CN As New ADODB.Connection
    Dim Csv_Rst As New ADODB.Recordset
    Dim DossierCSV As String, NomTable As String
    Dim FichCSV As String, MaBase As String
    Dim nbEnr As Long
 
    'Répertoire du fichier CSV
    DossierCSV = "C:\Documents and Settings\mimi\dossier"
    'Nom du fichier CSV à transfèrer
    FichCSV = "LeFichierCSV.csv"
    'Chemin et nom de la base Access
    MaBase = "C:\Documents and Settings\mimi\dossier\dataBase.mdb"
    'Nom de la nouvelle Table Access
    NomTable = "MaNouvelleTable"
 
 
    'Connection au fichier CSV
    Csv_CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        DossierCSV & ";Extended Properties='text;FMT=Delimited'"
    'Requète dans le fichier CSV
    Csv_Rst.Open "SELECT * FROM " & FichCSV, Csv_CN, _
        adOpenStatic, adLockOptimistic
 
    'Connection à la base de données Access
    Set AccessCn = New ADODB.Connection
    AccessCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & MaBase
 
 
    Csv_CN.Execute "SELECT * INTO [" & NomTable & "] IN '" & _
        MaBase & "' From [" & FichCSV & "]", nbEnr
 
 
    AccessCn.Close
    Csv_Rst.Close
    Csv_CN.Close
    Set AccessRst = Nothing
    Set AccessCn = Nothing
    Set Csv_Rst = Nothing
    Set Csv_CN = Nothing
End Sub

J'obtiens l'erreur suivante lors de la Connection à la base de données Access
"Le moteur de la base de données Microsoft Jet ne peut pas ouvrir le fichier 'F:\Macros\Etiquettes'. Il est déjà ouvert en mode exclusif par un autre utilisateur, ou vous devez avoir l'autorisation de visualiser ses données."

Peut-etre cela vient il d'un référence mal cochée, il y a en plein qui commence par Microsoft ActiveX Data Objects x.x Library

**DarkVader** · 07/10/2007, 20h57

Envoyé par mustang-ffw02

Merci ca marche bien mais ca revient a ce que je faisais en lisant une à une les lignes de mon fichier et en insérant dans une feuille seulement celles qui m'intersse.

Mais ca reste long...
Sachant que j'ai 7 fichiers de 70000 lignes et de 48 colonnes!!!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
    Set ts = fso.OpenTextFile(fichier)
    nb_lignes = 0
    While ts.AtEndOfStream = False
        line_split = Split(ts.ReadLine, ";")
 
        'si la colonne 17 commence par S008 ou S11
        If (Left(line_split(17), 4) = "S008" Or Left(line_split(17), 3) = "S11") And Len(line_split(17)) = 12 Then            nb_lignes= nb_lignes + 1
            'alors je recupere la colonne 17 et 42
            Cells(nb_lignes, 1) = line_split(17)
            Cells(nb_lignes, 2) = line_split(42)
        End If
    Wend

...

Le principe énoncé ci-dessus est «en gros» le bon - suffit d'optimiser
Régle 1 : ne pas affecter directement les cellules lignes à ligne mais remplir un tableau
puis ensuite en fin de traitement coller le résultat dans une feuille Excel.
=> résultat visible garanti - pas de recalcul intermédiaire ni de rafraichissement d'affichage
plus efficace que bloquer le rafraichiissement (screenupdating) et le recalcul (calculation)
Régle 2 : il sera toujours temps d'y songer si la régle 1 ne se suffit pas à elle-même

PS : afin d'optimiser le code, évaluer le temps écoulé lors du traitement avec un gettickcount
ou un querypeformancecounter pour la précision ultime (pas nécessaire à ce niveau)

**kiki29** · 08/10/2007, 00h32

To mustang-ffw02 : A adapter ( Const Dossier ainsi que Ar(17) etc )
Bref tu parlais de lignes à récupérer puis elles deviennent des cellules .....
Sans doute à optimiser ( 40s pour traiter 600 000 lignes ) sur ma brouette

**DarkVader** · 08/10/2007, 09h44

kiki, il était temps que t'ailles te coucher.

**mustang-ffw02** · 08/10/2007, 18h27

Envoyé par kiki29

To mustang-ffw02 : A adapter ( Const Dossier ainsi que Ar(17) etc )
Bref tu parlais de lignes à récupérer puis elles deviennent des cellules .....
Sans doute à optimiser ( 40s pour traiter 600 000 lignes ) sur ma brouette

Merci pour ce code, après un bref test, 40 seconde pour traiter un de mes fichiers (70000 lignes, 48 colonnes, environ 25 mo).

Par contre, ou le resultat est-il stocké? fichier texte, excel, cellule? A quoi sert la methode print?

Et plus généralement, pour mon information, d'où sortent ces open, close, line input...De quelle classe sortent ces méthodes?

**mustang-ffw02** · 08/10/2007, 18h44

Envoyé par DarkVader

Régle 1 : ne pas affecter directement les cellules lignes à ligne mais remplir un tableau
puis ensuite en fin de traitement coller le résultat dans une feuille Excel.

Quant tu parles de coller le tableau dans une feuille excel, tu fais ca en gros:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
For i = 1 to ubound(tableau)
 
    cells(i,1).value=tableau(i)
 
next i

Si c'est ca, je ne comprend pas le gain de temps. Sinon, il y a peut-etre un moyen de coller le tableau en une fois (sans boucle) et la je sais pas faire...

**kiki29** · 08/10/2007, 22h10

Par contre, ou le resultat est-il stocké?

Au niveau de Lecture2.xls sous forme d'un fichier nommé Resultats.csv

Ton temps de traitement pour un fichier m'apparait vraiment très long ( perso 5s pour 70000 lignes 50 colonnes )

Pour le reste [F1] Aide en ligne

**DarkVader** · 09/10/2007, 10h57

Envoyé par mustang-ffw02

Quant tu parles de coller le tableau dans une feuille excel, tu fais ca en gros:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
For i = 1 to ubound(tableau)
 
    cells(i,1).value=tableau(i)
 
next i

Si c'est ca, je ne comprend pas le gain de temps. Sinon, il y a peut-etre un moyen de coller le tableau en une fois (sans boucle) et la je sais pas faire...

surtout pas, utilises plutôt la méthode suivante qui est quasi instantanée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
dim plage as range
set plage=[Feuil1!A1] ' positionnement de l'origine de la plage => 1ere cellule haut gauche
set plage=plage.resize(ubound(tableau()),ubound(tableau(),2)) 'la plage cible est adaptée à la taille au tableau
plage=tableau()  'revient à faire plage.value=tableau()

**mustang-ffw02** · 09/10/2007, 13h51

Je ne connaissait absolument pas cette méthode. Vraiment un gros gain de temps.

J'ai par contre un petit soucis de maniulation de tableau:

J'ai un tableau dimensionner comme ceci (3 dimensions):
ReDim Extract(1 To 7), 1 To 20000, 1 To 2)

Ce sont les informations de mes 7 fichiers dont je recupere maximum 20000 lignes (pour etre large) et 2 colonnes.

Pour utiliser la méthode de DarkVader, il me faut un tableau à 2 dimensions...
Comment faire? Y a t il un moyen de passer d'un tableau a 3 dimension à un plusieurs tableaux (dans mon cas 7) a 2 dimensions ?

J'espere avoir été assez clair!?

**mustang-ffw02** · 09/10/2007, 19h23

J'ai finalement modifier mon code pour avoir un tableau a deux dimensions, donc c'est ok.

Merci à tous de votre participation.

**DarkVader** · 09/10/2007, 20h37

A titre d'information pour d'autres utilisateurs
indique en combien de temps s'exécute maintenant ton traitement qui à l'origine était de 5mn.

**mustang-ffw02** · 09/10/2007, 20h46

Pour 7 fichiers de 70000 lignes environs et de 48 colonnes (soit 20mo par fichiers) le durée de traitement était de 25 à 30min (soit 4-5 minutes par fichiers).

Maintenant, au lieu de remplir les cellules à chaque récupération d'une ligne dans le fichier csv, je stocke l'information dans un tableau. Enfin, à la fin de la lecture, j'affiche le tableau en une seule fois dans une feuille. Avec cett nouvelle méthode, la durée du traitement est de 5-6 minutes (sur un serveur réseaux en plus...)

Merci a tous pour votre participation et de votre aide précieuse...

Ouvrir un fichier csv avec plus de 65535 lignes

Macros et VBA Excel

Discussions similaires

Partager

Partager