Import data from one CSV file and append to an existing sheet

[lord_yo]

Hi everyone

I'm looking for a VBA script to run from my MASTER workbook which

- lets me pick a CSV file via an Open File dialogue
- opens the chosen CSV file
- copies all the data from the CSV file except the first/header row
- appends the data to a specified sheet in my MASTER workbook (which already contains data in the same format)
- and closes the CSV file

I'm sure this has been written hundreds of times, but I can't seem to find a solution which doesn't include looping through multiple sheets.

Thank you!

 

[John_w]

Try this. Change the name of the sheet in the MASTER workbook where indicated.

If the data doesn't import correctly then record a macro with you doing an Import External Data which runs the Text Import Wizard and use the QueryTables properties generated in place of my QueryTables properties (I reduced them to the bare minimum).

Sub Append_CSV_File()
    
    Dim csvFileName As Variant
    Dim destCell As Range
    
    Set destCell = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)      'CHANGE SHEET NAME
    
    csvFileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
    If csvFileName = False Then Exit Sub
    
    With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & csvFileName, Destination:=destCell)
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
    
    destCell.Parent.QueryTables(1).Delete

End Sub

 

[lord_yo]

That worked on the first attempt - thank you!

 

[septbaby09]

Hi,

I am looking for a similar vba, but instead of using csv file, I will be using xls file. My master work book has multiple worksheets. I would like to open the xls file and copy the data (except the first row) and append them to a particular worksheet in my master work book.

Kindly advise how to modify this vba to suit my needs.

Thanks.

Regards,
Meiqi

 

[John_w]

Similar, but not the same because importing CSV (text) data is different to importing Excel sheet data. Therefore please start your own thread and link to this one if you think it will help people answering your request.

I would also have a go at using the Macro Recorder to generate the code and tweak it as needed.

 

[ahafeez]

Similar, but not the same because importing CSV (text) data is different to importing Excel sheet data. Therefore please start your own thread and link to this one if you think it will help people answering your request.

I would also have a go at using the Macro Recorder to generate the code and tweak it as needed.

Thanks for this code. I'm trying to import CSV files into a pre-generated report with empty tables. Your macro is doing most of it but I'm running into 2 issues. 1 is that the CSV content isnt being pasted into the active cell but instead to the left of it. The second is that I can't seem to find a way for the macro to mimic the Insert Cut Cells function instead of simply pasting them. I need this to push the empty tables below it appropriately so I can continue populating them with the same macro.

I know this thread is pretty old but I am stumped and your macro is the closes thing to me finishing this.

I've included a sample file to elaborate http://www.filedropper.com/excelhelp. Any help appreciated!

 

[John_w]

1 is that the CSV content isnt being pasted into the active cell but instead to the left of it.

Because the macro imports to the cell referenced by the destCell variable, not the active cell.

The second is that I can't seem to find a way for the macro to mimic the Insert Cut Cells function instead of simply pasting them. I need this to push the empty tables below it appropriately so I can continue populating them with the same macro.

One way is to change the code to import the CSV file into a separate worksheet, get the size of the imported data (from the QueryTable.ResultRange.Rows.Count property), insert the same number of rows in the destination sheet and copy the data from the separate worksheet to the destination worksheet.

 

[ahafeez]

I have changed the destCell variable to point to the ActiveCell. I've also changed it so it counts the number of lines beforehand and add the appropriate amount of empty rows before pasting the content. It's actually working perfectly for the first table but it doesn't count the lines properly for the other files. I just cant seem to figure out why. Here's my code:

Sub ImportMacro()
Dim csvFileName As Variant
Dim destCell As Range

Set destCell = ActiveCell

csvFileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
If csvFileName = False Then Exit Sub
Open csvFileName For Input As #1
Do While Not EOF(1)
Line Input #1, sLineOfText
recordCounter = recordCounter + 1
Loop
result = MsgBox("Line Count: " & recordCounter) 'This is just to inform me if the line count worked properly.

counter = 0
Do While counter <= recordCounter
ActiveCell.Offset(1).EntireRow.Insert
counter = counter + 1
Loop
With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & csvFileName, Destination:=destCell)
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.Refresh BackgroundQuery:=False
End With
destCell.Parent.QueryTables(1).Delete
Close #1
End Sub


Thanks for your help

 

[ahafeez]

Is it not possible to edit posts on this forum? Anyway, I figured out why the rest of the CSVs arent importing properly. The one that works was using CRLF as a line feed while the ones that aren't working are using LF. After converting it to CRLF, it is importing properly again. 1 step closer! Now I'm trying to figure out how I can batch-convert the LFs to CRLFs since each report will be needing to import data from roughly 15 different CSV files. Any ideas?

 

[ahafeez]

Is it not possible to edit posts on this forum? Anyway, I figured out why the rest of the CSVs arent importing properly. The one that works was using CRLF as a line feed while the ones that aren't working are using LF. After converting it to CRLF, it is importing properly again. 1 step closer! Now I'm trying to figure out how I can batch-convert the LFs to CRLFs since each report will be needing to import data from roughly 15 different CSV files. Any ideas?

Feels like I'm spamming but can't find the edit feature :S

Here's the solution to replace all LFs with CRLFs in batch: notepad++ - Change EOL on multiple files in one go - Stack Overflow