使用VBA将数据从.csv导入到Excel文档 [英] Importing data from .csv to excel document using VBA

问题描述

很想知道是否可以解决VBA问题.我在不真正知道自己在做什么的情况下拼凑了以下内容:

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

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

据我所知，这可以在excel中打开文件对话框，允许用户选择一个文档，然后将其打开.

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

我想做的是以下事情:

1)打开文件对话框，然后选择一个.csv文件以将数据(完整?)导入到.xlsm主文件(包含多张工作表)中.

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

2)从.csv中选择某些列(在本例中为A，Q，R和S列)，将其复制并将其导入到主excel文件的第二张表中，该表名为原始条带数据".

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".

在此问题上的任何帮助将不胜感激.

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$1"))
        .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).

推荐答案

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

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.

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

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

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

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

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

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

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

Dim rngOutput As Range
Dim lngRows As Long

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

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

还有很多改进的余地:

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

• Removing duplicate headers
• Creating a loop instead of explicitly calling load_csv four 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 ThisWorkbook but afterwards saving ActiveWorkbook - they may not always be the same
• ...

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

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.

这篇关于使用VBA将数据从.csv导入到Excel文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！