Geek Answers Handbook

Import CSV files to Excel

I would like to ask you for help with the following:

I have CSV files exported from a software application that I need to import into Excel for data analysis. 40-50 CSVs are generated daily. So far I am doing this manually through "Get external data from text." Code recorded during import:

With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;SYSTEM:Users:catalin:Documents:LINELLA:WH Analytics:data:pick 01-18:050:Inquiry closed lists SKU_0142.csv" _ , Destination:=Range("A1704")) .Name = "Inquiry closed lists SKU_0142" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .TextFilePromptOnRefresh = False .TextFilePlatform = xlMacintosh .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileOtherDelimiter = ";" .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .Refresh BackgroundQuery:=False .UseListObject = False End With Selection.End(xlDown).Select Range("A1710").Select

I want to be able to automatically import all CSV files from the selected folder, where I will add new files and start the import process. Each file should be inserted immediately after the last line of previous files.

Your help would be greatly appreciated.

+7
import vba excel csv
source share
1 answer

Put the code written into the function, replacing the static file name with a variable, then call this function for each *.csv file in the folder. To get the example below, you need to save the file with this macro in the same folder as the csv files. For my quick test, I had to replace the delimiter from ; before , and delete the last line .UseListObject = False .

 Sub ImportAllCSV() Dim FName As Variant, R As Long R = 1 FName = Dir("*.csv") Do While FName <> "" ImportCsvFile FName, ActiveSheet.Cells(R, 1) R = ActiveSheet.UsedRange.Rows.Count + 1 FName = Dir Loop End Sub Sub ImportCsvFile(FileName As Variant, Position As Range) With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & FileName _ , Destination:=Position) .Name = Replace(FileName, ".csv", "") .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .TextFilePromptOnRefresh = False .TextFilePlatform = xlMacintosh .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileOtherDelimiter = "," .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .Refresh BackgroundQuery:=False End With End Sub
+8
source share

More articles:


All Articles