[Ghoztrider]

Good Afternoon,

I've made a rudimentary program that combines multiple files into one single file. Currently if I combine two .csv files together the output comes out correctly for what I need, which is a coma delimited file. When I try to combine two .csv files into a .xls file it doesn't format correctly (I get all the data separated by commas, but thrown into one column in the .xls file). For this reason I believe I need to add a delimiter.I'll post my code below.

I've tried adding a delimiter using the split method, however Vb Express tells me that I can't (Value of type 'String' cannot be converted to '1-dimensional array of String'). I'm still really new to VB and I'm not sure about the best course of action. I'll gladly accept any constructive criticism and help with this problem and the program in general. Thank you.

Public Class frmMain
    'Sets up array as read only.
    Public ReadOnly Property FileNames() As String
        Get
            Return FileNames
        End Get
    End Property
    

    Private Sub btnAppendData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAppendData.Click

        'Sets up the 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 = "Text Files (*.txt)|*.txt|CSV Files (*.csv)|*.csv|Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
        sfdMain.Filter = "Text Files (*.txt)|*.txt|CSV Files (*.csv)|*.csv|Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"

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

        ' Display the dialog box.
        ofdMain.FileName = ""
        ResultOFD = ofdMain.ShowDialog()


        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) 'write file to path given
                    sw.Write(FileContents)  'Writes contents of variable to file
                    sw.Close()  'Close StreamWriter
                Next File
                MessageBox.Show(FileNameWrite + " Written Successfully!") 'File status
            End If
        End If
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub
End Class

[macosxnerd101]

Moved to VB.NET. Please reserve the VB.NET Advanced Discussion forum for advanced discussions, not help questions.

[m_wylie85]

Hi

When you say your out put is OK i take it you are out putting to a text file?

if i am right, and if the file is delimited with a coma your lines in the text file would look something like so:

(i am just using names)

John, Peter, Steven
Zack, Paul, Mark

and so on, is that the kind of out put you are getting?.

This post has been edited by m_wylie85: 24 January 2012 - 02:35 PM

[Ghoztrider]

Hi m_wylie85,

Hopefully I can explain this a little better.

When I convert from .csv to .csv Zack, Paul, and Mark take up there own columns in the csv file for a total of 3 columns. This is working as intended.

When I convert a .csv file to a .xls file the 3 names look like this: Zack, Paul, Mark (the problem with this is that those 3 names make up one column in the excel file). This is not how I want the csv to xls conversion to work.

The comma should indicate the start of a new column when I convert the file.

[CharlieMay]

You might try looking into the TextFieldParser

I think it's supposed to handle situations like this but I'll admit I haven't worked with it much.

[AdamSpeight2008]

Tutorial on the TextFieldParser

[Ghoztrider]

Thanks for the tip CharlieMay, another option I'm now looking into!

[Ghoztrider]

I'm getting a little closer to the end game, but not close enough! I have to reference excel code in this program to get it converted to an .xls file. I need to set the row index to the 1st row and column index to the 1st column.

Is this how I set the row index?

oSheet.Range("A1").Select()

if so, how do I set the column index? Thanks!

[CharlieMay]

"A1" would be picking a row and column. A being the column, 1 being the row. Or am I completely misunderstanding what you're asking?

[Ghoztrider]

It's more like I'm not explaining it well enough

I'm trying to work out this logic:

I can work out the vb coding (for each, incrementing), I'm unsure about the excel coding.

set row index to first row <== Need Help
for each selected csv file
  set column index to first column  <== Need Help
  for each line in this file
    for each field in this line
      copy this field to the current column  <== Need Help
      increment column index
    next
  increment row index
  next
next

[CharlieMay]

I'm pretty sure there is a Cells property where you specify the rows and the columns.

oSheet.Cells(row, col)

Of course you can leave either static to increment just a row or just a column

For Row as Integer = 1 to 10
  MessageBox.Show(oSheet.Cells(row,1).Value) 'Pull the first 10 rows of column 1
Next

I'm a little rusty on the subject, but I'll look around and see if I've got any code that uses this method.

This post has been edited by CharlieMay: 01 February 2012 - 09:49 AM

[Ghoztrider]

Thanks...I really appreciate your assistance!