Convert .csv to .xls

[Ghoztrider]

Good Day!

I've created a rudimentary program that takes multiple .csv files and creates one merged .csv file. What I'm looking to do is after merging the file into one, I want to convert it to an .xls file, however, when I do this the converting is not correct.

Here is an example. Let's say csv file 1 contains this: (A1)Moe (B1)Larry (C1)Curly, and csv file 2 contains this: (A1)Joe (B1)Sally, when I merge these two files I get the following:

(A1)Moe (B1)Larry (C1)Curly
(A2)Joe (B2)Sally

This works as intended. The following is an example of what I get if I try to merge and convert to .xls.

(A1)Moe, Larry, Curly
(A2)Joe, Sally

Not quite what I wanted, but close. I need it to look like the first example but be in .xls format. I've done some research on the the net on various routes to take and have tried different ones with no success. I'm still fairly new to vb and would appreciate any direction given. See below for what I have working so far.......

Public Class frmMain
    Private Sub btnAppendData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAppendText.Click

        'Declare dialog boxes
        Dim ResultOFD As DialogResult
        Dim ResultSFD As DialogResult

        ' Allow the user to select multiple files.
        ofdMain.Multiselect = True
        ofdMain.Title = "Please Select Multiple Files"
        sfdMain.Title = "Please Choose New File Name"

        ' Set the file dialog to filter for text files.
        ofdMain.Filter = "All Files (*.*)|*.*|Text Files (*.txt)|*.txt|CSV Files (*.csv)|*.csv|Excel Files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xlsx"
        sfdMain.Filter = "All Files (*.*)|*.*|Text Files (*.txt)|*.txt|CSV Files (*.csv)|*.csv|Excel Files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xlsx"

        Dim FileNameWrite, File, FileContents As String
        Dim sw As StreamWriter

        ' Display the Open File dialog box with a null value for default file
        ofdMain.FileName = ""
        ResultOFD = ofdMain.ShowDialog()

        'If Open and Save file dialog boxes Ok then.......
        If ResultOFD = Windows.Forms.DialogResult.OK Then
            ResultSFD = sfdMain.ShowDialog()    ' Display the dialog box.
            FileNameWrite = sfdMain.FileName
            If ResultSFD = Windows.Forms.DialogResult.OK Then

                'For Each loop 
                For Each File In ofdMain.FileNames
                    Dim sr As New System.IO.StreamReader(File)  'Sets up StreamReader to read new "File" for each run 
                    File = ofdMain.FileName     'Holds file name
                    FileContents = sr.ReadToEnd     'Reads content to end of file and stores data in FileContents
                    sr.Close()  'Close StreamReader
                    sw = New StreamWriter(FileNameWrite, True) 'Open streamwriter to new file name created, True indicates data is appended , not overwritten
                    sw.Write(FileContents)  'Writes contents of variable to file
                    sw.Close()  'Close StreamWriter
                Next File
                MessageBox.Show(FileNameWrite + " was written successfully!") 'File status
            End If
        End If
    End Sub

[Reverend Jim]

The process I would use is as follows:

create an Excel application object
create a workbook
select the first worksheet

set row index to first row

for each selected csv file

    set column index to first column

    for each line in this file
        for each field in this line
            copy this field to the current column
            increment column index
        next
        increment row index
    next
    
next

save workbook
close workbook

dispose of worksheet object
dispose of workbook object

What part are you unclear on?

[ChrisPadgham]

why don't you try opening the csv file as a datasource rather than a file
similarly the xls and use SQL

INSERT INTO myXLdb (col1, col2, col2) SELECT col1, col2, col3 FROM myCSV

[Ghoztrider]

Thanks Reverend,

I was a little stumped as to how I would incorporate it into my current program. I've still got a lot to learn about VB :)

Thanks for giving me a basic template, I appreciate it.

The process I would use is as follows:

create an Excel application object
create a workbook
select the first worksheet

set row index to first row

for each selected csv file

    set column index to first column

    for each line in this file
        for each field in this line
            copy this field to the current column
            increment column index
        next
        increment row index
    next
    
next

save workbook
close workbook

dispose of worksheet object
dispose of workbook object

What part are you unclear on?

[Reverend Jim]

Chris - Your approach will work as long as the number of columns is the same for each file. In the example given, it is not. By doing a cell by cell copy it is not necessary to know how many columns are present as long as the columns and rows are contiguous (no blank entries).

[Ghoztrider]

Thanks for your input as well Chris, I'm going to dig in a little deeper and take a look at this.

why don't you try opening the csv file as a datasource rather than a file
similarly the xls and use SQL

INSERT INTO myXLdb (col1, col2, col2) SELECT col1, col2, col3 FROM myCSV