وارد کردن فایل های داده عظیم

Question

دامودار یک فایل CSV دارد که باید به اکسل وارد کند. به طور معمول این مشکلی ایجاد نمی کند، اما این فایل CSV بیش از یک میلیون ردیف در خود دارد. او فکر می کند که آیا راهی برای دریافت چنین فایل عظیمی در اکسل وجود دارد یا خیر.

اکسل برای تعداد ردیف هایی که می توانید در یک کاربرگ داشته باشید محدودیت دارد—تا 1,048,576. همانطور که دامودار کشف کرده است، داشتن یک فایل داده خام که بیش از این تعداد ردیف داشته باشد، بسیار امکان پذیر است. اگر باید آن فایل را به اکسل وارد کنید، انجام این کار تقریبا غیرممکن به نظر می رسد. با این حال، چند کار وجود دارد که می توانید انجام دهید.

یک امکان این است که از فایل متنی خام (آنی که می خواهید وارد کنید) کپی کنید و سپس اندازه هر فایل را کاهش دهید. به عنوان مثال، اگر در مجموع 1.5 میلیون ردیف دارید که باید به اکسل وارد کنید، می توانید دو کپی از فایل متنی خام تهیه کنید. نیمه دوم فایل متنی اول و نیمه اول دوم را حذف کنید. بنابراین، می توانید فایل اول (اکنون 750000 ردیف) را در یک کاربرگ و فایل دوم (همچنین 750000 ردیف) را در دومی وارد کنید.

اگر نمی خواهید فایل های ورودی خود را جدا کنید، ممکن است فایل را به Access وارد کنید. بر خلاف اکسل، Access عملاً محدودیتی در تعداد ردیف هایی که می توانید وارد کنید ندارد. سپس می توانید با فایل در Access کار کنید یا بخش هایی از فایل را برای استفاده در اکسل صادر کنید.

همچنین می توانید از یک ماکرو برای وارد کردن رکوردها در فایل منبع بزرگ استفاده کنید. راه های زیادی برای انجام این کار وجود دارد، اما ایده اصلی پشت هر رویکرد این است که هر ردیف را از فایل منبع واکشی کنید و آن را در ردیف جدیدی از یک کاربرگ قرار دهید. ماکرو باید تعداد ردیف های خود را پیگیری کند و در صورت لزوم به کاربرگ جدید تغییر مکان دهد.

Public Sub LoadFile()
    Dim strLine As String
    Dim I As Long
    Dim J As Long
    Dim iLen As Integer
    Dim iSh As Integer
    Dim lL As Long
    Dim sDelim As String
    Dim MaxSize As Long

    sDelim = Chr(9)
    MaxSize = 1048000
    I = 0
    Open "C:MyDirMyFile.txt" For Input As #5
    Do While Not EOF(5)
        iSh = (I / MaxSize) + 1
        lL = I Mod MaxSize
        Line Input #5, strLine
        If Right(strLine, 1) <> sDelim Then
           strLine = Trim(strLine) & sDelim
        End If
        J = 0
        Do While Len(strLine) > 1
            iLen = InStr(strLine, sDelim)
            Worksheets("Sheet" & iSh).Offset(lL, J).Value = _
              Trim(Left(strLine, iLen - 1))
            strLine = Trim(Right(strLine, Len(strLine) - iLen))
            J = J + 1
        Loop
        I = I + 1
    Loop
    Close #5
End Sub

ماکرو فرض می کند که شما به اندازه کافی کاربرگ در ورک بوک خود دارید تا حاوی داده ها باشد و شماره آنها Sheet1، Sheet2، Sheet3 و غیره است. دو متغیری که می خواهید در برنامه بررسی کنید تنظیمات sDelim و MaxSize هستند. اولی مشخص می کند که چه کاراکتری به عنوان جداکننده فیلد در اطلاعاتی که خوانده می شود استفاده می شود. دومی حداکثر تعداد ردیف های مورد نظر شما را در هر کاربرگ مشخص می کند. همچنین باید توجه داشته باشید که ماکرو فایل متنی MyFile.txt را باز می کند. شما می خواهید این عبارت Open را تغییر دهید تا فایل منبع واقعی را که می خواهید وارد کنید باز شود.

چند مشکل بالقوه وجود دارد که هنگام اجرای یک ماکرو مانند این باید از آنها آگاه باشید. اولین مورد این است که به آرامی اجرا می شود - مانند "برای ناهار استراحت کنید" یا "یک شب به خانه بروید" به آرامی. مدت زمانی که طول می کشد مستقیماً با تعداد ردیف های داده و تعداد فیلدهای هر ردیف مرتبط است. دومین چیزی که باید از آن آگاه باشید این است که کاملاً ممکن است سیستم خود را خراب کنید. این باز هم به مقدار داده ای که وارد می کنید بستگی دارد. این داده ها به حافظه نیاز دارند و مقدار حافظه مورد نیاز با هر ردیف وارد شده افزایش می یابد. اگر حافظه تان تمام شد، خب... (میفهمید).

در نهایت، اگر از نسخه اکسل ارائه شده با Microsoft 365 استفاده می کنید، می توانید با استفاده از Power Query با داده های خود کار کنید. البته این فقط در صورتی کار می کند که بتوانید داده های خود را به نحوی «تبدیل» کنید. این بدان معنی است که می توانید روش هایی را شناسایی کنید که از طریق آنها می توانید داده های خود را به کاربرگ های مختلف بر اساس مقداری که می توان در داده های مورد نیازتان شناسایی کرد، جدا کرد. به عنوان مثال، شاید بتوانید داده ها را بر اساس بخش، منطقه، فروشنده یا مقدار دیگری جدا کنید.

در این صورت مراحل زیر را دنبال کنید:

شکل 1. کادر محاوره ای Import Data.

1. تب Data روبان را نمایش دهید.
2. در گروه Get & Transform Data (سمت چپ نوار)، روی From Text/CSV کلیک کنید. اکسل کادر محاوره ای Import Data را نمایش می دهد. این بسیار شبیه یک جعبه گفتگوی Open استاندارد است.
3. با استفاده از کنترل های موجود در کادر محاوره ای، فایل CSV را که می خواهید وارد کنید، پیدا کرده و انتخاب کنید.
4. روی Import کلیک کنید. اکسل به فایل داده متصل می شود و به زودی بخشی از داده های شما را در یک کادر محاوره ای بزرگ نمایش می دهد.
5. روی دکمه Transform کلیک کنید. اکسل کادر محاوره ای Power Query Editor را نمایش می دهد.
6. روی ستونی که می خواهید داده هایتان را فیلتر کنید کلیک کنید.
7. از لیست کشویی در بالای ستون برای تعیین معیارهایی که داده ها باید بر اساس آن فیلتر شوند استفاده کنید.
8. در پانل پیمایش، روی پرس و جو دوبار کلیک کنید و نامی متناسب با فیلتر خود به آن بدهید. به عنوان مثال، می توانید پرس و جو را «بخش فروش» یا «دو سال اول» نامگذاری کنید.
9. روی File | کلیک کنید بستن و بارگذاری به. اکسل کادر محاوره ای Import Data را نمایش می دهد. (شکل 1 را ببینید.)
10. به دلخواه تنظیمات واردات را تغییر دهید. (برای بیشتر استفاده ها، پیش فرض های Table و New Worksheet باید خوب باشند.)
11. روی OK کلیک کنید. اکسل داده های فیلتر شده را از فایل CSV وارد می کند و آن را در یک کاربرگ جدید قرار می دهد. همچنین، پنجره Queries & Connections باید در سمت راست داده های شما قابل مشاهده باشد.
12. در قسمت Queries & Connections، روی پرس و جوی که به تازگی اجرا کرده اید راست کلیک کنید. اکسل یک منوی زمینه را نمایش می دهد.
13. در منوی زمینه، Duplicate را انتخاب کنید. اکسل یک کپی از پرس و جو را در قسمت Queries & Connections ایجاد می کند و آن عبارت تکراری را در ویرایشگر Power Query نمایش می دهد.
14. مراحل 6 تا 13 را برای هر یک از مجموعه داده های دیگری که می خواهید از فایل CSV استخراج کنید، تکرار کنید. (مطمئن شوید که معیارهای مجموعه داده شما به اندازه کافی جامع هستند که در نهایت همه رکوردها وارد شوند.)

کار با Power Query (همانطور که از نامش پیداست) قدرتمند است. با این حال، برای اطمینان از وارد کردن هر چیزی که نیاز دارید از فایل CSV منبع، کمی عادت خواهید کرد.