وارد کردن زیر مجموعه ای از رکوردها

Question

گوردون تعجب می کند که چگونه می تواند یک زیرمجموعه از یک فایل متنی را بسته به مقدار یک فیلد خاص به اکسل وارد کند. به عنوان مثال، او ممکن است بخواهد فقط رکوردهایی را وارد کند که حاوی "y" در ستون 5 هر رکورد در فایل متنی هستند.

راه های مختلفی وجود دارد که می توانید به این کار نزدیک شوید. یکی این است که می توانید به سادگی کل فایل متنی را وارد کنید، رکوردها را مرتب کنید و آن هایی را که نمی خواهید حذف کنید. اگر فقط نیاز به پردازش یک فایل دارید و کل فایل می تواند در یک کاربرگ جا شود، شاید این ساده ترین گزینه باشد.

روش دیگر استفاده از ماکرو است. (این موردی است که به نظر من سریع ترین و ساده ترین آن است، به ویژه اگر شما نیاز به وارد کردن یک نوع فایل دارید.) ماکرو می تواند فایل متنی را باز کند، هر خط را بخواند و سپس تعیین کند که آیا اطلاعات در آن خط باید به کاربرگ اضافه شود یا خیر. در اینجا یک مثال است که فایلی به نام "MyCSVFile.txt" را باز می کند و سپس داده ها را در یک کاربرگ جدید که از ردیف اول شروع می شود، قرار می دهد.

Sub ReadMyFile()
    Dim R As Integer
    Dim C As Integer
    Dim sDelim As String
    Dim sRaw As String
    Dim ReadArray() As String

    sDelim = ","      Set to vbTab if tab-delimited file

    Worksheets.Add
    Open "myCSVFile.txt" For Input As #1
    R = 1
    Do While Not EOF(1)
        Line Input #1, sRaw
        ReadArray() = Split(sRaw, sDelim, 20, vbTextCompare)
        If ReadArray(4) = "y" Then
            For C = 0 To UBound(ReadArray)
                Cells(R, C + 1).Value = ReadArray(C)
            Next C
            R = R + 1
        End If
    Loop
    Close #1
End Sub

برای استفاده از ماکرو، به سادگی نام فایل را تغییر دهید تا با فایلی که می خواهید پردازش کنید مطابقت داشته باشد. همچنین می خواهید متغیر sDelim را تغییر دهید تا مطمئن شوید که با هر چیزی که به عنوان جداکننده در رکوردهای شما استفاده می شود مطابقت دارد. همانطور که نوشته شد، فرض می کند که جداکننده یک کاما است (که در یک فایل CSV وجود دارد)، اما اگر واقعاً با یک فایل جداشده با تب کار می کنید، می توانید آن را به vbTab تغییر دهید. پس از تکمیل ماکرو، فقط آن دسته از رکوردهایی که دارای یک کاراکتر کوچک "y" هستند در کاربرگ جدید هستند.

روش دیگر استفاده از قابلیت Power Query اکسل است. این یک افزونه رایگان از مایکروسافت است که برای برخی از نسخه های اکسل 2010 و اکسل 2013 در دسترس است. می توانید در این مکان دانلود کنید (و ببینید کدام نسخه ها پشتیبانی می شوند):

http://www.microsoft.com/en-us/download/details.aspx?id=39379

اگر از Excel 2016 استفاده می کنید، Power Query در برنامه تعبیه شده است. اگر Power Query را در نسخه اکسل خود نصب یا در دسترس دارید و آن نسخه اکسل اکسل 2010 یا اکسل 2013 است، مراحل زیر را دنبال کنید:

1. تب Power Query روبان را نمایش دهید.
2. از فایل | کلیک کنید از CSV. اکسل کادر محاوره ای مرور مقادیر جدا شده با کاما را نمایش می دهد که بسیار شبیه یک کادر محاوره ای Open استاندارد است.
3. فایل CSV مورد نظر برای وارد کردن به اکسل را پیدا کرده و انتخاب کنید.
4. روی Open کلیک کنید. اکسل داده ها را در یک پنجره Power Query با دکمه های فیلتر موجود برای هر فیلد بارگیری می کند.

اگر از اکسل 2016 یا نسخه جدیدتر استفاده می کنید، مراحل کمی متفاوت است:

1. تب Data روبان را نمایش دهید.
2. ابزار New Query (Excel 2016) یا ابزار Get Data (نسخه های بعدی اکسل) را در گروه Get & Transform کلیک کنید. اکسل برخی از گزینه ها را نمایش می دهد.
3. از فایل | کلیک کنید از متن/CSV. اکسل کادر محاوره ای Import Data را نمایش می دهد که بسیار شبیه یک کادر محاوره ای Open استاندارد است.
4. فایل CSV مورد نظر برای وارد کردن به اکسل را پیدا کرده و انتخاب کنید.
5. روی Open کلیک کنید. اکسل داده ها را در یک پنجره Power Query با دکمه های فیلتر موجود برای هر فیلد بارگیری می کند.

در این مرحله - صرف نظر از نسخه اکسل که استفاده می کنید - می توانید از کنترل ها برای تعیین یک پرس و جو استفاده کنید (به این معنی که تعریفی را تنظیم کنید که کدام رکوردها باید وارد شوند). وقتی روی Close و Load کلیک می کنید، رکوردها از فایل بازیابی می شوند و پرس و جو برای استفاده در آینده ذخیره می شود.

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

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

شکل 2. کادر محاوره ای ایجاد منبع داده جدید.

شکل 3. کادر محاوره ای Add Criteria.

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

1. تب Data روبان را نمایش دهید.
2. بر روی ابزار Get Data در گروه Get & Transform Data کلیک کنید، سپس From Other Sources (در گروه Get External Data نسخه های قبلی اکسل) و سپس From Microsoft Query را انتخاب کنید. اکسل کادر محاوره ای Choose Data Source را نمایش می دهد. (شکل 1 را ببینید.)
3. گزینه New Data Source را انتخاب کرده و OK کنید. اکسل کادر محاوره ای Create New Data Source را نمایش می دهد. (شکل 2 را ببینید.)
4. یک نام برای منبع داده خود، مانند "CSV Files" ارائه کنید.
5. با استفاده از لیست کشویی مورد 2، Microsoft Text Driver را انتخاب کنید.
6. روی Connect کلیک کنید. اکسل کادر محاوره ای ODBC Text Setup را نمایش می دهد.
7. بلافاصله روی OK کلیک کنید تا کادر محاوره ای بسته شود.
8. روی OK کلیک کنید تا کادر محاوره ای Create New Data Source را ببندید. اکسل کادر محاوره ای Choose Data Source را به روزرسانی می کند تا نامی را که در مرحله 4 مشخص کرده اید شامل شود.
9. منبع داده ای را که ایجاد کرده اید انتخاب کنید و سپس روی OK کلیک کنید. اکسل هشداری مبنی بر وجود هیچ جدول داده ای در منبع نمایش می دهد. (بسیار خوب است، شما هیچ تعریفی نکرده اید.)
10. برای رد کردن اخطار روی OK کلیک کنید. اکسل کادر محاوره ای Query Wizard را نمایش می دهد.
11. از آنجایی که نمی توانید با کادر محاوره ای خالی Query Wizard کاری انجام دهید، روی Cancel کلیک کنید. اکسل هشداری را نشان می دهد که از شما می پرسد آیا می خواهید در مایکروسافت کوئری بمانید یا خیر.
12. روی Yes کلیک کنید. اکسل کادر محاوره ای افزودن جدول را نمایش می دهد.
13. با استفاده از کنترل های موجود در کادر محاوره ای، فایل CSV خود را پیدا کرده و انتخاب کنید.
14. روی دکمه Add کلیک کنید. به نظر می رسد اکسل هیچ کاری انجام نمی دهد، اما در واقع مرجع را به فایل CSV اضافه کرده است.
15. روی دکمه Close کلیک کنید تا کادر محاوره ای افزودن جدول حذف شود. فایل CSV شما در پنجره Microsoft Query نشان داده می شود.
16. با استفاده از لیست فیلدها برای فایل CSV، هر فیلدی را که می خواهید به کاربرگ وارد شود، به قسمت پایین پنجره Microsoft Query بکشید. (اگر همه فیلدها را می خواهید، فقط ستاره را به قسمت پایین پنجره بکشید.)
17. روی معیارها کلیک کنید اضافه کردن معیارها اکسل کادر محاوره ای Add Criteria را نمایش می دهد. (شکل 3 را ببینید.)
18. با استفاده از کنترل های موجود در کادر محاوره ای، مشخص کنید که می خواهید فیلد 5 (هر نامی که باشد) برابر با "y" باشد.
19. روی دکمه افزودن کلیک کنید تا در واقع معیارها را به پرس و جو اضافه کنید.
20. برای رد کردن کادر محاوره ای Add Criteria روی Close کلیک کنید.
21. روی File | کلیک کنید داده ها را به مایکروسافت اکسل برگردانید. اکسل کادر محاوره ای Import Data را نمایش می دهد. (شکل 4 را ببینید.)
22. تنظیمات را در کادر محاوره ای به دلخواه تغییر دهید تا نشان دهید چگونه می خواهید داده CSV به اکسل بازگردانده شود.
23. روی OK کلیک کنید.

(به شما گفتم که مراحل طولانی است.) اکنون می توانید با داده ها در اکسل کار کنید و در صورت تمایل، از ابزارهای موجود در برگه Design نوار برای بازخوانی داده ها از فایل CSV استفاده کنید.