XL 2016 VBA - Synthétiser fichier import au format *.txt

max.lander · 27 Décembre 2020

Bonjour à tous,

J'essaye sans succès de retravailler un fichier d'import au format texte.

Le fichier texte joint a titre d'exemple est éclatée en plusieurs lignes de même nature
je souhaiterai pouvoir out synthétiser en 4 lignes en effectuer la sommes des montants
pour chaque ligne de même nature.

Le fichier final doit respecter les espaces de base pour ne pas compromettre l'importation.

Si quelqu'un à une idée, je suis preneur.

Merci,

fanfan38 · 27 Décembre 2020

Bonjour
Sélectionner le document complet
Edition copier
ouvrir un nouveau document excel
copier les données du document texte
Choisir Données / convertir
choisir largeur fixe / suivant
créer un séparateur à 20 et supprimer celui à 44
suivant / terminer

A+ François

max.lander · 27 Décembre 2020

Merci fanfan, mais je voulais automatiser le process en VBA

patricktoulon · 28 Décembre 2020

bonjour

max.lander à dit:
Merci fanfan, mais je voulais automatiser le process en VBA

et bien fait ce que te dit @fanfan38 en enregistrant la macro
on t'aidera a nettoyer le code ensuite

max.lander · 2 Janvier 2021

Bonsoir à tous,

@fanfan38, @patricktoulon merci pour vos interventions.

Je viens d'enregistrer la macro de conversion et obtiens le fichier joint.

VB:

Sub Transformation()


    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(24, 1), Array(38, 1), Array(83, 1), Array(84, 1), _
        Array(104, 1), Array(141, 1)), TrailingMinusNumbers:=True
End Sub

dysorthographie · 4 Janvier 2021

bonjour,
je te propose un solution SQL!

VB:

Sub test()

Const Path As String = "C:\Test", Source As String = "fichier test#txt"
Const Champs As String = "" & vbCrLf & _
"DecimalSymbol=," & vbCrLf & _
"Col1=A Text Width 24" & vbCrLf & _
"Col2=B Text Width 14" & vbCrLf & _
"Col3=C Text Width 45" & vbCrLf & _
"Col4=D Text Width 13" & vbCrLf & _
"Col5=E Double Width 8" & vbCrLf & _
"Col6=F TEXT Width 34 " & vbCrLf & _
"Col7=G TEXT Width 4" & vbCrLf

ShemaIni Path, Source, "FixedLength", Champs, False

Dim Cn As Object
Set Cn = CreateObject("ADODB.Connection")
  Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Path & "' ;Extended Properties=""text;HDR=no;FMT=Delimited(,)"""

Sheets("Feuil2").Range("A1").CopyFromRecordset Cn.Execute("select A,B,C,D,sum(E),F,G from [" & Source & "] group by  A,B,C,D,F,G ")

   Cn.Close
  MsgBox "Fin"
End Sub


Public Sub ShemaIni(Rep As String, fichier As String, Delimited As String, Optional Champs As String = "", Optional NewCsv As Boolean = False)
Dim txt As String
txt = "[" & Replace(fichier, "#", ".") & "]" & vbCrLf & "Format=" & Delimited
If Champs <> "" Then txt = txt & vbCrLf & Champs
Dim fso, NewFichier
Set fso = CreateObject("Scripting.FileSystemObject")
Set NewFichier = fso.OpenTextFile(Rep & "\schema.ini", 2, True)
NewFichier.Write txt
NewFichier.Close
If NewCsv = True Then
    Set NewFichier = fso.OpenTextFile(Rep & "\" & Replace(fichier, "#", "."), 2, True)
    NewFichier.Write ""
    NewFichier.Close
End If
Set NewFichier = Nothing
Set fso = Nothing
End Sub

max.lander · 5 Janvier 2021

Bonsoir à tous,

@dysorthographie merci beaucoup pour ta propopsition c'est puissant

Par contre, pour le fichier test joint je devrais arriver au résulat ci-dessous, les sommes totaux sont différents

100310120OD6413310 LIGNE GROUPE GLOBALE 1 C 31826,06N EUR
100310120OD6413310 A05920 SOUS LIGNE GROUPE GLOBAL 1 D 27825,88N EUR
100310120OD6413310 A08020 SOUS LIGNE GROUPE GLOBAL 1 D 2083,50N EUR
100310120OD6413310 A01020 SOUS LIGNE GROUPE GLOBAL 1 D 2022,00N EUR

dysorthographie · 5 Janvier 2021

bonsoir,
j'ai corrigé le problème!

VB:

Sub test()

Const Path As String = "C:\Test", Source As String = "fichier test#txt"
Const Champs As String = "ColNameHeader=False" & vbCrLf & _
"DecimalSymbol=," & vbCrLf & _
"Col1=A Text Width 24" & vbCrLf & _
"Col2=B Text Width 14" & vbCrLf & _
"Col3=C Text Width 45" & vbCrLf & _
"Col4=D Text Width 13" & vbCrLf & _
"Col5=E Double Width 8" & vbCrLf & _
"Col6=F TEXT Width 34 " & vbCrLf & _
"Col7=G TEXT Width 4" & vbCrLf

ShemaIni Path, Source, "FixedLength", Champs, False
Dim Cn As Object
 Set Cn = CreateObject("ADODB.Connection")
  Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Path & "' ;Extended Properties=""text;HDR=No;"""
 Sheets("Feuil2").Range("A1").CopyFromRecordset Cn.Execute("select A,B,C,D,sum(E),F,G from [" & Source & "] group by  A,B,C,D,F,G ")
  Cn.Close
  MsgBox "Fin"
End Sub
Public Sub ShemaIni(Rep As String, fichier As String, Delimited As String, Optional Champs As String = "", Optional NewCsv As Boolean = False)
Dim txt As String
txt = "[" & Replace(fichier, "#", ".") & "]" & vbCrLf & "Format=" & Delimited
If Champs <> "" Then txt = txt & vbCrLf & Champs
Dim fso, NewFichier
Set fso = CreateObject("Scripting.FileSystemObject")
Set NewFichier = fso.OpenTextFile(Rep & "\schema.ini", 2, True)
NewFichier.Write txt
NewFichier.Close
If NewCsv = True Then
    Set NewFichier = fso.OpenTextFile(Rep & "\" & Replace(fichier, "#", "."), 2, True)
    NewFichier.Write ""
    NewFichier.Close
End If
Set NewFichier = Nothing
Set fso = Nothing
End Sub

max.lander · 6 Janvier 2021

@dysorthographie merci beaucoup c'est que je cherchais à faire.

On en apprend tous les jours je pensais qu'il était impossible d'utiliser du SQL sans passer par une base de données physique.

Pour stp m'aider dans l'ingestion du code en m'éclairer sur le sens des lignes ci-dessous :

VB:

;Extended Properties=""text;HDR=No;"""

VB:

"Col1=A Text Width 24" & vbCrLf & _

Code:

Public Sub ShemaIni(Rep As String, fichier As String, Delimited As String, Optional Champs As String = "", Optional NewCsv As Boolean = False)
Dim txt As String
txt = "[" & Replace(fichier, "#", ".") & "]" & vbCrLf & "Format=" & Delimited
If Champs <> "" Then txt = txt & vbCrLf & Champs
Dim fso, NewFichier
Set fso = CreateObject("Scripting.FileSystemObject")
Set NewFichier = fso.OpenTextFile(Rep & "\schema.ini", 2, True)
NewFichier.Write txt
NewFichier.Close
If NewCsv = True Then
    Set NewFichier = fso.OpenTextFile(Rep & "\" & Replace(fichier, "#", "."), 2, True)
    NewFichier.Write ""
    NewFichier.Close
End If
Set NewFichier = Nothing
Set fso = Nothing
End Sub

dysorthographie · 6 Janvier 2021

bonjour max.lander,

comme tu l'as maintenant compris il est possible d'utiliser SQL pour manipuler des fichiers Text, CSV etc.

pour ce faire on considère le répertoire qui contient les fichier comme un base de données!

VB:

 Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Path & "' ;Extended Properties=""text;HDR=No;"""

Extended Properties=""text; il s'agit de fichier texte!

HDR=Yes/No signifie que la première ligne du fichier texte contient/ou pas le nom des champs dans le fichier que tu as fourni c'est non!

vue que le repertoire est une base de données tous les fichier.txt sont des table!

afin de pouvoir explorer notre TXT, il nous faut analyser sa structure et en plus exploitable par ADODB?
pour ce faire il est possible d'utiliser un fichier d'initialisation!

dans mon repertoire C:\Test on trouve le fichier schema.ini dont voici le contenu!

Code:

[fichier test.txt]          'nom du fichier concerné
Format=FixedLength           'indique que les champs on une longueur fixe
ColNameHeader=False       'il n'y a pas d'entête de colonnes   dans le fichier
DecimalSymbol=,               'que le caractère decimal est la virgule
Col1=A Text Width 24        'défini le nom le type et le nombre de caractère  de la Colonne  ColX
Col2=B Text Width 14
Col3=C Text Width 45
Col4=D Text Width 13
Col5=E Double Width 8     'on défini que E est un double et c'est mieux pour faire Sum(E)
Col6=F TEXT Width 34
Col7=G TEXT Width 4

sub ShemaIni permet de créer le fichier schema.ini et accessoirement de créer un fichier vierge
en ca d'export de données d'excel au fichier TXT

Code:

If NewCsv = True Then
    Set NewFichier = fso.OpenTextFile(Rep & "\" & Replace(fichier, "#", "."), 2, True)
    NewFichier.Write ""
    NewFichier.Close
End If

Code:

"select A,B,C,D,sum(E),F,G from [" & Source & "] group by  A,B,C,D,F,G "

cette requête sql sélectionne les champs défini dans schema.ini et qui n'existe pas dans le fichier texte! et fait la somme du champ E "Sum(E)"

tu noteras également que le point dans une requête sql est le séparateur entre la table et le champ Table.champ c'est pourquoi je remplace le . par # dans le nom de la table!

Code:

 Source As String = "fichier test#txt"

max.lander · 6 Janvier 2021

@dysorthographie merci beaucoup d'avoir pris le temps de commenter ton code.
Je vais maintenant m'atteler à le comprendre en profondeur.

Je reviens très certainement avec de nouvelles questions

XL 2016 VBA - Synthétiser fichier import au format *.txt

max.lander

XLDnaute Occasionnel

Pièces jointes

fanfan38

XLDnaute Barbatruc

max.lander

XLDnaute Occasionnel

patricktoulon

XLDnaute Barbatruc

max.lander

XLDnaute Occasionnel

Pièces jointes

dysorthographie

XLDnaute Accro

max.lander

XLDnaute Occasionnel

dysorthographie

XLDnaute Accro

max.lander

XLDnaute Occasionnel

dysorthographie

XLDnaute Accro

max.lander

XLDnaute Occasionnel

Discussions similaires