Add sheet to text file

[noobsesher]

Hi I have a program which asks user for some inputs. Builds an array based on the output called fArray. Then writes fArray to a csv file (I need the output to be view-able in excel). The problem I am having is that sometimes fArray will have more elements than lines in an excel spreadsheet. In this case my current code has no problem writing all the values to the csv file but when I open the csv file it only shows 1,048,576 values(row limit in excel).

Is there a way to write say the first million values to pg1 of my csv file then second million to pg 2 and so on?
Or any other suggestions. My data needs to output like...

     var1_value1     var2_value1     ... varn_value1
     var1_value2     var2_value2     ... varn_value2

Because the number of variables(columns in output) is set by user I dont want to move over x columns and start back at the top of current page. But instead start outputting in same format on a second page.

Below is what I currently have.

        Dim strRelFilename As String = "Resources\TestCSV.csv"

        'Get the path of the executable (i.e., the main app-directory).
        Dim strStartupPath As String = System.IO.Path.GetDirectoryName(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName)

        Dim strFullFilename As String = Path.Combine(strStartupPath, strRelFilename)
        'Stop if the CSV doesn't exist
        If Not File.Exists(strFullFilename) Then
            MsgBox(strRelFilename & " doesn't exist")
            Return
        End If

        Dim sb As New Text.StringBuilder

        For g As Long = 0 To numCombos - 1

            For y As Long = 0 To numVars - 1
                sb.Append(fArray(y)(g) + ",")
            Next
            sb.Remove(sb.Length - 1, 1) 'Remove trailing ","
            sb.Append(Environment.NewLine) 'Finish the current line
        Next

        'Write the file
        My.Computer.FileSystem.WriteAllText(strFullFilename, sb.ToString(), False)

Any help is appreciated. Thanks!

[Shaggy Hiker]

There aren't any pages in a CSV file, so that's out.

One alternative would be to use multiple CSV files, with the first million records in the first one, and so on. The other alternative would be if Excel had some means to fill extra records onto a second sheet, which I've never heard of, and you didn't see it doing that, so it seems unlikely.

What's the point of using Excel anyways? Nobody can reasonably view a million records, let alone several million. Therefore, I would assume that the point is to do some kind of math, but you are working in a programming language already, and the language could do the math as well.

[noobsesher]

Okay sounds good. I will use multiple csv files. It took almost no time at all to build the csv file with 5 million records. I am gonna see how it works breaking it up into 5 csv files then copying each to a different sheet in an excel wb. Thank you for your help.

But honestly I dont see the point either. I know we are going to build this into another application that runs economics based off oil & gas well production simulations, but as for why my boss wants to see the actual outputs in excel, I am unsure.

Thank you again for your help.

[Shaggy Hiker]

One thing to note is that I believe that Excel workbooks are now all XML. I've never really looked at that, but if that is the case, then the worksheets must be represented in the XML along with the data. XML is just text, just like a CSV is text. It's more complicated text, because there are the tags, but there are also tools to work with writing to XML. Therefore, it should be possible to build a whole workbook in XML such that Excel will display the whole thing. That may be quite a bit more work, though, and it requires that you be using one of the newer versions of Excel...though that move to XML based files may have been 2010.

[RDevos]

if you use the jet text isam to create a recordset of that CSV file
you could use the CopyFromRecordset method to transfer it to an excel sheet
https://msdn.microsoft.com/en-us/lib.../ff839240.aspx

[Shaggy Hiker]

That doesn't sound like the right order of business, to me. Since Excel can open a CSV as easily as it opens a spreadsheet, what's the point of doing anything at all to copy? Are you thinking that could be used to move the lines beyond the limit to a new spreadsheet?

[RobDog888]

The vast majority of offices should be running on some version of Excel 2002 or above as that when MS introduced the xml based file format for it. I would recommend using it for records above 1,048,576. Pre-2007 only can manage 65,565 records so your boss should be running 2007 or newer

[noobsesher]

If I am not concerned about the MS version, (Everyone in my office is on the most current one) then does using a recordset make the task any easier?

As of now I have 5 CSV files in a folder. I open up the number of CSV files needed and write 1 million rows of records to each.

My next action was to open an xl workbook with 5 sheets and try to copy the values from each CSV file to a sheet in the xl workbook.
I am unsure the best method to do this copying however.

[RDevos]

That doesn't sound like the right order of business, to me. Since Excel can open a CSV as easily as it opens a spreadsheet, what's the point of doing anything at all to copy? Are you thinking that could be used to move the lines beyond the limit to a new spreadsheet?

if the recordset has more records than there are rows in a sheet
the copying stops at the end of the sheet
and the recordsets recordpointer is on the next uncopied record
and you can just copy the rest of the recordset to another sheet
and so on--and so on, as implied by the remark on the link (i suppose)

but who knows, maybe excel is smart enough to just use a new sheet (who knows ?)
in any case i think it is worth a try

[noobsesher]

if the recordset has more records than there are rows in a sheet
the copying stops at the end of the sheet
and the recordsets recordpointer is on the next uncopied record
and you can just copy the rest of the recordset to another sheet
and so on--and so on, as implied by the remark on the link (i suppose)

but who knows, maybe excel is smart enough to just use a new sheet (who knows ?)
in any case i think it is worth a try

Can you please explain what you mean by "jet text isam"
I have googled it but still do not understand what it does.

[RDevos]

with a reference to Microsoft DAO 3.6 Object Library

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim dbe As DAO._DBEngine
        Dim db As DAO.Database
        Dim rs As DAO.Recordset

        dbe = New DAO.DBEngine
        db = dbe.OpenDatabase("E:\CSV", False, False, "TEXT;")
        rs = db.OpenRecordset("Sample.CSV")
        Me.Text = rs.Fields(0).Value.ToString & "   " & rs.Fields(0).Name & "   " & rs.RecordCount

        rs.Close()
        db.Close()

    End Sub

E:\CSV is the dir of the csv file
Sample.CSV =the name of he csv file

i have a question about the above code
should i add:
rs = Nothing
db = Nothing
dbe = Nothing
or is that not needed ?

[RDevos]

may come in handy to :
https://support.microsoft.com/EN-US/help/295646

[Shaggy Hiker]

DAO is ancient technology that was being phased out by the time VB6 came along. The same kind of thing could be done with ADO.NET using a datareader, but that makes for a convoluted process because you'd be creating the CSV files from the data, then reading them back in to use a datareader. Before doing that, you might as well just write them out to Excel directly, which is an option, but I'm not sure that it would be a good option.

Over in the .NET CodeBank, I have a thread on writing a datatable to Excel. I was writing dozens of records, not millions, and the performance was acceptable. I really don't think it would be with millions of records, at least not with that code. What I'd be looking at is writing to XML rather than a CSV, but having never done that, I can't say more about it.

Actually, I think I'd be asking my boss what the point of going to Excel really was. You said that you don't understand it. I wonder if your boss does.

[RDevos]

Shaggy, very instructive and constructive your post

i just tried out the following in an excel 2010 module:
(with a reference to microsoft office 14.0 access database engine object)

Sub test()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = OpenDatabase("E:\CSV", False, False, "TEXT;")
    Set rs = db.OpenRecordset("Sample.CSV")
    Blad1.Range("A1").CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

the csv file has 36600 records and 18 fields
were copied into excel 2010 instantaniously on pushing F5

eagerly awaiting your suggestions and code

[RDevos]

If I am not concerned about the MS version, (Everyone in my office is on the most current one) then does using a recordset make the task any easier?

As of now I have 5 CSV files in a folder. I open up the number of CSV files needed and write 1 million rows of records to each.

My next action was to open an xl workbook with 5 sheets and try to copy the values from each CSV file to a sheet in the xl workbook.
I am unsure the best method to do this copying however.

the easy method is probably just let excel import the data
i think it is data-text on the menu (cant be sure i have a dutch version of excel 2010 installed)
and this laptop was a gift from my grand children
probably with a strange version of office
since it can not be activated becauses the maximum number of activations has been reached
and the functionality has been reduced (what ever that may mean)

[jdc20181]

Just for reference I gave you a link to a old thread of mine with a example someone made for me. It has the ability to open and save.
(http://www.vbforums.com/showthread.p...e-NEW-csv-file)
There really isn't much more, I can help you with as far multiple pages, that is something left to a certain program like Math in Open office or libre office or Excel in MS office.

... Why not use Excel to begin with?