Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

How do I reorder the columns in a data Table

Avatar of LukeSayaw
LukeSayaw asked on
.NET ProgrammingASP.NETVisual Basic.NET
5 Comments1 Solution2456 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answers