Exporter une table au format csv en prenant en charge les caractères de délimitations.

**BasicZX81** · 29/09/2013, 13h17

Bonjour a tous,
j'ai un problème avec le format CSV. En effet les données que j'exporte peuvent contenir n'importe quel type de caractères comme les points virgules ou les guillemets, or le format csv utilise les points-virgules comme séparateur de données. Pour régler ce 1er problème j'ai délimité mes données par des guillemets. Maintenant les points virgules sont bien reconnus comme faisant partie de mes données mais les guillemets sont maintenant interprétés comme séparateur. J'aimerais que ma procédure fonctionne pour les 2 cas et a priori c'est faisable puisque j'ai essayé de crée le fichier a partir d'excel et celui-ci est crée correctement (même si les données contiennent ces fameux caractères).
Si quelqu'un possède une routine qui fonctionne dans tous les cas comme le fait excel ça intéresserait beaucoup.

En vous remerciant pour votre aide.

Voici le code que j'utilise :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Imports System.IO
Module modExportCSV
    Dim BaseColonnesName As New List(Of String)
    Public Sub ExportCSV(ByVal TableName As String)
        Dim SaveFileDialog As New SaveFileDialog
        Dim FichierName = ""
 
        With SaveFileDialog
            'On spécifie l'extension de fichiers visibles
            .Filter = "All Files (*.*)|*.*|Fichiers csv (*.csv) | *.csv"
            .FilterIndex = 2
            'On affiche et teste le retour du dialogue
            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                'On récupère le nom du fichier
                FichierName = .FileName
            Else : Exit Sub
            End If
        End With
 
        If FichierName.EndsWith(".csv") Then
        Else
            FichierName = FichierName & ".csv"
        End If
 
 
        Try
            Dim sw As StreamWriter = New StreamWriter(FichierName, False, System.Text.Encoding.Default)
 
            Dim Separ As Char() = {CChar(";")}
 
            'Remplissage des en-têtes de colonnes
            Dim dt As DataTable = objDBDataSet.Tables(TableName)
            Dim HeadersText As String = Nothing
            Dim col As DataColumn
            For Each col In dt.Columns
                HeadersText &= """" & col.ColumnName & """;"
            Next
            sw.WriteLine(HeadersText)
 
            'Remplissage des cellules a partir des données
            Dim RowCount As Integer
            Dim row As DataRow
            Dim Item As Object
            For Each row In dt.Rows
                Dim RowText As String = Nothing
                For Each Item In row.ItemArray
                    Dim _Type As Type = Item.GetType
                    Dim _TypeCode As TypeCode = Type.GetTypeCode(_Type)
                    Select Case _TypeCode
                        Case TypeCode.DateTime
                            ' Si c'est un champ date on met en forme la date :
                            Dim _Date As DateTime = CDate(Item)
                            RowText &= """" & _Date.ToString("dd/MM/yyyy HH:mm:ss") & """;"
                        Case Else
                            RowText &= """" & Item.ToString & """;"
                    End Select
                Next
                ' On supprime le dernier caractère de séparation avant d’écrire la ligne :
                RowText = RowText.TrimEnd(Separ)
                sw.WriteLine(RowText)
                RowCount += 1
            Next
 
            sw.Close()
            sw.Dispose()
            MessageBox.Show(String.Format("{0} ligne(s) exportée(s) avec succés", RowCount), "Données exportées", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Une erreur est survenue", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
 
    End Sub
End Module

**dkmix** · 30/09/2013, 11h15

Bonjour,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
 
 
  Public Class CSVTool
 
        Private Const SEPARATOR_CHAR As Char = ";"c
 
        ''' <summary>
        ''' Reads CSV data into the Datatable using the specified file name.
        ''' </summary>
        ''' <param name="table">The table to read the CSV data into.</param>
        ''' <param name="fileName">The name of the file from which to read the data.</param>
        ''' <param name="hasColumnHeader">Whether the CSV document has a header row.</param>
        ''' <param name="separator">Separator caractere </param>
        ''' <param name="DeleteLastChar">Suppression du dernier caractère si les lignes se terminent pas le caractère de séparation</param>
        Public Sub ReadCsv(table As DataTable, fileName As String, hasColumnHeader As Boolean, encoding As Encoding, Optional separator As Char = SEPARATOR_CHAR, Optional DeleteLastChar As Boolean = False)
            Using st As Stream = File.OpenRead(fileName)
                ReadCsv(table, st, hasColumnHeader, encoding, separator, DeleteLastChar)
            End Using
        End Sub
 
        ''' <summary>
        ''' Reads CSV data into the Datatable using the specified <see cref="Stream"/>.
        ''' </summary>
        ''' <param name="table">The table to read the CSV data into.</param>
        ''' <param name="stream">The stream from which to read the data.</param>
        ''' <param name="hasColumnHeader">Whether the CSV document has a header row.</param>
        ''' <param name="separator">Separator caractere </param>
        Public Sub ReadCsv(table As DataTable, stream As Stream, hasColumnHeader As Boolean, encoding As Encoding, Optional separator As Char = SEPARATOR_CHAR, Optional DeleteLastChar As Boolean = False)
            Using TxtReader As TextReader = New StreamReader(stream, encoding)
                ReadCsv(table, TxtReader, hasColumnHeader, separator, DeleteLastChar)
            End Using
        End Sub
 
        ''' <summary>
        ''' Reads CSV data into the Datatable using the specified <see cref="TextReader"/>.
        ''' </summary>
        ''' <param name="table">The table to read the CSV data into.</param>
        ''' <param name="reader">The reader from which to read the data.</param>
        ''' <param name="hasColumnHeader">Whether the CSV document has a header row.</param>
        Public Sub ReadCsv(table As DataTable, reader As TextReader, hasColumnHeader As Boolean, Optional separator As Char = SEPARATOR_CHAR, Optional DeleteLastChar As Boolean = False)
            Dim line As String = reader.ReadLine()
            If String.IsNullOrEmpty(line) Then
                Return
            End If
 
            Dim parts As String() = ReadParts(line, separator, DeleteLastChar)
 
            If hasColumnHeader Then
                For Each part As String In parts
                    table.Columns.Add(part, GetType(String))
                Next
 
                line = reader.ReadLine()
            Else
                For Each part As String In parts
                    table.Columns.Add().DataType = GetType(String)
                Next
            End If
 
            While Not String.IsNullOrEmpty(line)
                parts = ReadParts(line, separator, DeleteLastChar)
 
                Dim row As DataRow = table.NewRow()
                For index As Integer = 0 To parts.Length - 1
                    If parts(index) = separator Then
                        parts(index) = String.Empty
                    Else
                        row(index) = parts(index)
                    End If
                Next
                table.Rows.Add(row)
 
                line = reader.ReadLine()
            End While
        End Sub
 
        ''' <summary>
        ''' Writes the Datatable to a CSV file using the specified file name.
        ''' </summary>
        ''' <param name="table">The table that contains the data to write.</param>
        ''' <param name="fileName">The name of the file where the data should be written.</param>
        ''' <param name="hasColumnHeader">Whether the CSV document should have a header row.</param>
        ''' <param name="separator">Separator caractere </param>
        Public Sub WriteCsv(table As DataTable, fileName As String, hasColumnHeader As Boolean, Optional separator As Char = SEPARATOR_CHAR)
            If File.Exists(fileName) Then File.Delete(fileName)
            Using STWritter As Stream = File.Create(fileName)
                WriteCsv(table, STWritter, hasColumnHeader, separator)
            End Using
        End Sub
 
        ''' <summary>
        ''' Reads CSV data into the Datatable using the specified <see cref="Stream"/>.
        ''' </summary>
        ''' <param name="table">The table that contains the data to write.</param>
        ''' <param name="stream">The stream where the data ahould be written.</param>
        ''' <param name="hasColumnHeader">Whether the CSV document should have a header row.</param>
        ''' <param name="separator">Separator caractere </param>
        Public Sub WriteCsv(table As DataTable, stream As Stream, hasColumnHeader As Boolean, Optional separator As Char = SEPARATOR_CHAR)
            Using TxtWritter = New StreamWriter(stream)
                WriteCsv(table, TxtWritter, hasColumnHeader, separator)
            End Using
        End Sub
 
        ''' <summary>
        ''' Reads CSV data into the Datatable using the specified <see cref="TextReader"/>.
        ''' </summary>
        ''' <param name="table">The table that contains the data to write.</param>
        ''' <param name="writer">The <see cref="TextWriter"/> where the data ahould be written.</param>
        ''' <param name="hasColumnHeader">Whether the CSV document should have a header row.</param>
        Public Sub WriteCsv(table As DataTable, writer As TextWriter, hasColumnHeader As Boolean, Optional separator As Char = SEPARATOR_CHAR)
            'writer.Encoding = System.Text.Encoding.Default
            If hasColumnHeader Then
                For index As Integer = 0 To table.Columns.Count - 1
                    If index > 0 Then
                        writer.Write(separator)
                    End If
 
                    writer.Write(QuoteText(table.Columns(index).ColumnName, separator))
                Next
 
                writer.WriteLine()
            End If
 
            For rowIndex As Integer = 0 To table.Rows.Count - 1
                For colIndex As Integer = 0 To table.Columns.Count - 1
                    If colIndex > 0 Then
                        writer.Write(separator)
                    End If
 
                    writer.Write(QuoteText(Convert.ToString(table.Rows(rowIndex)(colIndex), CultureInfo.InvariantCulture), separator))
                Next
 
                writer.WriteLine()
            Next
        End Sub
 
        ''' <summary>
        ''' Get the differences between this Datatable and the specified Datatable.
        ''' </summary>
        ''' <param name="original">The table that represents the original set of data.</param>
        ''' <param name="table">The table that represents the new set of data.</param>
        ''' <returns>A table that represents the differences between the sets of data.</returns>
        Public Function GetDifferences(original As DataTable, table As DataTable) As DataTable
            If original Is Nothing Then
                Throw New ArgumentNullException("original")
            End If
 
            If table Is Nothing Then
                Throw New ArgumentNullException("table")
            End If
 
            If original.Columns.Count <> table.Columns.Count Then
                Throw New ArgumentOutOfRangeException("table", "Columns do not match.")
            End If
 
            If original.PrimaryKey Is Nothing OrElse original.PrimaryKey.Length <> 1 OrElse table.PrimaryKey Is Nothing OrElse table.PrimaryKey.Length <> 1 Then
                Throw New ArgumentOutOfRangeException("original", "Tables must have single column primary keys.")
            End If
 
            Dim originalView As New DataView(original, Nothing, original.PrimaryKey(0).ColumnName, DataViewRowState.CurrentRows)
            Dim tableView As New DataView(table, Nothing, table.PrimaryKey(0).ColumnName, DataViewRowState.CurrentRows)
            Dim originalIndex As Integer = 0
            Dim tableIndex As Integer = 0
 
            Dim result As DataTable = original.Copy()
 
            While True
                Dim originalRow As DataRow = Nothing
                Dim tableRow As DataRow = Nothing
                If originalIndex < originalView.Count Then
                    originalRow = originalView(originalIndex).Row
                End If
 
                If tableIndex < tableView.Count Then
                    tableRow = tableView(tableIndex).Row
                End If
 
                If originalRow Is Nothing AndAlso tableRow Is Nothing Then
                    ' ran out of rows, done
                    Exit While
                ElseIf originalRow Is Nothing Then
                    ' ran out of original, this row is inserted
                    result.Rows.Add(tableRow.ItemArray)
                    tableIndex += 1
                ElseIf tableRow Is Nothing Then
                    ' ran out of new, this row is deleted
                    result.Rows.Find(originalRow(original.PrimaryKey(0))).Delete()
                    originalIndex += 1
                Else
                    Dim originalKey As IComparable = DirectCast(originalRow(original.PrimaryKey(0)), IComparable)
                    Dim tableKey As IComparable = DirectCast(tableRow(table.PrimaryKey(0)), IComparable)
 
                    Dim compareResult As Integer = originalKey.CompareTo(tableKey)
                    If compareResult < 0 Then
                        ' rows don't match, original is smaller, this row is deleted
                        result.Rows.Find(originalRow(original.PrimaryKey(0))).Delete()
                        originalIndex += 1
                    ElseIf compareResult > 0 Then
                        ' rows don't match, new is smaller, this row is inserted
                        result.Rows.Add(tableRow.ItemArray)
                        tableIndex += 1
                    Else
                        Dim resultRow As DataRow = result.Rows.Find(originalRow(original.PrimaryKey(0)))
                        Dim resultRowDirty As Boolean = False
                        For columnIndex As Integer = 0 To original.Columns.Count - 1
                            If originalRow.IsNull(columnIndex) AndAlso tableRow.IsNull(columnIndex) Then
                                Continue For
                            ElseIf originalRow.IsNull(columnIndex) OrElse tableRow.IsNull(columnIndex) Then
                                resultRow.ItemArray = tableRow.ItemArray
                                resultRowDirty = True
                                Exit For
                            Else
                                Dim originalValue As IComparable = DirectCast(originalRow(columnIndex), IComparable)
                                Dim tableValue As IComparable = DirectCast(tableRow(columnIndex), IComparable)
 
                                If originalValue.CompareTo(tableValue) <> 0 Then
                                    resultRow.ItemArray = tableRow.ItemArray
                                    resultRowDirty = True
                                    Exit For
                                End If
                            End If
                        Next
 
                        If Not resultRowDirty Then
                            result.Rows.Remove(result.Rows.Find(originalRow(original.PrimaryKey(0))))
                        End If
 
                        originalIndex += 1
                        tableIndex += 1
                    End If
                End If
            End While
 
            Return result
        End Function
 
        Private Function ReadParts(line As String, separator As Char, Optional DeleteLastChar As Boolean = False) As String()
 
            'Return line.Split(separator)
 
            ' Suppression du dernier caractère de séparation en cas de csv mal formaté
            If DeleteLastChar = True Then
                If line.EndsWith(separator) Then
                    line = line.Substring(0, line.Length - 1)
                End If
            End If
 
            Dim parts As New List(Of String)()
 
 
            Dim inQuotes As Boolean = False
            Dim partIndex As Integer = 0
            Dim endPartIndex As Integer = 0
            For index As Integer = 0 To line.Length - 1
                If inQuotes Then
                    If line(index) = """"c Then
                        endPartIndex = index - 1
                        inQuotes = False
                    Else
                        endPartIndex = index
                    End If
                Else
                    If line(index) = """"c Then
                        partIndex = index + 1
                        endPartIndex = partIndex
                        inQuotes = True
                    ElseIf line(index) = separator Then
                        'If index > 0 AndAlso line(index - 1) = separator Then
                        '    parts.Add(String.Empty)
                        'Else
                        parts.Add(line.Substring(partIndex, endPartIndex - partIndex + 1))
                        'End If
                        partIndex = index + 1
                        endPartIndex = partIndex
                    Else
                        endPartIndex = index
                    End If
                End If
            Next
            ' If line.LastIndexOf(separator) <> partIndex - 1 Then
            If Not line.Length.Equals(partIndex) Then
                parts.Add(line.Substring(partIndex, endPartIndex - partIndex + 1))
            Else
                parts.Add(String.Empty)
            End If
 
 
            ' Else
            '  parts.Add(String.Empty)
            ' End If
 
            Return parts.ToArray()
        End Function
 
        ''' <summary>
        ''' Called to quote text so the CSV is well formatted.
        ''' </summary>
        ''' <param name="value">The value to quote.</param>
        ''' <returns>The quoted text.</returns>
        Private Function QuoteText(value As Object, separator As Char) As String
            If value Is Nothing OrElse Convert.IsDBNull(value) Then
                Return String.Empty
            End If
 
            Dim strValue As String = value.ToString()
            If strValue.Contains(separator) Then
                Return """" + strValue + """"
            Else
                Return strValue
            End If
        End Function
 
End Class

**chrismonoye** · 30/09/2013, 16h59

Bonjour,
Pour délimiter une chaîne de texte, CSV utilise un identificateur qui est le guillemet ("), si cette chaine contient un caractère délimiteur (";").
Si il y des guillemets dans cette chaine, en plus de délimiter celle-ci, il faut doubler ces guillemets pour qu'ils soient interprétés littéralement en tant que tels.
Soit un truc du genre, en reprenant ton code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
                    Select Case _TypeCode
                        Case TypeCode.DateTime
                            ' Si c'est un champ date on met en forme la date :
                            Dim _Date As DateTime = CDate(Item)
                            RowText &= """" & _Date.ToString("dd/MM/yyyy HH:mm:ss") & """;"
                        Case Else
                            Dim textItem As String = Item.ToString
                            If textItem.Contains(""""c) Then
                                textItem = textItem.Replace(""""c, """""")
                            End If
 
                            RowText &= """" & textItem & """;"
                    End Select

**BasicZX81** · 30/09/2013, 19h23

Merci a vous,
Comme j'ai la simplicité j'ai utilisé la solution de chrismonoye, j'ai également copier la classe de dkmix au cas ou la 1ere solution montrerais des limites mais apriori j'ai essayé de mettre en défaut cette procédure en mettant des guillemets et des points-virgules dans tous les sens et je dois dire que je n'y suis pas arrivé, ça fonctionne donc parfaitement

.
Un grand merci a vous.

Exporter une table au format csv en prenant en charge les caractères de délimitations.

VB.NET

Discussions similaires

Partager

Partager