将 CSV 文件导入 Excel [英] Import CSV files into Excel
问题描述
我想在以下方面寻求您的帮助:
I would like to ask for your help with the following:
我有从软件应用程序导出的 CSV 文件,我需要将其导入 Excel 以分析数据.每天生成 40-50 个 CSV.现在我通过从文本中获取外部数据"手动执行此操作.导入时记录的代码为:
I have CSV files exported from a software application that I need imported in Excel to analyse the data. Daily are generated 40-50 CSVs. For now I do this manually through "Get External Data from Text". The code recorded during the import is:
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
我希望能够从选定的文件夹中自动导入所有 CSV 文件,我将在其中放置新文件并启动导入过程.每个文件应立即插入到前一个文件的最后一行之后.
I want to be able to import automatically all CSV files from a selected folder where I'll put new files and launch the import process. Each file should be inserted immediately after last row of the previous files.
非常感谢您的帮助.
推荐答案
把你记录的代码放在一个函数中,用一个变量替换静态文件名,然后为每个*.csv
文件夹中的文件.要使下面的示例工作,您需要使用此宏将文件保存在与 csv 文件相同的文件夹中.对于我的快速测试,我必须将分隔符从 ;
替换为 ,
,并删除最后一行 .UseListObject = False
.
Put the code you recorded in a function, replacing the static file name with a variable, then call that function for each *.csv
file in the folder. The get the example below to work you need to save a file with this macro in the same folder as the csv files. For my quick test I had to replace the separator from ;
to ,
, and to remove the last row .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
这篇关于将 CSV 文件导入 Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!