XL 2019 extraire des données d'un fichier Excel sans l'ouvrir avec VBA

[Kenza18]

Bonjour,

je souhaite extraire les données d'un fichier Excel très volumineux (lire le fichier sans l'ouvrir).

les données que je veux extraire sont les suivantes :

les cellules du champ code-techno == '4GF'. --> [ ce champ se trouve sur la colonne D]

Est ce que c'est possible de m'aider à trouver une solution avec VBA s'il vous plait ?

En vous remerciant d'avance pour vote aide.



Cordialement,
Kenza

 

[chris]

RE

Access peut importer un csv mais je ne sais pour cette taille

 

[soan]

@chris, @Kenza18,

comme tu avais écrit qu'Access ne peut pas lire des fichiers .csv de plus d'un million de lignes,
je croyais que tu parlais de créer un nouveau fichier Access, en mettant toutes les données
dans des tables Access.

par la même occasion, ça permettrait à @Kenza18 de profiter de la puissance de la Base de
Données Access (SGBDR = Système de Gestion de Bases de Données Relationnelles).

soan

 

[Kenza18]

@chris, @Kenza18,

comme tu avais écrit qu'Access ne peut pas lire des fichiers .csv de plus d'un million de lignes,
je croyais que tu parlais de créer un nouveau fichier Access, en mettant toutes les données
dans des tables Access.

par la même occasion, ça permettrait à @Kenza18 de profiter de la puissance de la Base de
Données Access (SGBDR = Système de Gestion de Bases de Données Relationnelles).

soan

vous pouvez m'expliquer comment cela est possible s'il vous plait ?

 

[soan]

Bonjour @Kenza18, (post modifié et complété ce matin, le 21 décembre)

il faudrait que tu saches utiliser Access, et comme pour tout nouveau logiciel, il faut
un temps d'apprentissage ! ce serait long pour t'expliquer comment t'en servir, et en
plus, on est ici sur le Forum Excel ! pour Access, c'est dans : « Questions / Autres
applications » ; tu peux aussi faire une recherche google avec ces trois mots :
« cours access gratuit ».

j'ai ajouté tout ce qui est écrit ci-dessous ; bonne lecture !

-------------------------------------------------------------------------------------------------

tu sais déjà ce qu'est un traitement de textes comme Word et un tableur comme Excel ;
pour une Base de données comme Access, ça se passe en 2 temps : 1) stocker toutes
les données dans des tables ; 2) interroger la Base de données pour faire ressortir les
informations qui nous sont utiles.

-------------------------------------------------------------------------------------------------

voici un résumé très court : dans Access, il y a en particulier : les Tables ; Requêtes ;
Formulaires ; États ; il y a en plus les relations entre les tables, les macros, et les
modules.

-------------------------------------------------------------------------------------------------

* toutes les données d'une base Access sont stockées dans les Tables, et uniquement
dans ces Tables ; la notion de clé primaire est vraiment très importante, car c'est
elle qui permet d'accéder aux données d'une Table ; y'a aussi un principe d'unicité :
les données ne doivent pas comporter de doublons, et surtout pas pour la clé primaire,
sinon, Access ne pourrait pas déterminer quelle est la « fiche » qui est concernée.

* les Requêtes permettent d'interroger la base et de faire ressortir les données qui
nous intéressent ; même s'il est possible de modifier les données dans des « feuilles
de réponse », c'est quand même les données des Tables qui sont modifiées ; on dit
que les Tables sont sous-jacentes aux Requêtes ; notion QBE = Query By Exemple
= Requête par l'exemple.

* les Formulaires permettent de mieux présenter les données des Tables, à raison
d'un seul enregistrement par Formulaire ; un enregistrement est une ligne de
données, les différents éléments de cette ligne étant les champs ; exemples :
Nom ; Prénom ; Adresse ; Code Postal ; Ville ; là aussi, même s'il est possible
de modifier les données dans les formulaires, c'est quand même les données
des Tables qui sont modifiées ; on dit que les Tables sont sous-jacentes aux
Formulaires.

* les États permettent de mieux présenter les données à imprimer ; on ne peut
pas modifier les données des États ; les États sont basés sur les Tables et / ou
sur les Requêtes.

-------------------------------------------------------------------------------------------------

pour plus d'infos, tu peux suivre le lien de mon post #35 ci-dessous.

soan

 

[soan]

@Kenza18

voici un lien : Cours Guide Microsoft Access 2010 gratuit en PDF

si tu as une autre version d'Office, tu peux me dire laquelle, et j'essayerai
de te trouver un autre lien pour l'Access de cette autre version d'Office.

-------------------------------------------------------------------------------

regarde aussi les propositions de @job75 et @R@chid ci-dessous :
post #36 et post #37 ; peut-être que t'auras pas besoin d'Access ?
mais c'est quand même intéressant de connaître ce logiciel.

soan

 

[job75]

Voici la macro pour importer un fichier texte .txt :

Sub Importer_TXT()
Dim t#, chemin$, fichier$, ncol%, texte$, i&, s, n&, a(), col%, resu(), dest As Range
t = Timer
chemin = ThisWorkbook.Path & "\" 'à adapter
fichier = "Source.txt" 'à adapter
ncol = 8 'nombre de colonnes à copier
'---lecture séquentielle du fichier texte---
Open chemin & fichier For Input As #1 'ouverture du fichier texte
Do While Not EOF(1) 'EndOfFile: fin du fichier
    Line Input #1, texte 'récupère la ligne
    i = i + 1
    s = Split(texte, vbTab) 'séparateur : tabulation
    If i = 1 Or s(3) = "4GF" Then 'critère en 4ème colonne
        n = n + 1
        ReDim Preserve a(1 To ncol, 1 To n)
        For col = 1 To ncol
            a(col, n) = s(col - 1)
        Next col
    End If
Loop
Close #1 'fermeture du fichier texte
'---transposition---
ReDim resu(1 To n, 1 To ncol)
For i = 1 To n
    For col = 1 To ncol
        resu(i, col) = a(col, i)
Next col, i
'---restitution dans la feuille Excel quand c'est possible---
With Feuil1 'CodeName à adapter
    If .FilterMode Then .ShowAllData
    Set dest = .[A1] '1ère cellule de destination, à adapter
    If n > .Rows.Count - dest.Row + 1 Then MsgBox "Tableau trop grand pour Excel !", 48: Exit Sub
    If n Then dest.Resize(n, ncol) = resu
    dest.Offset(n).Resize(.Rows.Count - n - dest.Row + 1, ncol).ClearContents 'RAZ en dessous
    With .UsedRange: End With 'actualise les barres de défilement
End With
MsgBox "Durée " & Format(Timer - t, "0.00 \sec"), , "Import"
End Sub

Testez les fichiers zippés joints, chez moi l'import se fait en 0,45 seconde.

Le fichier Source.txt a 40 000 lignes.

 

[R@chid]

Bonjour @ tous,
@Kenza18
On ne sait pas comment tu vas utiliser ces données après l'importation, sinon, lors de l'importation via Power Query, tu peux toutefois ne créer que la connexion avec le fichier source et ajouter les données importées au modèle de données pour faire des calculs avec des TCDs ou PowerPivot.

Cordialement

 

[chris]

Bonjour à tous

On ne sait pas comment tu vas utiliser ces données après l'importation, sinon, lors de l'importation via Power Query, tu peux toutefois ne créer que la connexion avec le fichier source et ajouter les données importées au modèle de données pour faire des calculs avec des TCDs ou PowerPivot.

Il n'est pas nécessaire d'ajouter les données au modèle PowerPivot pour faire un TCD à partir d'une requête
On évite même en général sauf besoin de mesures particulières dans un TCD car le modèle de données est lourd

Comme tu avais écrit qu'Access ne peut pas lire des fichiers .csv de plus d'un million de lignes,

Je parlais d'Excel pas d'Access pour 1 millions de lignes.
Les limites d'accès sont différentes

Le problème c'est que ce fil tient du débat sur le sexe des anges !
On ne connait toujours ni le contexte, ni l'objectif opérationnels...

 

[Airone784]

Bonjour @job75,

Sub Importer()
Dim t#, chemin$, fichier$, dercol$, formule$, n&
t = Timer
chemin = ThisWorkbook.Path & "\" 'à adapter
fichier = "Source.xlsx" 'à adapter
dercol = "H" 'dernière colonne à copier
formule = "'" & chemin & "[" & fichier & "]Feuil1'!"
On Error Resume Next
n = ExecuteExcel4Macro("MATCH(""zzz""," & formule & "C4)") 'dernière cellule texte en colonne D
On Error GoTo 0
'---restitution---
Application.ScreenUpdating = False
With Feuil1 'CodeName de la feuille de destination
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    With .Range("A1:" & dercol & 1).Resize(n)
        .FormulaArray = "=" & formule & "A1:" & dercol & n 'formule de liaison matricielle
        .Value = .Value 'supprime la formule
       .Rows(1) .Offset(n).Resize(Rows.Count - n).ClearContents 'RAZ en dessous
        .Sort .Columns(4), Header:=xlYes 'tri pour regrouper et accélérer
        .Columns(4).Replace "4GF", "#N/A"
        On Error Resume Next
        .Columns(4).Offset(1).SpecialCells(xlCellTypeConstants, 3).EntireRow.Delete 'textes et nombres
        .Columns(4).Replace "#N/A", "4GF"
        .Replace 0, "", xlWhole 'efface les valeurs zéro
    End With
    With .UsedRange: End With 'actualise les barres de défilement
End With
MsgBox "Durée " & Format(Timer - t, "0.00 \sec"), , "Import"
End Sub

Cette méthode fonctionne bien pour transférer des données d'un fichier A vers B rapidement. J'ai testé avec le fichier que Job75 a mis dans le post #6. Ensuite, je me suis permis d'enlever la partie qui filtre les données car dans mon exemple à moi, je ne veux que récupérer des données d'un fichier B vers fichier A sans rien filtrer.
Donc, je vire la fin du code que j'ai réussi à décrypter. Par contre, en adaptant ce code à mon cas à moi comme je n'ai que des données numériques, n=0 à chaque fois et du coup ça ne fonctionne plus.
Comment faire si mes données sont numériques donc ???

Sinon @patricktoulon

Sub testAdO()
    Dim fichier$, nomfeuille$, DispoCel As Range
    fichier = ThisWorkbook.Path & "\Planning_GDS.xlsm"
    nomfeuille = "Liste_ressources"
    Set DispoCel = [M3]
    resADO [A1:D300000], fichier, nomfeuille, DispoCel
End Sub
Function resADO(plage, fichier, nomfeuille, destination)


'Dim Cn As ADODB.Connection, texte_SQL$, Rst As ADODB.Recordset
    Dim Cn As Object, texte_SQL$, Rst As Object
      Set Cn = CreateObject("ADODB.Connection")
     Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"";"
         texte_SQL = "SELECT * FROM [" & nomfeuille & "$" & plage.Address(0, 0) & "]"  'la requête.    ' Attention!!!!!!! à ne pas oublier le symbole "$" après le nom de la feuille.


    
    
    Set Rst = CreateObject("ADODB.RecordSet")
    Set Rst = Cn.Execute(texte_SQL)
    
       '--- Boucle sur les entêtes pour récupérer les noms ---
    For i = 0 To Rst.Fields.Count - 1
    Cells(2, i + 13).Activate
        Cells(2, i + 13) = Rst.Fields(i).Name
    Next i
    
    destination.CopyFromRecordset Rst
    '--- Fermeture connexion ---
    Cn.Close
    Set Cn = Nothing: Set Rst = Nothing
End Function

cette méthode fonctionne bien également sauf quand le fichier origine est ouvert où je rencontre un message d'erreur. C'est normal comme comportement ?

Merci de vos aides respectives.

 

[soan]

Bonjour Airone784, le fil,

bonne année 2021, et meilleurs vœux !

c'est bizarre : on dirait que le demandeur @Kenza18 s'est changé en @Airone784 !

si c'est toi Kenza : j'espère que c'est pas car je t'ai confondu avec Kenza Braiga
que tu as changé de pseudo ! j'espère aussi que tu as lu mon post #34 et aussi
mon post #35 ; si oui, j'espère que toutes mes infos t'ont été utiles.

soan

 

[Airone784]

Bonjour Airone784, le fil,

bonne année 2021, et meilleurs vœux !

c'est bizarre : on dirait que le demandeur @Kenza18 s'est changé en @Airone784 !

si c'est toi Kenza : j'espère que c'est pas car je t'ai confondu avec Kenza Braiga
que tu as changé de pseudo ! j'espère aussi que tu as lu mon post #34 et aussi
mon post #35 ; si oui, j'espère que toutes mes infos t'ont été utiles.

soan

Bonjour et meilleurs voeux aussi.
Désolé mais je ne comprends pas ta réponse. Je suis bien dans un fichier excel xlsm vers un autre excel xlsm. Je ne souhaite pas utiliser access.
Moi j'ai une base de 15000 lignes grand max que je veux importer d'un excel à un autre mais je bloque avec les codes proposés... D'où ma question !

 

[eriiic]

Bonjour à tous,

Job, un .csv est un fichier texte, on peut le traiter directement de la même manière si j'ai bon souvenir.
eric

 

[soan]

@Kenza18, @Airone784, le fil,

 

[soan]

@Kenza18, @Airone784, le fil,

soan

 

[soan]

@Kenza18, @Airone784, le fil,

maint'nant, si tu préfères utiliser Excel pour lire
ton fichier .csv d'environ 15 000 lignes, c'est
ton choix, bien sûr !

soan