locked
How to combine multiple csv files into an existing excel sheet RRS feed

  • Question

  • I've programmed a vb.net application. This is a matching applcation generates 3 lisboxes. I have written these to a csv files.

    Private Sub Button3_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles Button3.MouseClick, Button4.MouseClick, Button5.MouseClick 
            ListBox3.Items.Add(TimeString) '(ElapsedTime.Milliseconds).ToString)
            If sender Is Button3 Then ListBox2.Items.Add("1")
            If sender Is Button4 Then ListBox2.Items.Add("2")
            If sender Is Button5 Then ListBox2.Items.Add("3")
            chooseImg() 
        End Sub
    
    
        Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click
            Using sw As StreamWriter = New StreamWriter("C:\Documents and Settings\Desktop\Poo\Poofile.csv") 
                For i As Integer = 0 To ListBox1.Items.Count - 1
                    sw.WriteLine(ListBox1.Items(i) & "," & ListBox2.Items(i) & "," & ListBox3.Items(i))
                Next
                sw.Close()
            End Using
            SaveButton.Enabled = False
        End Sub
    

    This produces my csv files and I have the contents of each listbox nicely written to individual columns...no commas yeahyyy!!!

    My application stores login information to excel. How do I pull in all the csv files into the existing excel file? I don't have any column names in the csv file just three lists of numbers. Is it better to send each file into separate worksheets and perhaps call the the worksheet the same name as the csv file? Or is there a way of combining all the data into one excel sheet ...perjaps pull the csv file into the same sheet?



    • Edited by renuv8 Thursday, January 19, 2012 4:09 PM delete confidential info
    Thursday, January 19, 2012 3:55 PM

Answers

  • Renuv8,

    I don't really see several CSV files : I see only the C:\Documents and Settings\Desktop\Poo\Poofile.csv

    What are the other files ?

    The way you have written your program, you can view directly a .CSV file in Excel without any additional coding: Excel reads .CSV files directly. So if you have several .CSV files, they will be read as different files.

    If you want to gather various .CSV files into the same workbook, you will have to code that, using the Excel interop interface.

    • Proposed as answer by Mike Feng Wednesday, January 25, 2012 5:24 PM
    • Marked as answer by Mike Feng Sunday, January 29, 2012 7:43 AM
    Thursday, January 19, 2012 4:21 PM
  • Hi Renuv,

    Welcome to the MSDN Forum.

    Add to Sygrien's suggestion: this is the code of save several CSV file in one excel file:

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim Exl As New Excel.Application()
    
            'make the application visible when processing
            Exl.Visible = True
    
            Dim wb As Excel.Workbook = Exl.Application.Workbooks.Add 'create a new Workbook
            Dim csvFile As String = Dir("D:\*.csv")
    
            Do While csvFile <> ""
                Dim wb2 As Excel.Workbook = Exl.Workbooks.Open("d:\" & csvFile)
                wb.Sheets.Add() ' add a new worksheet
                wb2.Sheets(1).cells.copy()
                wb.Sheets(1).paste()
                wb.Sheets(1).name = csvFile 'rename the worksheet
                csvFile = Dir()
            Loop
    
            'The line below will save only the active workbook with .xls extention that Exel Viewer can open
            'Exl.DisplayAlerts = vbFalse
            Exl.AlertBeforeOverwriting = vbFalse
            wb.SaveAs("D:\test.xls", -4143)
            wb.Close()
    
            'EXIT Application
            Exl.Quit() 'when you exit your application, the process will exit         
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Exl)
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Exl)
            Exl = Nothing
            GC.Collect()
            GC.WaitForPendingFinalizers()
    
        End Sub
    
    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Mike Feng Sunday, January 29, 2012 7:43 AM
    Wednesday, January 25, 2012 5:28 PM

All replies

  • Renuv8,

    I don't really see several CSV files : I see only the C:\Documents and Settings\Desktop\Poo\Poofile.csv

    What are the other files ?

    The way you have written your program, you can view directly a .CSV file in Excel without any additional coding: Excel reads .CSV files directly. So if you have several .CSV files, they will be read as different files.

    If you want to gather various .CSV files into the same workbook, you will have to code that, using the Excel interop interface.

    • Proposed as answer by Mike Feng Wednesday, January 25, 2012 5:24 PM
    • Marked as answer by Mike Feng Sunday, January 29, 2012 7:43 AM
    Thursday, January 19, 2012 4:21 PM
  • So you have three CSV files but the columns are not separated by commas? What is the column delimiter?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, January 19, 2012 4:21 PM
  • Hi Renuv,

    Welcome to the MSDN Forum.

    Add to Sygrien's suggestion: this is the code of save several CSV file in one excel file:

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim Exl As New Excel.Application()
    
            'make the application visible when processing
            Exl.Visible = True
    
            Dim wb As Excel.Workbook = Exl.Application.Workbooks.Add 'create a new Workbook
            Dim csvFile As String = Dir("D:\*.csv")
    
            Do While csvFile <> ""
                Dim wb2 As Excel.Workbook = Exl.Workbooks.Open("d:\" & csvFile)
                wb.Sheets.Add() ' add a new worksheet
                wb2.Sheets(1).cells.copy()
                wb.Sheets(1).paste()
                wb.Sheets(1).name = csvFile 'rename the worksheet
                csvFile = Dir()
            Loop
    
            'The line below will save only the active workbook with .xls extention that Exel Viewer can open
            'Exl.DisplayAlerts = vbFalse
            Exl.AlertBeforeOverwriting = vbFalse
            wb.SaveAs("D:\test.xls", -4143)
            wb.Close()
    
            'EXIT Application
            Exl.Quit() 'when you exit your application, the process will exit         
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Exl)
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Exl)
            Exl = Nothing
            GC.Collect()
            GC.WaitForPendingFinalizers()
    
        End Sub
    
    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Mike Feng Sunday, January 29, 2012 7:43 AM
    Wednesday, January 25, 2012 5:28 PM