使用 VBA 将数据从 .csv 导入到 Excel 文档

Importing data from .csv to excel document using VBA

提问by Spnkmyr

wondering if you can help out with a VBA issue. I pieced together the following without really knowing what I was doing:

想知道您是否可以帮助解决 VBA 问题。我在不知道自己在做什么的情况下拼凑了以下内容：

Sub Import_Raw_Stripe_data()

    Dim fileDialog As fileDialog
    Dim strPathFile As String
    Dim strFileName As String
    Dim strPath As String
    Dim dialogTitle As String
    Dim Tworkbook As Workbook
    Dim Sworkbook As Workbook


dialogueTitle = "Select File to Import"
Set fileDialogue = Application.fileDialog(msoFileDialogFilePicker)
With fileDialogue
    .InitialFileName = "L:\Downloads"
    .AllowMultiSelect = False
    .Filters.Clear
    .Title = dialogueTitle

    If .Show = False Then
        MsgBox "No file selected."
        Exit Sub
    End If
    strPathFile = .SelectedItems(1)
End With

Set Sworkbook = Workbooks.Open(fileName:=strPathFile)
Set Tworkbook = ThisWorkbook



End Sub

Which, as far as I can tell opens a file dialog in excel, allows a user to choose a document and then opens it.

据我所知，在 excel 中打开一个文件对话框，允许用户选择一个文档然后打开它。

What I would like to do is the following:

我想做的是以下内容：

1) Open a file dialogue and select a .csv file to import data from (complete?) into a .xlsm master file (with multiple sheets).

1) 打开文件对话框并选择一个 .csv 文件以将数据从（完整？）导入 .xlsm 主文件（包含多张工作表）。

2) Select certain columns from the .csv (column A, Q, R and S in this case), copy them and import them into the second sheet of the master excel file entitled "Raw Stripe Data".

2) 从 .csv 中选择某些列（在本例中为 A、Q、R 和 S 列），将它们复制并导入到名为“原始条纹数据”的主 excel 文件的第二张表中。

Any help in the matter would be greatly appreciated.

对此事的任何帮助将不胜感激。

Update: I managed to find the following code:

更新：我设法找到以下代码：

Sub load_csv()
    Dim fStr As String

    With Application.fileDialog(msoFileDialogFilePicker)
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancel Selected"
            Exit Sub
        End If
        'fStr is the file path and name of the file you selected.
        fStr = .SelectedItems(1)
    End With

    With ThisWorkbook.Sheets("Stripe Raw Data").QueryTables.Add(Connection:= _
    "TEXT;" & fStr, Destination:=ThisWorkbook.Sheets("Stripe Raw Data").Range("$A"))
        .Name = "CAPTURE"
        .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 = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        ActiveWorkbook.Save



    End With

End Sub

This works great - but is there anyway to have it not override the data already imported? (for example, if i use it twice, the second import overrides the first).

这很好用 - 但无论如何让它不覆盖已经导入的数据？（例如，如果我使用它两次，第二个导入会覆盖第一个）。

回答by Leviathan

ThisWorkbook.Sheets("Stripe Raw Data").Range("$A$1")specifies where the imported data is written to, that is the first cell of the sheet Stripe Raw Data.

ThisWorkbook.Sheets("Stripe Raw Data").Range("$A$1")指定导入数据的写入位置，即工作表Stripe Raw Data的第一个单元格。

Adapt this to your liking if you want the next import at another location.

如果您想在其他位置进行下一次导入，请根据自己的喜好进行调整。

As mentioned in the comments, you could change load_csv()to take the output destination as a parameter. If you also change it from Subto Function, you can return the number of rows imported:

如评论中所述，您可以更改load_csv()为将输出目标作为参数。如果您也将其从 更改Sub为Function，则可以返回导入的行数：

Function load_csv(rngDestination As Range) As Long
    '...

    With ThisWorkbook.Sheets("Stripe Raw Data").QueryTables.Add(Connection:= _
    "TEXT;" & fStr, Destination:=rng)

        '...

        .Refresh BackgroundQuery:=False
        load_csv = .ResultRange.Rows.Count

        '...

End Function

Now you can repeatedly call load_csvand provide it with the range where the output should begin for example:

现在您可以重复调用load_csv并为其提供输出应开始的范围，例如：

Dim rngOutput As Range
Dim lngRows As Long

Set rngOutput = ThisWorkbook.Sheets("Stripe Raw Data").Range("$A")

lngRows = load_csv(rngOutput) ' load first file
lngRows = lngRows + load_csv(rngOutput.Offset(lngRows)) ' load second file
lngRows = lngRows + load_csv(rngOutput.Offset(lngRows)) ' load third file
lngRows = lngRows + load_csv(rngOutput.Offset(lngRows)) ' load fourth file

There is still much room for improvement:

还有很大的改进空间：

• Removing duplicate headers
• Creating a loop instead of explicitly calling load_csvfour times
• Better control for the user to select files (multiselect)
• Disconnecting the imported data from the QueryTable to reduce dependencies even after the import
• Not importing in ThisWorkbookbut afterwards saving ActiveWorkbook- they may not always be the same
• ...

• 删除重复的标题
• 创建循环而不是显式调用load_csv四次
• 更好地控制用户选择文件（多选）
• 将导入的数据与 QueryTable 断开连接以减少即使在导入后的依赖性
• 不导入ThisWorkbook但随后保存ActiveWorkbook- 它们可能并不总是相同的
• ...

But that's not part of this question. After all, all you wanted to know was:

但这不是这个问题的一部分。毕竟，您只想知道：

is there anyway to have it not override the data already imported?

无论如何让它不覆盖已经导入的数据？

I hope I could sufficiently answer this with the above.

我希望我能用上面的内容充分回答这个问题。