Lire un fichier CSV de 170Mo avec Access

Lire un fichier CSV de 170Mo avec Access - VB/VBA/VBS - Programmation

Marsh Posté le 06-07-2009 à 10:37:57    

Bonjour à tous!!
 
J'ai un petit soucis, j'ai besoin de récupérer des données de plusieurs fichier CSV dans ma base de données access. Le truc c'est que ces fichiers sont très lours, don 1 qui fais exctement 173Mo (je sais mm pas il comporte de ligne, mais en tout cas Excel ne peut pas l'ouvrir!)
 Donc le problème c'est que quand je lance mon appli pour lire le fichier elle plante! (enfin jje la lance le matin à 10h, à 17h ça n'a toujours pas finis de tourner, donc je suppose qu'elle à planter, non??
Donc je me demandais si c'étais possible de découper le fichiercsv, ou de lme lire en partie et de reprendre la lecture une heure plustard, enfin de faire en sorte que ça ne plante pas!
voici mon code (mon code fonctionne sur des fichier de 5Mo, mais je pense qu'il est pas assez performant pour  175Mo) :
 
--------------------------------------------------------------------------------
 

Code :
  1. Set myFSO = CreateObject("Scripting.FileSystemObject" )
  2.     RecupNomFich TabTypeFichiers(2), TabTypeInterv(5)
  3.     Set csvFile = myFSO.OpenTextFile(cheminCSV & fichierCSV)  'c'est des variables globales
  4.    
  5.     'tant qu'on est pas à la fin du fichier CSV (boucle sur chaque ligne)
  6.     While Not csvFile.AtEndOfStream
  7.          'lire la ligne suivante
  8.         csvLine = csvFile.ReadLine
  9.            '"spliter" la ligne
  10.            tabStr = Split(csvLine, csvDelimiter)
  11.               'actions sur le fichier
  12.     Wend
  13.     csvFile.Close
  14.     Set csvFile = Nothing: Set myFSO = Nothing


 
--------------------------------------------------------------------------------
Merci beaucoup à tous!!

Reply

Marsh Posté le 06-07-2009 à 10:37:57   

Reply

Marsh Posté le 06-07-2009 à 10:50:44    

Pour savoir, s'il y a un plantage ou juste un traitement très long, il faudrait avoir des traces.
 
De toutes façons, le VBA est très lent, et de plus, la méthode employée pour lire ce fichier n'est peut-être pas la plus performante. Peut-être que le code old-school suivant serait plus performant :

Open cheminCSV & fichierCSV For Input As #1
Do While Not EOF(1)
   Line Input #1, csvLine
   '"spliter" la ligne
   tabStr = Split(csvLine, csvDelimiter)
   'actions sur le fichier
Loop
Close #1

Reply

Marsh Posté le 06-07-2009 à 10:54:37    

Tu peux normalement mettre le débuggeur en pause avec CTRL+PAUSE, tu cliques sur Débugger et en regardant le contenu des variables tu peux savoir où il en est. Et tu peux le remettre en marche après.

Reply

Marsh Posté le 06-07-2009 à 11:30:52    

Merci olivthill, je vais tester ça tout de suite!!
Donc apparement le fait que mon fichier soit gros ne devrais pas poser de problème???
Je testerai toutes les heures le debugger pour voir si tout roule!
merci à vous 2 pour vos réponses!!

Reply

Marsh Posté le 06-07-2009 à 13:15:58    

Pour infos, En VBA l'écriture d'un fichier de 176MO prend 9 secondes, et sa lecture avec le split prend 25 secondes.

Option Compare Database
Public cheminCSV, fichierCSV, csvDelimiter
Public mlngStart, EndTimer As Long
Private Declare Function GetTickCount Lib "kernel32" () As Long
 
Sub write_bigfile()
  Dim i As Long
  cheminCSV = "C:\toto\"
  fichierCSV = "f.csv"
  mlngStart = GetTickCount
  Open cheminCSV & fichierCSV For Output As #1
  For i = 1 To 3300000
     Print #1, CStr(i) & ";12345679;abcdefghijklmnopqrstuvwxyz;987653210"
  Next
  Close #1
  EndTimer = (GetTickCount - mlngStart)
  MsgBox ("That took: " & EndTimer & " milliseconds" )
  'That took 9 391 milliseconds, un peu plus de 9 secondes pour 176 MO
End Sub
 
Sub read_bigfile2()
  Dim csvLine
  Dim tabStr
  Dim i As Long
  cheminCSV = "C:\toto\"
  fichierCSV = "f.csv"
  csvDelimiter = ";"
  mlngStart = GetTickCount
  Open cheminCSV & fichierCSV For Input As #1
  i = 0
  Do While Not EOF(1)
     Line Input #1, csvLine
     '"spliter" la ligne
     tabStr = Split(csvLine, csvDelimiter)
     'actions sur le fichier
     i = i + 1
     'If (i < 5) Then
     '  MsgBox ("Debug " & CStr(i) & ":" & csvLine)
     ' Else
     '  Exit Do
     'End If
  Loop
  Close #1
  EndTimer = (GetTickCount - mlngStart)
  MsgBox ("That took: " & EndTimer & " milliseconds" )
  'That took 25 843 milliseconds
End Sub

Je n'ai pa pu tester avec le code indiqué car "Set myFSO = CreateObject("Scripting.FileSystemObject" ), c'est du VBS et non du VBA Access, ou bien peut-être du VBA d'une nouvelle version ou avec une configuration particulière.

Reply

Marsh Posté le 06-07-2009 à 13:26:53    

J'ai aussi testé en VBS, et cela prend 49 secondes pour mon fichier de 176MO. Donc le problème ne doit pas venir de la lecture.

Start = Now  
cheminCSV = "C:\toto\"
fichierCSV = "f.csv"
 Set myFSO = CreateObject("Scripting.FileSystemObject" )
'    RecupNomFich TabTypeFichiers(2), TabTypeInterv(5)
 
Set csvFile = myFSO.OpenTextFile(cheminCSV & fichierCSV)  'c'est des variables globales
   
  'tant qu'on est pas à la fin du fichier CSV (boucle sur chaque ligne)
  While Not csvFile.AtEndOfStream
     'lire la ligne suivante
      csvLine = csvFile.ReadLine
      '"spliter" la ligne
      tabStr = Split(csvLine, csvDelimiter)
      'actions sur le fichier
  Wend
csvFile.Close
 
Set csvFile = Nothing: Set myFSO = Nothing
Wscript.Echo DateDiff("s", Start, Now)

Reply

Marsh Posté le 06-07-2009 à 14:27:30    

Merci pour tes réponses!!!
Bah je code en VBA donc je suis plus interressée par le code en VBA. Bah mon big fichier de 176Mo contient une liste d'intervenants avec des informations. je lis chaque ligne, et sur chaque ligne je récupère des données, je teste si ces données, sont déjà présente dans une de mes table (donc une requete sql à éxécuté) si c'sst pas le ca jinsere la ligne (2e requete sql) sinon j'insere la ligne en incrémentant le nombre (intervenant info2, intervenant info3 etc...) donc j'execute au moins de requete sql par ligne. a ton avis ça vient de la?
car quand mm plus de 6h d'execution c'est beaucoup!
je vais essayer de tester avec ton code.

Reply

Marsh Posté le 06-07-2009 à 20:24:35    

Pour ma part, j'importerais toutes les données d'un seul coup dans une table temporaire, et ensuite je ferais les traitements sql nécessaires. Suivant la façon dont tu lances ta requête et l'emplacement de ta base, ça peut être effectivement très long d'exécuter ligne après ligne une requête.

Reply

Marsh Posté le 07-07-2009 à 11:47:13    

exactement ce que j'allais dire : un import est quand même assez classique dans ce cas (mais pas systématique)

Reply

Marsh Posté le 09-07-2009 à 17:48:29    

bonjour à tous!!!
 
bon suite à mon problème de temps avec mon fameux fichier csv de 170mo, j'ai eu (la bonne) idée d'exporter directement mon fichier dans une autre base de données access, il semblerai que les accès à une autre base de données access soit plus rapide que els accès à un fichier csv (je me trompe,)
en plus ce qui est pratique, c'est que je peux chercher plus précisément ce que je veux, pour insérer les informations dans ma 1e base.
 
donc petit soucis, la table de ma 2e page (celle qui contient le fichier de 170mo) fais 1 800 000 lignes comme le fichier, et en fait pour récupérer els infos de cette base dans ma base 1, je fais une requete où je demande de sélectionné certaine colonne de cette selon certain critère, et afin de récupérer els données ligne par ligne, j'ai mis un compteur (c'est un integer) appellé ligne.
 
Le soucis c'est que a environ 35000 lignes (le compteur = 35000) le compteur déclenche une erreur de type 6 "Dépassement de capacité"!
Comment y remédier, une idée? merci beaucoup!!!
 
j'utilise mon compteur comme ceci :
 

Code :
  1. ligne =ligne +1ce compteur est dans une boucle.


 
et c'est sur cette ligne que access se stoppe.

Reply

Marsh Posté le 09-07-2009 à 17:48:29   

Reply

Marsh Posté le 09-07-2009 à 18:22:17    

Citation :

Il semblerait que les accès à une autre base de données access soit plus rapide que les accès à un fichier csv

Non. Une base de données est forcément constituée d'un ou de plusieurs fichiers. Donc, ça ne peut pas être plus rapide (il pourrait juste y avoir une prélecture, mais cela ne ferait que déplacer le problème). Mais de toutes manières, mes tests ont montré que la lecture du fichier entier prend moins d'une minute sur mon vieux PC. Donc le problème du traiement qui dure plusieurs heures n'a aucun rapport avec la lecture du fichier. Le problème ce situe dans ce qui se trouve derrière les "actions" pour chaque ligne lue.

Reply

Marsh Posté le 10-07-2009 à 09:57:32    

bonjour olivthill!
bah je pense que c'est les nombreuses requetes et test qui font font ralentir le traitement. j'ai chronométré hier, ça met 1minutes et 16secondes pour traiter 1 000 lignes. vu que j'en ai 1 800 000 j'ai fais le calsul j'en ai pour 25 ou 30heures je sais plus. donc galere, donc du coup avec ma base de données, je faisdes requetes spécifique comme ça je récupère 100 000 lignes par 100 000 lignes.
 
Enfin si je n'arrive pas à optimiser le temps c'est pas grave, mais parcontre je ne comprend pas l'erreur 6, dépacement de capacité.  
j'ai regardé sur internet le plus grand interger c'est genre 2 milliard et des poussieres, donc j'en suis loin avec mes 35 000.
 
un bout de code peut peut-être te donner une idée?:
 

Code :
  1. Public Function ExecuteReqZG(sql As String)
  2.     Dim access As ADODB.Connection
  3.     Dim Myrecord As ADODB.Recordset
  4.     Dim result As String
  5.     Dim lngNbFields As Integer, i As Integer, ligne As Integer
  6.    
  7.     Set access = New ADODB.Connection
  8.     access.Provider = "Microsoft.Jet.Oledb.4.0"
  9.     access.ConnectionString = "C:\Documents and Settings\gf3323\Bureau\ExtractionZG.mdb"
  10.     access.Open
  11.     Set Myrecord = New ADODB.Recordset
  12.     Myrecord.Open sql, access, adOpenDynamic, adLockOptimistic
  13.    
  14.     result = ""
  15.     ligne = 1
  16.     lngNbFields = Myrecord.Fields.Count - 1
  17.     ligne = ligne + 1
  18.     'écriture du recordset dans la feuille
  19.     Do While Not Myrecord.EOF
  20.         For i = 0 To lngNbFields
  21.             result = result & Myrecord(i).Value
  22.             If i < lngNbFields Then result = result & ";"
  23.             DoEvents
  24.         Next
  25.        
  26.         TableDetailGeo result
  27.         result = ""
  28.         ligne = ligne + 1   'c'est cette ligne qui plante
  29.         Myrecord.MoveNext
  30.     Loop
  31.    
  32.     access.Close
  33. End Function

Reply

Marsh Posté le 10-07-2009 à 09:57:53    

J'oubliai!! merci beaucoup!!

Reply

Marsh Posté le 10-07-2009 à 10:15:39    

Ligne as Long fonctionnera bcp mieux
Integer = 32767 max


Message édité par 4get9 le 10-07-2009 à 10:16:21
Reply

Marsh Posté le 10-07-2009 à 10:21:43    

bonjour 4get9!
 
merci pour l'info je test ça tout de suite!!!
je ne savais pas que c'etait limité à 32000 les integers.

Reply

Marsh Posté le 10-07-2009 à 10:27:46    

si tu veux tu peux aussi faire une table liée (dans ta base 1 tu créé une table liée à la table de la base 2)
Ainsi tu pourra travailler comme si c'était une table locale.
 
Sinon : tu dois utiliser toutes les lignes ? ou seulement une partie ?

Reply

Marsh Posté le 10-07-2009 à 11:40:04    

re!
bonne idée de liée les tables, je n'y avais pas pensé....
bah à la base je dois utilisé toutes les lignes, mais c'est carrément trop long, donc jme suis dis autant profiter des requêtes pour faire par paquets de lignes.
 
donc à ton avis si je lie toutes les tables de ma 2e base access, àma 1e, les traitement seront plus rapide?
 
merci!

Reply

Marsh Posté le 10-07-2009 à 13:39:23    

J'pense que la différence sera sensible question perfs
par contre question code, ça sera bcp plus simple.
 
Sinon question : que fait ton code au juste ?

Reply

Marsh Posté le 10-07-2009 à 13:51:46    

en gros je récupère chaque ligne de mes fichiers csv, et je les inseres dans ma base de données. en fait elle contient les informations sur tous les intervenants de mon entreprise, donc c'est un gros truc. chaque intervenant à plusieurs informations éparpillées dans plusieurs fichiers.
donc quand je lis une ligne d'un fichier je dois retrouvé cet intervenant dans la base afin d'insérer la ligne correctement dans la table appropriée. je récupère une information typique à l'intervenant qui me sert de clé primaire en fait.
 
donc ce qui fais que dès que je lis une ligne je fais à peu près 3 requêtes, car je dois repérer dans quelle table je dois inséré l'information, si c'est pas un doublons.... donc du coup ça ralenti enormément.
 
pour lié les tables j'utilise simplement l'assistant access? et une fois liée je les traite comme si elle était en locale?
merci.

Reply

Marsh Posté le 10-07-2009 à 14:46:12    

Hou la... ça me semble bien complexe ton traitement... !
 
Pourquoi as tu besoin de dispatcher tes informations dans plusieurs tables ? Sont-elles hétérogènes au point de devoir créer des tables différentes ?
 
Tu dis que tu dois faire une requête pour savoir à quel intervenant appartient l'information : N'y a t-il pas un moyen de le repérer grace au fichier ? Par exemple son nom de fichier ou son emplacement ?
 
Telle que je vois le truc, je le redis, importe toutes tes infos dans une seule table, quitte ensuite à utiliser les assistants d'access pour la fractionner en plusieurs pour traiter les doublons.
 
Ceci dit, au départ, tu parle d'un fichier de plusieurs milliers de lignes, maintenant, c'est de plusieurs fichiers dont il s'agit.

Reply

Marsh Posté le 10-07-2009 à 16:22:48    

mmmxtina a écrit :

...pour lié les tables j'utilise simplement l'assistant access? et une fois liée je les traite comme si elle était en locale?...


oui

Reply

Marsh Posté le 10-07-2009 à 16:24:31    

sinon tu pourrais nous donner un extrait de ton fichier ?
 
ATTENTION à bien retirer tout ce qui concerne la confidentialité (remplace nom/matricule/adresse/code de carte bancaire/... par des trucs bidon)

Reply

Marsh Posté le 10-07-2009 à 17:31:22    

mmmxtina a écrit :


donc quand je lis une ligne d'un fichier je dois retrouvé cet intervenant dans la base afin d'insérer la ligne correctement dans la table appropriée. je récupère une information typique à l'intervenant qui me sert de clé primaire en fait.

 

donc ce qui fais que dès que je lis une ligne je fais à peu près 3 requêtes, car je dois repérer dans quelle table je dois inséré l'information, si c'est pas un doublons.... donc du coup ça ralenti enormément.

 

J'espère pour toi que tu n'a pas créé une table par intervenant, chacune de ces tables ayant les même champs !

 

Ca ne m'étonne pas que ça rame : à chaque ligne, tu relances le moteur de la base, ce n'est pas très efficace.


Message édité par Kapote Nestor le 10-07-2009 à 17:31:59
Reply

Marsh Posté le 15-07-2009 à 15:42:44    

Bonjour à tous! et merci de toutes vos réponses!
 
Kapote Nestor:

Citation :

J'espère pour toi que tu n'a pas créé une table par intervenant, chacune de ces tables ayant les même champs !


 
Non je n'ai pas mis un intervenant par table, j'aurai eu plus de 20000 tables!!!!! hi!
j'ai une table intervenant qui contient la totalité des informations générales de l'intervenant (nom, prénom, adresse ....)
 
Kapote Nestor:

Citation :

Ceci dit, au départ, tu parle d'un fichier de plusieurs milliers de lignes, maintenant, c'est de plusieurs fichiers dont il s'agit.


 
En fait j'ai une trentaine de fichier csv à inséré dans lma base de donnée, mais seul 1 fichier pèse 170Mo et seulement lui me posait problème s'est pourquoi, je n'ai parlé que de celui là au départ.
 
Je vais essayé d'être plus rpécise sur ce que je fais:
Il y a 20 000 intervenants que je dois inséré dans ma base de données.
C'est intervenants sont découpé par type (10 au total), chaque type est contenu dans un fichier.  
prenons le 1e fichier, il va contenir les Détails généraux des intervenants de type A.
Il y a 3 sorte de fichier par type, les Détails généraux, les zones géoographiques (où ils interviennent), les détails des zones géographiques (où ils interviennent), tout ça par type, ça fais 3 (types de fichiers) * 10 types d'intervenant = 30 fichiers .csv
 
Ils ont une appellation spéciale, avec dez chiffres, mais j'ai fais une petite appli qui s'est les reconnaitre.
 
donc pour avoir l'intégralité de ces données, j'ai crée plusieurs table intervenants, convention... zone_Géo_Matrque1, zone_géo_Marque2, zone_géo_Marque3 etc. (j'ai du inséré les zones géographique par marque car sinon il y aurait eu trop de ligne dans une table)
 
C'est pourquoi je fais autant de test avant de lire une ligne, car un intervenant peut avoir plusieurs conventions, donc si il a 2 conventions, on observe 2 lignes distinctes dans le fichiers .csv, il faut que je fasse des test pour insérer ses informations par la suite dans ma base de données.
 
voilà.
 
4get9 :

Citation :

sinon tu pourrais nous donner un extrait de ton fichier ?


 
je ne préfère pas mais si ce que je viens d'expliqué plus haut, est encore trop embrouillé je vous transmettrai un exemple de fichier dans ce cas.
 
Donc pour en revenir à la discussion, je pense que les tests que je fais sont nécessaires, et sont la première cause de ralentissement, après vraiment, je ne sais pas comment faire mieux.
 
si vous avez des idées?
merci beaucoup!

Reply

Marsh Posté le    

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed