非常大的ex​​cel文件 - 如何在表之间复制数据？ [英] Very large excel file - how to copy data between sheets?

问题描述

我需要将一些csv文件导入到excel 2010中，并创建一个非常简单但非常大的数据库。

整个故事将是五列和数千行。

VBA也很简单 - 将数据从一张表复制到另一张 - 反之亦然。

但是我需要关心内存需求，因为可能非常大的文件大小。

I need to to import some csv files into excel 2010 and create a very simple, but very large database.
The whole story will be - five columns and thousands of rows.
VBA is also simple - copy data from one sheet to another - and vice versa.
But I need to care about memory requirement, because of potentially very large file size.

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim r1 As Range
Dim r2 As Range
Set ws1 = Sheets("01")
Set ws2 = Sheets("02")
Set r1 = ws1.Range("A1:B10") ' for example
Set r2 = ws2.Range("C5:D14")
r1.Copy Destination:=r2 'first way
r2.Value = r1.Value ' second way

这两种方法之间是否存在差异，在内存/耗时的范围？

在我会有超过10,000行。文件的大小是多少？

Is there any differences between this two methods, in the scope of memory/time consuming?
At the and I will have over 10,000 rows. What will be the size of the file?

推荐答案

这个代码块有一些我曾经参与的项目的具体细节，但是应该帮助让您了解如何通过VBA导入CSV文件（稍微清理）：

This code block had some specifics for a project I was on, but should help get you started on how to import CSV files (somewhat cleaning) through VBA:

Public Sub ImportCSV(strPath As String, strFile As String, strExt As String, wbDestination As Workbook, Optional wsDest As Worksheet, Optional strRange As String, Optional blHeaders As Boolean = True)
'imports given CSV file into given sheet at given range _
    defaults to comma separated delimiters

Dim wsDestination As Worksheet
Dim strFileName As String
strFileName = strPath & strFile & ".csv"


If wsDest Is Nothing Then Set wsDestination = wbDestination.Worksheets.Add(, wbDestination.Worksheets(wbDestination.Worksheets.Count)) Else: Set wsDestination = wsDest
If strRange = "" Then strRange = "$A$1"

With wsDestination.QueryTables.Add(Connection:="TEXT;" & strFileName, Destination:=wsDestination.Range(strRange))
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        .Delete
    End With

If Not blHeaders Then wsDestination.Range(strRange).EntireRow.Delete

End Sub

这篇关于非常大的ex​​cel文件 - 如何在表之间复制数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！