{"id":307,"date":"2025-10-24T22:30:49","date_gmt":"2025-10-24T20:30:49","guid":{"rendered":"https:\/\/www.silverice.it\/IpercubeTechWP1\/?page_id=307"},"modified":"2025-10-25T10:11:38","modified_gmt":"2025-10-25T08:11:38","slug":"convertcsvstring2datatable","status":"publish","type":"page","link":"https:\/\/www.silverice.it\/IpercubeTechWP1\/ipercube-utilities\/data-table-functions\/convertcsvstring2datatable\/","title":{"rendered":"Convert CSV String to DataTable"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Parses a CSV-formatted string and converts it into a <em>DataTable<\/em>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This function uses TextFieldParser for robust parsing, which handles quoted fields and delimiters properly.<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:75%\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column page-snippet is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:100%\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-Geist-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-Geist-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">VB<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>    Public Function ConvertCsvString2DataTable(CsvString As String,\n                                           Optional RecordSeparator As String = vbCrLf,\n                                           Optional FieldSeparator As String = \";\") As Data.DataTable\n        Dim dtTable As New Data.DataTable\n        Dim bFirstRecord As Boolean = True\n\n        Try\n            Using strReader As New IO.StringReader(CsvString)\n                Using textparser As New TextFieldParser(strReader)\n                    textparser.Delimiters = {FieldSeparator}\n                    While Not textparser.EndOfData\n                        Dim curRow = textparser.ReadFields()\n\n                        ' Set column headers from the first record\n                        If bFirstRecord Then\n                            For i As Integer = LBound(curRow) To UBound(curRow)\n                                dtTable.Columns.Add(curRow(i))\n                            Next\n                            bFirstRecord = False\n                        Else\n                            ' Add each subsequent row\n                            Dim drRow As Data.DataRow = dtTable.NewRow()\n                            For j = LBound(curRow) To UBound(curRow)\n                                drRow(j) = curRow(j)\n                            Next\n                            dtTable.Rows.Add(drRow)\n                        End If\n\n                    End While\n                End Using\n            End Using\n\n        Catch ex As Exception\n            Dim szFunctionName As String = New Diagnostics.StackTrace().GetFrame(0).GetMethod().Name\n            Throw New ArgumentException(szModuleName + \".\" + szFunctionName + vbTab + ex.Message)\n        End Try\n\n        'Try to convert Boolean and date\n        ConvertBooleanColumns(dtTable)      '\"True\"\/\"False\" or \"0\"\/\"1\"\n        ConvertDateColumns(dtTable)         '\"yyyy-MM-dd\" or \"yyyyMMdd\"\n\n        Return dtTable\n    End Function<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">Public Function <\/span><span style=\"color: #DCDCAA\">ConvertCsvString2DataTable<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">CsvString<\/span><span style=\"color: #D4D4D4\"> As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                           Optional RecordSeparator As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> =<\/span><span style=\"color: #9CDCFE\"> vbCrLf<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                           Optional FieldSeparator As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #CE9178\">&quot;;&quot;<\/span><span style=\"color: #D4D4D4\">) As <\/span><span style=\"color: #4EC9B0\">Data<\/span><span style=\"color: #D4D4D4\">.DataTable<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> dtTable <\/span><span style=\"color: #D4D4D4\">As<\/span><span style=\"color: #569CD6\"> New <\/span><span style=\"color: #D4D4D4\">Data.DataTable<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> bFirstRecord <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">Boolean<\/span><span style=\"color: #D4D4D4\"> =<\/span><span style=\"color: #569CD6\"> True<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Try<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            Using strReader As<\/span><span style=\"color: #569CD6\"> New <\/span><span style=\"color: #D4D4D4\">IO.<\/span><span style=\"color: #DCDCAA\">StringReader<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">CsvString<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                Using textparser As<\/span><span style=\"color: #569CD6\"> New <\/span><span style=\"color: #DCDCAA\">TextFieldParser<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">strReader<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    textparser.<\/span><span style=\"color: #9CDCFE\">Delimiters<\/span><span style=\"color: #D4D4D4\"> = {FieldSeparator}<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    <\/span><span style=\"color: #C586C0\">While<\/span><span style=\"color: #D4D4D4\"> Not textparser.EndOfData<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> curRow <\/span><span style=\"color: #D4D4D4\">= textparser.<\/span><span style=\"color: #DCDCAA\">ReadFields<\/span><span style=\"color: #D4D4D4\">()<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        <\/span><span style=\"color: #6A9955\">&#39; Set column headers from the first record<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> bFirstRecord <\/span><span style=\"color: #C586C0\">Then<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                            <\/span><span style=\"color: #C586C0\">For<\/span><span style=\"color: #D4D4D4\"> i As <\/span><span style=\"color: #4EC9B0\">Integer<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #DCDCAA\">LBound<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">curRow<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #C586C0\">To<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">UBound<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">curRow<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                dtTable.Columns.<\/span><span style=\"color: #DCDCAA\">Add<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #DCDCAA\">curRow<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">i<\/span><span style=\"color: #D4D4D4\">))<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                            <\/span><span style=\"color: #C586C0\">Next<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                            <\/span><span style=\"color: #9CDCFE\">bFirstRecord<\/span><span style=\"color: #D4D4D4\"> =<\/span><span style=\"color: #569CD6\"> False<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        <\/span><span style=\"color: #C586C0\">Else<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                            <\/span><span style=\"color: #6A9955\">&#39; Add each subsequent row<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                            <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> drRow <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">Data<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #9CDCFE\">DataRow<\/span><span style=\"color: #D4D4D4\"> = dtTable.<\/span><span style=\"color: #DCDCAA\">NewRow<\/span><span style=\"color: #D4D4D4\">()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                            <\/span><span style=\"color: #C586C0\">For<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">j<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #DCDCAA\">LBound<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">curRow<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #C586C0\">To<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">UBound<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">curRow<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                <\/span><span style=\"color: #DCDCAA\">drRow<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">j<\/span><span style=\"color: #D4D4D4\">) = <\/span><span style=\"color: #DCDCAA\">curRow<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">j<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                            <\/span><span style=\"color: #C586C0\">Next<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                            dtTable.Rows.<\/span><span style=\"color: #DCDCAA\">Add<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">drRow<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        <\/span><span style=\"color: #C586C0\">End If<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">While<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Using<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Using<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Catch ex As <\/span><span style=\"color: #4EC9B0\">Exception<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> szFunctionName <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> =<\/span><span style=\"color: #569CD6\"> New <\/span><span style=\"color: #D4D4D4\">Diagnostics.<\/span><span style=\"color: #DCDCAA\">StackTrace<\/span><span style=\"color: #D4D4D4\">().<\/span><span style=\"color: #DCDCAA\">GetFrame<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">).<\/span><span style=\"color: #DCDCAA\">GetMethod<\/span><span style=\"color: #D4D4D4\">().Name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            Throw<\/span><span style=\"color: #569CD6\"> New <\/span><span style=\"color: #DCDCAA\">ArgumentException<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">szModuleName<\/span><span style=\"color: #D4D4D4\"> + <\/span><span style=\"color: #CE9178\">&quot;.&quot;<\/span><span style=\"color: #D4D4D4\"> +<\/span><span style=\"color: #9CDCFE\"> szFunctionName<\/span><span style=\"color: #D4D4D4\"> +<\/span><span style=\"color: #9CDCFE\"> vbTab<\/span><span style=\"color: #D4D4D4\"> + ex.<\/span><span style=\"color: #9CDCFE\">Message<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Try<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #6A9955\">&#39;Try to convert Boolean and date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #DCDCAA\">ConvertBooleanColumns<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">dtTable<\/span><span style=\"color: #D4D4D4\">)      <\/span><span style=\"color: #6A9955\">&#39;&quot;True&quot;\/&quot;False&quot; or &quot;0&quot;\/&quot;1&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #DCDCAA\">ConvertDateColumns<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">dtTable<\/span><span style=\"color: #D4D4D4\">)         <\/span><span style=\"color: #6A9955\">&#39;&quot;yyyy-MM-dd&quot; or &quot;yyyyMMdd&quot;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #D4D4D4\"> dtTable<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">End Function<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Parses a CSV-formatted string and converts it into a DataTable. This function uses TextFieldParser for robust parsing, which handles quoted fields and delimiters properly.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":282,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-307","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/307","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/comments?post=307"}],"version-history":[{"count":4,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/307\/revisions"}],"predecessor-version":[{"id":366,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/307\/revisions\/366"}],"up":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/282"}],"wp:attachment":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/media?parent=307"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}