Create multiple Export Files from dataset

[bbrendan]

Hi all,

Just in need of some help in creating a variable number of export files from a database.
The reason is i have a limit of the number of rows that each file can contain. in this case 3000 rows per file. I can create the file ok, but im having trouble dropping 3000 records into each file...

Any help would be appreciated

        Const maxRows = 3000                ' ive set the contant for the rows for each file
        Dim da As SqlDataAdapter

        strSQL = "select * from xxx"
        da = New SqlDataAdapter(strSQL, strConn)

        Dim ds As DataSet = New DataSet()
        Dim dt As DataTable
        Dim dr As DataRow
        Dim path As String
        Dim delim As String
        Dim intLineNo As Integer    'current row
        Dim intTotalFiles As Integer
        Dim intCurrentFile As Integer
        Dim intRowCount As Integer

        Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)

        da.Fill(ds, 0)
        dt = ds.Tables(0)

        Dim intTotalRows As Integer = dt.Rows.Count     'total rows
        intTotalFiles = (intTotalRows / maxRows) + 1       ' this works out the total number of files. by dividing the total rows in the table / max rows
        intCurrentFile = 1

        intRowCount = (intTotalRows / intTotalFiles)

        Do Until intCurrentFile = intTotalFiles                   'loop through to create each file

            strPath = "j:\"
            path = strPath & "test" & intCurrentFile & ".txt"

            Dim textout As New StreamWriter(New FileStream(path, FileMode.Create, FileAccess.Write))

            ' Write out the header row
            delim = ""

            For Each col As DataColumn In dt.Columns
                textout.Write(delim)
                textout.Write(col.ColumnName)
                delim = ","
            Next
            textout.WriteLine()

            For Each dr In dt.Rows                   ' here is where i get stuck. I need to start from for exmaple row 1 and goto row 3000. Then start from row 3001 to 6000
                'delim = ""     'ive take out this line to cut

                For Each value As Object In dr.ItemArray
                    textout.Write(delim)

                    'If TypeOf value Is String Then
                    textout.Write(""""c) ' thats four double quotes and a c
                    textout.Write(value)

                    textout.Write(""""c) ' thats four double quotes and a c
                    'Else
                    'textout.Write(value)
                    'End If
                    delim = ","

                Next
                textout.WriteLine()
                intLineNo = intLineNo + 1
            Next
            intRowCount = intRowCount + 1
            textout.Close()
        Loop

        intCurrentFile = intCurrentFile + 1

 

[ThatRickGuy]

This should spit out XML files containing the data you passed in with the number of rows you specified, and the Filename with a # stuck in before the .

So "c:\temp\MyFile.XML" would spit out "C:\Temp\MyFile0.XML"

Private sub SplitDataSet(ExportDataTable as datatable, MaxRows as integer, FileName as string)
  
  dim iteration as integer = 0
  dim AtEndOfData as boolean = false

  while not AtEndOfData
    dim dsExport as new dataset
    dim dt as new datatable
    dsexport.tables.add(dt)

    dim UpperLimit as integer 
    UpperLimit = ExportDataTable.rows.count - (MaxRows * Iteration) - 1 
    if UpperLimit > MaxRows then 
       UpperLimit = MaxRows
    else
       AtEndOfData = True
    end if

    for i as integer = 0 to UpperLimit
      dt.rows.importrow(ExportDataTable.rows(i))
    next i

    dsExport.writeXML(filename.replace(".",Iteration & ".")
    iteration += 1
  end while
end sub

Completely untested, so it will likely need some finessing.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.