Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How do I reorder the columns in a data Table

Posted on 2007-03-25
Medium Priority
Last Modified: 2007-12-19
Hello Experts,

I would like to refer to the question which i raised yesterday. As in the previous question, the task I would like to accomplish is to reorder the columns in a data table before exporting to CSV file. All the data rows in the columns of object dtCargoList are already populated with data and the columns names are reformatted as in the "Select Case" statement.  The order in which the columns exported to the CSV file appeared is the not the order desired. For example, I would like the order of Columns in the CSC file as the one below;

Order: [ShipName], [ShipType], [Berth,InDate/Time],[OutDateTime] etc.. as appeared exactly in the Select Case Statement.

The code I used for this is mentioned below for clarity.

Can you pls suggest an efficient way to finish this. Thanks.

 'Public Function to export Find Cargo search results to a csv file
        Public Function ExportFindCargoSearchResults(ByRef sTempFilePath As String, _
                                                     ByRef dtCargoList As DataTable, _
                                                     ByVal sFileName As String) As Data.ResultData

            Dim oResultData As New Data.ResultData
            Dim oFileWriter As StreamWriter


                Dim sheader As String
                Dim sbody As String
                Dim srecord As String
                Dim sformattedColumnName As String
                Dim drRow As DataRow

                'For each Datacolumn in the DataTable
                For Each col As DataColumn In dtCargoList.Columns

                    'Initially set formattted column name to empty string
                    sformattedColumnName = String.Empty

                    'Change the headers to match the screen information

                    Select Case col.ColumnName

                        Case "Ship_name"
                            sformattedColumnName = "Ship Name"
                            'sHeaderColumnShipname = "Ship Name"

                        Case "Ship_type"
                            sformattedColumnName = "Ship Type"
                            'sHeaderColumnShipType = "Ship Type"

                        Case "Berth_abbreviation"
                            sformattedColumnName = "Berth"
                            'sHeaderColumnBerth = "Berth"

                        Case "In_datetime"
                            sformattedColumnName = "In"
                            'sHeaderColumnInDateTime = "In"

                        Case "Out_datetime"
                            sformattedColumnName = "Out"
                            'sHeaderColumnOutDateTime = "Out"

                        Case "Cargo_agent_short_name"
                            sformattedColumnName = "Cargo Agent"
                            'sHeaderColumnCargoAgent = "Cargo Agent"

                        Case "Principle_agent_short_name"
                            sformattedColumnName = "Principal Agent"
                            'sHeaderColumnPrincipalAgent = "Principal Agent"

                        Case "Handling_type_description"
                            sformattedColumnName = "Handling Type"
                            'sHeaderColumnHandlingType = "Handling Type"

                        Case "Storage_id"
                            sformattedColumnName = "Storage ID"
                            'sHeaderColumnStorageID = "Storage ID"

                        Case "Storage_device_type_description"
                            sformattedColumnName = "Storage Device"
                            'sHeaderColumnStorageDevice = "Storage Device"

                        Case "TimeRestrictedCargo"
                            sformattedColumnName = "Time Restricted"
                            'sHeaderColumnTimeRestricted = "Time Restricted"

                        Case "Unno"
                            sformattedColumnName = "UNNo"
                            'sHeaderColumnUNNo = "UNNo"

                        Case "Class"
                            sformattedColumnName = "Class"
                            'sHeaderColumnClass = "Class"

                        Case "Subrisk"
                            sformattedColumnName = "Subrisk"
                            'sHeaderColumnSubrisk = "Subrisk"

                        Case "Pg"
                            sformattedColumnName = "PG"
                            'sHeaderColumnPackagingGroup = "PG"

                        Case "Proper_shipping_name"
                            sformattedColumnName = "PSN"
                            'sHeaderColumnProperShippingName = "PSN"

                        Case "Quantity"
                            sformattedColumnName = "Qty"
                            'sHeaderColumnQuantity = "Qty"

                        Case "Packaging_type_code"
                            sformattedColumnName = "PT"
                            'sHeaderColumnPackagingTypeCode = "PT"
                    End Select

                    'Add the column to the header and include the comma

                    sheader = sheader & Chr(34) & sformattedColumnName & Chr(34) & ","


                sheader = sheader.Substring(0, sheader.Length - 1)
                'For each row in the datatable
                For Each row As DataRow In dtCargoList.Rows
                    'Create an array
                    Dim arr() As Object = row.ItemArray()

                    For i As Integer = 0 To arr.Length - 1
                        If arr(i).ToString().IndexOf(",") > 0 Then
                            srecord = srecord & Chr(34) & _
                            arr(i).ToString() & Chr(34) & ","
                            srecord = srecord & arr(i).ToString() & ","
                        End If
                    sbody = sbody & srecord.Substring(0, srecord.Length - 1) & vbCrLf
                    srecord = ""

                Dim sCSVtext As String = sheader & vbCrLf & sbody

                Dim sFullFilePath = sTempFilePath & sFileName

                'Write CSV text to file
                'Check if message and file path are both provided
                If Not (sCSVtext Is Nothing AndAlso sFullFilePath Is Nothing) Then

                    'check if file exists
                    If File.Exists(sFullFilePath) Then
                        'add file path
                        oFileWriter = File.AppendText(sFullFilePath)
                        'create new file and add file path
                        oFileWriter = File.CreateText(sFullFilePath)
                    End If
                    'Write text to file, flush then close the file
                    sCSVtext = String.Empty

                End If ' customer log file and message is not null

                'Set the return result to success
                oResultData.ReturnResult = Data.ResultData.ReturnResultType.Success

                'Catch the exception and set the return result and errors
            Catch ex As Exception
                oResultData.ReturnResult = Data.ResultData.ReturnResultType.SystemError
                oResultData.ErrorMessage &= "Error ocurred while trying to export Cargo details"
                oResultData.ErrorException = ex

            End Try

            'Return the result data
            Return oResultData

        End Function
Question by:LukeSayaw
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 15

Expert Comment

ID: 18790512
Hi LukeSayaw,
You can use SetOrdinal() method of DataColumn.
It changes the ordinal or position of the DataColumn to the specified ordinal or position. If ordinal is less than 0 or greater than the existing number of columns ? 1 (greater than the ordinal of the last column) then an Invalid ArgumentException is thrown.

DataColumn.SetOrdinal Method

For example:

Author Comment

ID: 18790638
The method "SetOrdinal" is only available in .NET Framework 2.0 or 3. I am using .NET Framewrok 1.1 only. So the method is not applicable unfortunately.

At the moment I am trying another approach: Copying the data to a new DataTable and re-order the column indexes. That might work but not sure.

If u have a better efficient approach to it, pls let me know immediately.
LVL 15

Expert Comment

ID: 18790728
Sorry, you didn't said anything about .net 1.1

You will have to code a method to spin through the data and build a copy with the columns in the desired order.

DataSet dsNew = new DataSet();
DataTable dt = new DataTable("MyTableName");
DataColumn OldCol;

// Swapping columns 0 and 1
OldCol = MyDataSet.Tables[0].Columns[1];
dt.Columns.Add(new DataColumn(OldCol.ColumnName, OldCol.DataType));
OldCol = MyDataSet.Tables[0].Columns[0];
dt.Columns.Add(new DataColumn(OldCol.ColumnName, OldCol.DataType));

LVL 34

Accepted Solution

Sancler earned 1500 total points
ID: 18791502
You don't actually need to re-order columns in a datatable.  You could simply create a "translation array" and use it in building your output string for the CSV file.  On these lines

   Dim translate(dtCargoList.Columns.Count - 1) As Integer
   translate(0) = <existing column number that you want to be first in output>
   translate(1) = <existing column number that you want to be second in output>

Then in this

                    For i As Integer = 0 To arr.Length - 1
                        If arr(i).ToString().IndexOf(",") > 0 Then
                            srecord = srecord & Chr(34) & _
                            arr(i).ToString() & Chr(34) & ","
                            srecord = srecord & arr(i).ToString() & ","
                        End If
                    sbody = sbody & srecord.Substring(0, srecord.Length - 1) & vbCrLf
                    srecord = ""

replace every occurrence of arr(i) with arr(translate(i))


Author Comment

ID: 18791941
I prefer the second approach to the first one. I will use the second approach in the application and hope that it will work. I will let u know if it works tomorrow.

Many Thanks

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question