将CSV文件导入Excel [英] Import CSV files into Excel

问题描述

我要求您对以下方面的帮助：

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

我有一个从Excel中导入的软件应用程序导出的CSV文件，用于分析数据。每日生成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屋！