如何更快遍历DataTable完成问卷Excel数据格式转换与批量入库

我有一份约315列、4000行的Excel文件，存储了一份300题问卷的作答结果，数据格式如下：

(Headers)   A | B | C | D | E | F       | Q.1 | Q.2 | ... | Q.300 |
(FirstRow)  Info of first participant   | AnswerCode for every Q  | 

其中A-F列为每位受访者的基础信息，Q.1至Q.300列为对应问题的作答编码。我已将该文件读取为一个大DataTable，后续需要将全部4000行数据导入现有数据库表，导入前需先完成格式转换，目标格式如下：

ParticipantCode | QuestionCode | AnswerCode | DateOfRegistration
00001           | 0001         | 1234567    | yyyy-MM-dd HH:mm:ss
...             | ...          | ...        | ...
00001           | 0300         | 1234567    | yyyy-MM-dd HH:mm:ss
00002           | 0001         | 1234567    | yyyy-MM-dd HH:mm:ss
...             | ...          | ...        | ...
04000           | 0300         | 1234567    | yyyy-MM-dd HH:mm:ss

即原ExcelDataTable的每一行需要转换为FinalDataTable中的300行，最终FinalDataTable约有120万行数据，转换完成后我会将其批量插入（bulk insert）数据库。

Private Function MyFunction()
    For Each ExcelRow As DataRow In ExcelDataTable.Rows
        For Each ExcelColumn As DataColumn In ExcelDataTable.Columns
            QuestionCodeFound = False
            ExcelColumnNameRaw = ExcelColumn.ColumnName.ToString.Trim
            If ExcelColumnNameRaw.StartsWith("Q") Then
                ' Correct the headers
                ExcelColumnSplit = ExcelColumnNameRaw.Split("#")
                ExcelColumnName = String.Concat(ExcelColumnSplit(0), ExcelColumnSplit(1))

                SelectedRowFromDT = QuestionCodeAndQuestionIDDataTable.Select("QuestionID = '" + ExcelColumnName + "'")

                ' Search for "_", because some questions are different
                If SelectedRowFromDT.Length > 0 Then
                    QuestionCodeFound = True
                Else
                    Dim ExcelColumnSplitForMult As String()
                    ExcelColumnSplitForMult = ExcelColumnName.Split("_")
                    SelectedRowFromDT = QuestionCodeAndQuestionIDDataTable.Select("QuestionID = '" + ExcelColumnSplitForMult(0).ToString + "'")
                    If SelectedRowFromDT.Length > 0 Then
                        QuestionCodeFound = True
                    End If
                End If
                If QuestionCodeFound Then
                    Dim QuestionCode As String
                    Dim QuestionTypeDataTable As DataTable
                    Dim QuestionType As String

                    ' Get the Question Type from the respective table
                    QuestionType = String.Empty
                    QuestionCode = SelectedRowFromDT(0).Item("QuestionCode").ToString
                    QuestionTypeDataTable = SearchInSql(My.Settings.ConnectionString, SQLString)

                    If QuestionTypeDataTable.Rows.Count > 0 Then
                        QuestionType = QuestionTypeDataTable.Rows(0).Item(0).ToString.Trim
                    End If

                    ' Fix the Date Format 
                    DateRaw = ExcelRow.Item(1).ToString
                    DateSplit = DateRaw.Split("/")
                    If DateSplit(0).Length = 1 Then
                        DateSplit(0) = String.Concat("0", DateSplit(0))
                    End If
                    If DateSplit(1).Length = 1 Then
                        DateSplit(1) = String.Concat("0", DateSplit(1))
                    End If
                    DateText = String.Concat(DateSplit(0), "/", DateSplit(1), "/", DateSplit(2))
                    DateRegistration = DateTime.ParseExact(DateText, "MM/dd/yyyy", CultureInfo.InvariantCulture)
                    DateRegistrationReformed = DateRegistration.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)
                    DateRegFinal = DateTime.ParseExact((DateRegistrationReformed + " " + "10:00:00").ToString, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture)

                    Dim AnswerValue As String
                    Dim AnswerCode As String
                    Dim AnswerCodeDataTable As DataTable
                    Dim QuestionWasAnswer As String

                    Dim AnswerValueRow() As DataRow = ExcelDataTable.Select("ParticipantCode = '" + ExcelRow.Item(2).ToString + "'")

                    AnswerCodeDataTable = New DataTable
                    AnswerValue = ""
                    QuestionWasAnswer = "0"

                    ' Complete "QuestionWasAnswer" field for all questions and retrieve the AnswerCode for the answer given by each participant
                    If AnswerValueRow.Length > 0 And AnswerValueRow(0).Item(ExcelColumnNameRaw).GetType IsNot GetType(DBNull) Then
                        If Not (QuestionType.Equals("02") Or QuestionType.Equals("03")) Then
                            AnswerValue = AnswerValueRow(0).Item(ExcelColumnNameRaw)
                            QuestionWasAnswer = "1"
                        ElseIf QuestionType.Equals("02") Or QuestionType.Equals("03") Then
                            Dim ExcelColumnSplitForMultSecond As String()
                            Dim MultAnswerValue As String

                            ExcelColumnSplitForMultSecond = ExcelColumnName.Split("_")
                            MultAnswerValue = AnswerValueRow(0).Item(ExcelColumnNameRaw).ToString.Trim
                            AnswerValue = ExcelColumnSplitForMultSecond(1).ToString

                            If MultAnswerValue.Equals("1") Then
                                QuestionWasAnswer = "1"
                            ElseIf MultAnswerValue.Equals("2") Then
                                QuestionWasAnswer = "2"
                            End If
                        End If

                        ' Search in the Answers table for the existing AnswerCode
                        SQLString = String.Format("SELECT Answers.AnswerCode
                                                       FROM Answers
                                                       WHERE Answers.QuestionCode = '{0}'
                                                           AND (Answers.AnswerNumber = '{1}' OR Answers.Answer = '{1}')", QuestionCode, AnswerValue)
                        AnswerCodeDataTable = SearchInSql(My.Settings.ConnectionString, SQLString)

                        If AnswerCodeDataTable.Rows.Count > 0 Then
                            AnswerCode = AnswerCodeDataTable.Rows(0).Item(0).ToString
                            FormattedDataTable.Rows.Add(ParticipantAnswerCode, ExcelRow.Item(2), QuestionCode, AnswerCode, QuestionWasAnswer, DateRegFinal)
                            ParticipantAnswerCode = Convert.ToInt32(ParticipantAnswerCode + 1).ToString.PadLeft(ParticipantAnswerCodeFieldLength, "0")
                        Else
                            ' If a given answer does not exist, save it in the respective table and then try again
                            Dim AnswerCodeLength = GetLengthFromSqlDataBase(My.Settings.ConnectionString, "Answers", "AnswerCode")
                            Dim NextAnswerCode = CalculateNextAnswerCode(AnswerCodeLength)
                            Dim NestAnswerNumber = CalculateNextAnswerNumber(QuestionCode)

                            SaveNewAnswer(NextAnswerCode, QuestionCode, NestAnswerNumber, AnswerValue)

                            SQLString = String.Format("SELECT Answers.AnswerCode
                                                       FROM Answers
                                                       WHERE Answers.QuestionCode = '{0}'
                                                           AND Answers.Answer = '{1}'", QuestionCode, AnswerValue)
                            AnswerCodeDataTable = SearchInSql(My.Settings.ConnectionString, SQLString)

                            If AnswerCodeDataTable.Rows.Count > 0 Then
                                AnswerCode = AnswerCodeDataTable.Rows(0).Item(0).ToString
                                FormattedDataTable.Rows.Add(ParticipantAnswerCode, ExcelRow.Item(2), QuestionCode, AnswerCode, QuestionWasAnswer, DateRegFinal)
                                ParticipantAnswerCode = Convert.ToInt32(ParticipantAnswerCode + 1).ToString.PadLeft(ParticipantAnswerCodeFieldLength, "0")
                            End If
                        End If
                    End If
                End If
            End If
        Next
    Next
    Return FormattedDataTable
End Function

转换完成后我会将FinalDataTable批量插入数据库。

当前程序处理ExcelDataTable的每一行需要约40秒才能转换为FinalDataTable的300行，如果处理全部4000行，整个转换过程需要超过40小时，耗时过长，需要找到更快速的实现方案。

内容的提问来源于stack exchange，提问作者M_J