sql server - Inserting data into multiple CSV files, only the last file actually contains the inserted data - like the first fil

admin管理员组

文章数量:1123788

I am currently using the below function to gather two sets of information and store them into a CSV file, with a blank link between the two sets as a deliniation. I have included the entire function instead of just a snippet for context, basically:

1. The data is retrieved from a database and stored in a DataTable - this works.
2. The DataTable content (3 cols) should be inserted into specific columns of the CSV - this works.
3. If there are more rows in the DataTable than there are in the template CSV (this is dynamic), copy the template a second time and insert the overflow into that CSV, rinse and repeat to create as many CSVs are required. This doesn't work properly.

For some reason, where there is overflow, the second/third/fourth CSV is created, but only the last file contains any values. So for eg, if there are 4 exported files, file 4 has values, but the first to third are blank, same as the template. I'm at a loss as to why, hoping for some guidance here. Thank you.

Public Function Build_Results_Merged()
    ' Get a current timestamp for export file naming
    Dim exportTimeStamp As String = expTS
    Dim baseFilename As String = $"I:\SCINOMIX\MASTERLIST TO RUN\{txtTest.Text} -- {exportTimeStamp}.csv"
    Dim outputFilename As String = baseFilename
    Dim fileCount As Integer = 1

    ' Load CMM and CDSA results and convert DataTable to List
    Dim cmmTable As DataTable = GetResults_CMM()
    Dim cmmResults As New List(Of (Lab_ID As String, Name As String, EtrDate As String))
    For Each row As DataRow In cmmTable.Rows
        cmmResults.Add((row("request_id").ToString(), row("Patient_Name").ToString(), row("EtrDate").ToString()))
    Next

    Dim cdsaTable As DataTable = GetResults_CDSA()
    Dim cdsaResults As New List(Of (Lab_ID As String, Name As String, EtrDate As String))
    For Each row As DataRow In cdsaTable.Rows
        cdsaResults.Add((row("request_id").ToString(), row("Patient_Name").ToString(), row("EtrDate").ToString()))
    Next

    ' Combine results: CMM first, then blank row, then CDSA
    Dim allResults As New List(Of (request_id As String, Patient_Name As String, EtrDate As String))
    allResults.AddRange(cmmResults)
    allResults.Add(("", "", "")) ' Blank row
    allResults.AddRange(cdsaResults)

    ' Find template file
    Dim di = New DirectoryInfo("I:\SCINOMIX\Templates")
    Dim filename = di.EnumerateFileSystemInfos("*", System.IO.SearchOption.AllDirectories).
    Where(Function(i) i.Name.IndexOf(txtTest.Text, StringComparison.InvariantCultureIgnoreCase) >= 0).
    Select(Function(i) i.FullName).FirstOrDefault()
    Dim templatePath As String = filename

    ' Copy template file
    My.Computer.FileSystem.CopyFile(templatePath, outputFilename)

    ' Read and update the CSV dynamically
    Dim inputFile As String = outputFilename
    Dim rows As New List(Of String())
    Dim headers As String()

    Using parser As New TextFieldParser(inputFile)
        parser.TextFieldType = FieldType.Delimited
        parser.SetDelimiters(",")
        headers = parser.ReadFields()

        ' Read all rows
        While Not parser.EndOfData
            rows.Add(parser.ReadFields())
        End While
    End Using

    ' ✅ DEBUGGING OUTPUT
    Console.WriteLine("Total rows available in template: " & rows.Count)
    Console.WriteLine("Total results to insert: " & allResults.Count)

    ' Insert data dynamically
    Dim resultIndex As Integer = 0
    Dim rowIndex As Integer = 0

    While resultIndex < allResults.Count
        ' ✅ Before writing, check if the file has enough rows left
        If rowIndex >= rows.Count Then
            Console.WriteLine("No more space in the current file, creating a new file.")

            If resultIndex < allResults.Count Then
                fileCount += 1
                outputFilename = $"I:\SCINOMIX\MASTERLIST TO RUN\{txtTest.Text} {fileCount}nd Run -- {exportTimeStamp}.csv"
                My.Computer.FileSystem.CopyFile(templatePath, outputFilename)

                ' Reload template into rows for the new file
                Dim newRows As New List(Of String())
                Using parser As New TextFieldParser(outputFilename)
                    parser.TextFieldType = FieldType.Delimited
                    parser.SetDelimiters(",")
                    headers = parser.ReadFields()
                    While Not parser.EndOfData
                        newRows.Add(parser.ReadFields())
                    End While
                End Using

                ' Assign newRows instead of clearing rows
                rows = newRows
                rowIndex = 0
            End If
        End If

        ' ✅ Continue inserting all remaining data into the new file, not just one row
        While rowIndex < rows.Count AndAlso resultIndex < allResults.Count
            If rows(rowIndex).Length > 9 Then
                Console.WriteLine($"Writing row {rowIndex} in file {outputFilename}: {allResults(resultIndex).request_id}, {allResults(resultIndex).Patient_Name}, {allResults(resultIndex).EtrDate}")
                rows(rowIndex)(7) = allResults(resultIndex).request_id
                rows(rowIndex)(8) = allResults(resultIndex).Patient_Name
                rows(rowIndex)(9) = allResults(resultIndex).EtrDate
            End If

            resultIndex += 1
            rowIndex += 1
        End While
    End While

    While resultIndex < allResults.Count
        ' ✅ Before writing, check if the file has enough rows left
        If rowIndex >= rows.Count Then
            ' ✅ Ensure the current file is written before switching to a new one
            Console.WriteLine($"Finalizing file: {outputFilename} with {rowIndex} rows.")

            Using writer As New StreamWriter(outputFilename)
                writer.WriteLine(String.Join(",", headers))
                For Each row In rows
                    writer.WriteLine(String.Join(",", row))
                Next
            End Using

            Console.WriteLine($"File {outputFilename} written successfully.")

            ' ✅ Now create the new file
            fileCount += 1
            outputFilename = $"I:\SCINOMIX\MASTERLIST TO RUN\{txtTest.Text} {fileCount}nd Run -- {exportTimeStamp}.csv"
            My.Computer.FileSystem.CopyFile(templatePath, outputFilename)

            ' Reload template into rows for the new file
            Dim newRows As New List(Of String())
            Using parser As New TextFieldParser(outputFilename)
                parser.TextFieldType = FieldType.Delimited
                parser.SetDelimiters(",")
                headers = parser.ReadFields()
                While Not parser.EndOfData
                    newRows.Add(parser.ReadFields())
                End While
            End Using

            ' Assign newRows instead of clearing rows
            rows = newRows
            rowIndex = 0
        End If

        ' ✅ Continue inserting all remaining data into the current file, filling every row before switching
        If rowIndex < rows.Count AndAlso resultIndex < allResults.Count Then
            If rows(rowIndex).Length > 9 Then
                Console.WriteLine($"Writing row {rowIndex} in file {outputFilename}: {allResults(resultIndex).request_id}, {allResults(resultIndex).Patient_Name}, {allResults(resultIndex).EtrDate}")
                rows(rowIndex)(7) = allResults(resultIndex).request_id
                rows(rowIndex)(8) = allResults(resultIndex).Patient_Name
                rows(rowIndex)(9) = allResults(resultIndex).EtrDate
            End If

            resultIndex += 1
            rowIndex += 1
        End If
    End While

    ' ✅ Ensure the last file is written after exiting the loop
    Console.WriteLine($"Finalizing last file: {outputFilename} with {rowIndex} rows.")
    Using writer As New StreamWriter(outputFilename)
        writer.WriteLine(String.Join(",", headers))
        For Each row In rows
            writer.WriteLine(String.Join(",", row))
        Next
    End Using
    Console.WriteLine($"File {outputFilename} written successfully.")


    ' ✅ Ensure the last file is written after exiting the loop
    Console.WriteLine($"Finalizing last file: {outputFilename} with {rowIndex} rows.")
    Using writer As New StreamWriter(outputFilename)
        writer.WriteLine(String.Join(",", headers))
        For Each row In rows
            writer.WriteLine(String.Join(",", row))
        Next
    End Using
    Console.WriteLine($"File {outputFilename} written successfully.")


End Function

本文标签：