Solved

How do I reorder the columns in a data Table

Posted on 2007-03-25
5
1,863 Views
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

            Try

                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) & ","

                Next

                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) & ","
                        Else
                            srecord = srecord & arr(i).ToString() & ","
                        End If
                    Next
                    sbody = sbody & srecord.Substring(0, srecord.Length - 1) & vbCrLf
                    srecord = ""
                Next

                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)
                    Else
                        'create new file and add file path
                        oFileWriter = File.CreateText(sFullFilePath)
                    End If
                    'Write text to file, flush then close the file
                    oFileWriter.Write(sCSVtext)
                    oFileWriter.Flush()
                    oFileWriter.Close()
                    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
0
Comment
Question by:LukeSayaw
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:igor_alpha
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
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.setordinal.aspx

For example:
datatable.Columns("Col1").SetOrdinal(1)
0
 

Author Comment

by:LukeSayaw
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.
Thanks
0
 
LVL 15

Expert Comment

by:igor_alpha
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));

dsNew.Tables.Add(dt);
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 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>
   'etc

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) & ","
                        Else
                            srecord = srecord & arr(i).ToString() & ","
                        End If
                    Next
                    sbody = sbody & srecord.Substring(0, srecord.Length - 1) & vbCrLf
                    srecord = ""
                Next

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

Roger
0
 

Author Comment

by:LukeSayaw
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

919 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now