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 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191
|
Private Sub ImporterTexte(prmChmFic As String, prmNomFic As String, prmNomTable As String)
Debug.Print prmNomFic
On Error GoTo Err_ImporterTexte
Dim nomFic As String: nomFic = prmNomFic
Dim nomTable As String: nomTable = prmNomTable
Dim chmFic As String: chmFic = prmChmFic
Dim numFic As Integer: numFic = FreeFile()
Dim lf As String
Dim i As Integer
Dim db As Database: Set db = CurrentDb
Open chmFic & nomFic For Input As numFic
Line Input #numFic, lf: 'ligne d'entête
Dim nomField As Variant
nomField = Split(lf, ",")
For i = LBound(nomField) To UBound(nomField)
nomField(i) = CalculerNomField(CStr(nomField(i)), nomField)
Next i
'Lit une première fois les données pour déterminer leur longueur
Dim tailleMaxField(255) As Integer
Dim dataField(255) As String
Do While Not EOF(numFic)
Line Input #numFic, lf
Debug.Print lf
Call DecouperEnr(lf, dataField)
For i = LBound(nomField) To UBound(nomField)
tailleMaxField(i) = monMax(tailleMaxField(i), Len(CalculerDataField(CStr(dataField(i)))))
Next i
Loop
Close #numFic
'Creer la table associée
Dim t As TableDef
Dim f As Field
Call db.TableDefs.Delete(nomTable)
Set t = db.CreateTableDef(nomTable)
For i = LBound(nomField) To UBound(nomField)
Set f = New Field
f.Name = nomField(i)
f.Type = dbText
f.Size = IIf(tailleMaxField(i) = 0, 255, tailleMaxField(i))
f.AllowZeroLength = True
Call t.Fields.Append(f)
Next i
Call db.TableDefs.Append(t)
Set t = Nothing
Dim descriptionChamp As String
Set t = db.TableDefs(nomTable)
For Each f In t.Fields
descriptionChamp = RetrouverDescription(t.Name, f.Name)
If IsNull(descriptionChamp) Then
descriptionChamp = "."
Else
If descriptionChamp = "" Then
descriptionChamp = "."
End If
End If
Call f.Properties.Append(CurrentDb.CreateProperty("Description", dbText, descriptionChamp))
Next
Set t = Nothing
'charge les données
Dim r As Recordset
Open chmFic & nomFic For Input As numFic
Line Input #numFic, lf: 'ligne d'entête
Set r = db.OpenRecordset(nomTable)
Do While Not EOF(numFic)
Line Input #numFic, lf
Debug.Print lf
lf = ConvertirAWindows(lf)
Call DecouperEnr(lf, dataField())
r.AddNew
For i = LBound(nomField) To UBound(nomField)
r.Fields(i) = CalculerDataField(CStr(dataField(i)))
Next i
r.Update
Loop
r.Close: Set r = Nothing
Close #numFic
db.Close: Set db = Nothing
Exit_ImporterTexte:
Exit Sub
Err_ImporterTexte:
Select Case Err.Number
Case 3265
'La table n'existe pas
Resume Next
Case Else
MsgBox Err.Number & ", " & Err.description, vbExclamation
Resume Exit_ImporterTexte
End Select
End Sub
Private Function CalculerNomField(prmNomField As String, prmListeNomField As Variant) As String
Dim result As String
result = Mid(prmNomField, 2, Len(prmNomField) - 2): 'Supprime les guillemets autours du nom
Dim c As String
Dim i As Integer
For i = 1 To Len(result)
c = Mid(result, i, 1)
If Not (("A" <= UCase(c) And UCase(c) <= "Z") Or ("0" <= c And c <= "9") Or (c = "_")) Then
c = "_"
Mid(result, i, 1) = c
End If
Next i
'Verifie qu'on a pas déjà un champ avec le même nom
Dim fieldUnique As Boolean
Do
fieldUnique = True
For i = LBound(prmListeNomField) To UBound(prmListeNomField)
If result = prmListeNomField(i) Then
fieldUnique = False
result = result & "x"
End If
Next i
Loop While Not fieldUnique
CalculerNomField = result
End Function
Private Function CalculerDataField(prmDataField As String) As String
Dim result As String
If Left(prmDataField, 1) = """" Then
result = Mid(prmDataField, 2, Len(prmDataField) - 2): 'Supprime les guillemets autours de la chaine
Else
result = prmDataField
End If
result = Trim(result)
CalculerDataField = result
End Function
Private Function monMax(prmInt1 As Integer, prmInt2 As Integer)
If prmInt1 > prmInt2 Then
monMax = prmInt1
Else
monMax = prmInt2
End If
End Function
Private Function ConvertirAWindows(prmTexte As String)
Dim i As Integer
Dim c As String
Dim result As String
For i = 1 To Len(prmTexte)
c = Mid(prmTexte, i, 1)
result = result & dosAWindows(c)
Next i
ConvertirAWindows = result
End Function
Private Function dosAWindows(prmCharDos As String) As String
Dim result As String
Select Case prmCharDos
Case Chr(144)
result = "É"
Case Chr(130)
result = "é"
Case Chr(135)
result = "ç"
Case Chr(131)
result = "â"
Case Else
result = prmCharDos
End Select
dosAWindows = result
End Function |
Partager