Base données et Excel

[JJ]

Bonsoir à tous,
j'ai une grosse base de données txt avec un séparateur (ex: que je ne peux pas importer dans Excel car > 65000 lignes.

J'ai donc la possibilité de l'importer dans Acces pour la travailler.

Existe t il un moyen de l'importer dans Excel par une macro?
En effet, si ma base de données a 8 enregistrements séparés par un ; (par ex) et que le 2ème champ soit 01, soit 02 soit 03 soit 04, serait il possible d'ecrire une macro qui ,une fois un classeur excel vierge ouvert, ouvre la base de données et copie chaque ligne d'enregistrement dans chaque feuille du classeur en fonction de la valeur du champ en 2ème position ' 01' ou '02' ou '03 'ou' 04' ...(01 en feuil1, 02 en feuil2 et etc, ce qui me donnerait 65000*4 lignes au total)

merci pour votre aide et bonne semaine à tous
JJ

 

[MichelXld]

bonjour

j'espere que cet exemple pourra t'aider

Sub importFichierTexte()
Dim Wb As Workbook
Dim Ligne As String
Dim x As Integer, numFeuille As Integer
Dim Tableau() As String
Dim i As Long

Set Wb = Workbooks.Add 'creation nouveau classeur

If Wb.Sheets.Count < 4 Then
Do
Wb.Sheets.Add After:=Worksheets(Worksheets.Count)
Loop Until Wb.Sheets.Count = 4
End If

Open 'C:\\\\\\\\fichierTest.txt' For Input As #1
Do While Not EOF(1)
Line Input #1, Ligne
i = i + 1
Tableau = Split(Ligne, ';') 'le separateur est le point virgule
numFeuille = Tableau(1)
i = Wb.Sheets(numFeuille).Range('A65536').End(xlUp).Row + 1
For x = 0 To UBound(Tableau)
Wb.Sheets(numFeuille).Cells(i, x + 1) = Tableau(x)
Next
Loop
Close #1

End Sub



bonne journée
MichelXld

 

[JJ]

Bonjour Michel, as tu une idée du temps d'exécution d'une telle macro, en supposant que j'ai 50000lignes en 01, et 10000 sur les 3 autres feuilles, ce doit etre tres long?
je vais ecrire la macro et tester
Je te remercie
Bonne journée
jj

 

[MichelXld]

rebonjour

je n'ai aucune idée du temps de calcul : j'ai fait un test seulement sur quelques lignes


pour un gain de temps tu pourrais utiliser la methode ADO
dans cet exemple chaque colonne du fichier texte doit contenir un entete : champ1 , champ2 , Champ3....

la macro filtre les lignes du fichier fermé en recuperant les enregistrements qui contiennent 01 dans le 'champ2' , puis 02 ...etc , pour importer les infos dans chaque feuille du nouveau classeur
(testé avec Excel2002 )


Sub importFichierTexte_ADO_filtreChamp()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim Rc As Object
Dim Cn As String, Chemin As String, Fichier As String
Dim i As Long

Set Wb = Workbooks.Add 'creation nouveau classeur
If Wb.Sheets.Count < 4 Then
Do
Wb.Sheets.Add After:=Worksheets(Worksheets.Count)
Loop Until Wb.Sheets.Count = 4
End If

Chemin = 'C:\\\\\\\\'
Fichier = 'fichierTest.txt'

Cn = 'Driver={Microsoft Text Driver (*.txt; *.csv)};' & _
'Dbq=' & Chemin & ';Extensions=asc,csv,tab,txt'

For Each Ws In Wb.Sheets
Set Rc = New ADODB.Recordset
Rc.Open 'SELECT * FROM ' & Fichier & ' WHERE champ2 = ' & Ws.Index, Cn, _
adOpenForwardOnly, adLockReadOnly, adCmdText

If Not Rc.EOF Then
Ws.Range('A1').CopyFromRecordset Rc
End If

Rc.Close
Set Rc = Nothing
Next
End Sub


bonne journée
MichelXld

Message édité par: michelxld, à: 12/12/2005 10:37

 

[JJ]

Rebonjour,
je viens de faire un essai moi aussi sur queleques lignes, la macro est parfaite.

J'ai bien mes resultats dans un nouveau classeur par feuille 01,02...

Toutefois si je veux apporter une modification:
- que le champ '01' (ou 02,03,04 ) ne soit pas en 2éme position dans la ligne, par ex:
28;toto;mars;01;jean
ici 01 est en 4 ème position (et non en 2ème position comme je l'avais ecrit) , ou dois je modifier dans ta macro?

- si je veux rajouter des feuilles de classeur (par ex jusqu a 06?) je modifie juste: if wb.sheets.count =6 ?

car alors je peux faire une feuille 05 avec la condition '01' en 4ème position par ex et 'jean' en 5ème position et une nouvelle feuille 06 avec '02' et 'jean'? Est ce possible?
merci pour ton aide
PS: je m'en tiens a la 1ere macro qui marche super
(pour info je te donnerai le temps d'execution quand j'aurais fini)
Bonne journée
JJ

 

[MichelXld]

bonjour

si les valeurs 01 , 02 , 03 .... sont dans la 4eme colonne , tu peux adapter la macro ainsi

numFeuille = Tableau(3)


pour t'assurer qu'il y a 6 feuilles dans le nouveau classeur

If Wb.Sheets.Count < 6 Then
Do
Wb.Sheets.Add After:=Worksheets(Worksheets.Count)
Loop Until Wb.Sheets.Count = 6
End If


par contre je n'ai pas compris cette phrase

car alors je peux faire une feuille 05 avec la condition '01' en 4ème position par ex et 'jean' en 5ème position et une nouvelle feuille 06 avec '02' et 'jean'? Est ce possible?

bonne journée
MichelXld

 

[JJ]

merci pour ta réponse
je voulais juste savoir si on pouvait créér une feuille 5 par ex en faisant non plus 1 condition (01 ou 02...) mais 2 pour 'recouper' le tableau des 01 par ex:

par ex:
Feuil1 excel= ligne avec 01 en col 4
Feuil2..........................02 idem
Feuil3..........................03 idem
Feuil4..........................04 idem
Feuil5 avec 01 en col 4 et jean (par ex) en col5 dans le fichier txt
Feuil6 avec 01 en col 4 et pierre en col5
ou et comment se fairait ce test?

Question subsidiaire a ajouter a ta macro:

- en fin de macro, comment sauvegarder le classeur automatiquement dans le répertoire avec un nom par ex 'resultat.xls (au cas ou je le ferais tourner de nuit et alors une coupure EDF......!!!)
-comment inserer dans la macro du Classeur que la colonne s'ajuste a la taille des enregistrements car le tableau excel resultat est tres large pour des colonnes qui pourraient etre moins large (par ex col 01)
Merci beaucoup
Bon apres midi
JJ

 

[JJ]

Rebonjour Michel,

Je viens de m'apercevoir en ouvrant le bloc-notes que le fichier texte ext inexploitable car sous borme de tableau sur 2 lignes (et pas avec

Je l'ai importé dans une table Access de 80000 lignes et 13 colonnes

Cette table est trop grande quand je fais exporter dans Excel (>65000lignes)

J'ai donc 2 possibilités:

-soit je travaille sur Access (mais je connais pas bien) et j'exporte des fichiers travaillés plus petits dans Excel

- soit , et c'est ma question, pour utiliser ta macro, Access sait-il exporter la table dans un format txt , avec un séparateur ;
car apres je me servirai de ta macro ?

Bonne soirée et encore merci pour ton aide
JJ

 

[JJ]

Bonsoir Michel,

Cet AM j'ai testé ta 1ere macro.

Ma base access tres importante a été convertie a partir d'Access en fichier.txt avec separateur ; pour utiliser ta macro .OK

Je lance la macro Excel et erreur d'incompatibilité : je m'apercois alors que le 01 ou 02 ou 03.. est en format texte et non numerique (que je vois dans le fichier texte converti d'access par ;'01'; et non ;01; )
Je supprime ll fichier.txt pour recommencer

Je reviens sur Access pour passer le champ en numerique pour reconvertir en txt, il ne peut pas le passer en numerique (pas assez de memoire)

Peut on modifier simplement ta macro pour que le champ 01 texte soit considéré dans ta macro comme 01 numerique, ou ce n'est pas possible?,

merci pour ton aide et bonne soirée
JJ

 

[MichelXld]

bonsoir

je ne suis pas sur d'avoir bien compris mais tu peux essayer de remplacer cette ligne

numFeuille = Tableau(1)

par

numFeuille = Val(Right(Tableau(1), Len(Tableau(1)) - 1))



bonne soiree
MichelXld

 

[JJ]

Merci Michel, je vais tester ta solution demain matin.

Si j'ai mon champ 01 ou 02..en col4 , je dois écrire:
numFeuille=Val(Right(Tableau(3),Len(Tableau(3))-1)) j'ai juste?

Pour juste te donner la raison de cette modif:

Dans la base Access, si je fais modifier : je me suis apercu que ce champ 01 ou 02..est en texte et non numerique, donc quand je convertis la table Access en fichier texte j'obtiens:
;'01'; au lieu de ;01; (qui fonctionnait)

donc j'ai supposé que l'incompatibilité de type venait de là.

Je te dirai le temps de création du classeur si ta methode fonctionne, en tout cas marci pour ton aide
Bonne soirée
JJ

 

[JJ]

Bonsoir Michel, ta formule est super, ca marche !
Bonne soirée et merci
JJ