Importing .csv files into Excel

[melmac82]

I am looking to import numerous .csv files into one worksheet in Excel. Each .csv file has 3 columns of information, and I would like to put the first .csv file into the first 3 columns of the Excel worksheet, the second .csv file in the second 3 columns, ect. until all files have been imported. Ideally I only actually need the first 2 columns from the first .csv file and the second column from all the rest, but I know this is harder to do and having all 3 columns would work fine. I know that I can write a macro in Excel that can do this, but I am not at all proficient with VBA and was wondering if anyone has done anything like this.

 

[melmac82]

bump

 

[dinalie]

Sub test()

' Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\dlie\My Documents\csv1.csv", Destination:= _
Range("A1"))
.Name = "csv1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ","
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("D1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\dlie\My Documents\csv2.csv", Destination:= _
Range("D1"))
.Name = "csv2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ","
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("G1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\dlie\My Documents\csv3.csv", Destination:= _
Range("G1"))
.Name = "csv3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ","
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.Save
End Sub

 

[melmac82]

Thanks, that helps, but is there any way to do this in a loop so that it doesn't matter how many .csv files I have? I'm not sure how to go about incrementing so that I get the next set of data in the next 3 columns.

 

[bill_reinwald]

You can do something like this, it'll open each CSV in a folder, copy columns A to C, close the CSV and then just paste the selection to the first blank column on the right side of the worksheet.

Sub getfiles()

strpath = "FolderName\"
Strfile = Dir(strpath & "*.csv")

Do While Strfile <> ""

Workbooks.Open (strpath & Strfile)
Columns("a:c").Copy
ActiveWindow.Close
Workbooks("mainfiletostoredata.xls").Activate
lastcol = Range("A1").End(xlToRight) + 1
Cells(1, lastcol).Select
ActiveSheet.paste

Strfile = Dir()

Loop

End Sub

 

[melmac82]

Thanks, that helps a lot.