تغییر جداکننده واردات متن پیش فرض

Question

اینورتر خورشیدی Bills یک فایل روزانه را ذخیره می کند که در آن فیلدها با نقطه ویرگول (;) مشخص می شوند. وقتی با استفاده از Text Import Wizard آن را به اکسل وارد می کند، جداکننده پیش فرض یک کاراکتر تب است. بیل باید هر بار که وارد می کند تیک Tab را بردارید و Semicolon را بررسی کند، بنابراین او نمی پرسد آیا راهی برای تغییر کاراکتر جداکننده پیش فرض وجود دارد یا خیر.

راه های مختلفی وجود دارد که می توانید به این موضوع برخورد کنید، همانطور که در بخش های بعدی توضیح داده شده است.

تغییر تنظیمات منطقه ای شما

اگر می خواهید اکسل همیشه نقطه ویرگول را به عنوان جداکننده تشخیص دهد، می توانید در تنظیمات منطقه ای که ویندوز استفاده می کند تغییری ایجاد کنید. این مراحل را دنبال کنید:

شکل 1. کادر محاوره ای Customize Format.

1. کلید Windows را فشار دهید و بلافاصله شروع به تایپ کردن "con" (بدون علامت نقل قول) کنید. ویندوز باید به شما نشان دهد که بهترین تطابق برای تایپ شما، برنامه کنترل پنل است.
2. Enter را فشار دهید . ویندوز کنترل پنل را باز می کند.
3. با استفاده از لیست کشویی View By (گوشه سمت راست بالای کنترل پنل)، مطمئن شوید که بر اساس دسته بندی مشاهده می کنید.
4. روی گزینه «تغییر تاریخ، زمان یا فرمت های اعداد»، درست در زیر عنوان ساعت و منطقه کلیک کنید. ویندوز کادر محاوره ای Region را نمایش می دهد.
5. روی گزینه Additional Settings کلیک کنید. ویندوز کادر محاوره ای Customize Format را نمایش می دهد. (شکل 1 را ببینید.)
6. تنظیمات List Separator را به نقطه ویرگول تغییر دهید.
7. برای بستن کادر محاوره ای Customize Format روی OK کلیک کنید.
8. روی OK کلیک کنید تا کادر گفتگوی Region بسته شود.
9. کنترل پنل را ببندید

در این مرحله می توانید اکسل را مجددا راه اندازی کنید و از Text Import Wizard برای وارد کردن فایل خود استفاده کنید. اکسل باید به طور پیش فرض از نقطه ویرگول به عنوان جداکننده استفاده کند. و به اندازه کافی جالب، اگر یک فایل CSV را باز کنید که از کاما به عنوان جداکننده استفاده می کند، جادوگر وارد کردن متن به طور پیش فرض از کاما استفاده می کند.

به یاد داشته باشید که هر تغییری که در تنظیمات منطقه ای خود ایجاد می کنید می تواند علاوه بر اکسل بر عملکرد سایر برنامه ها نیز تأثیر بگذارد. اگر تغییری مانند آنچه در بالا ایجاد کردید، می خواهید آن برنامه ها را بررسی کنید تا مطمئن شوید هیچ اثر نامطلوبی از این تغییر وجود ندارد.

تغییر تنظیمات متن به ستون

توجه داشته باشید که عنوان این رویکرد نشان می دهد که شما در حال تغییر تنظیمات "Text to Column" هستید و Bill به طور خاص در مورد "Text Import Wizard" سوال کرده است. علیرغم آنچه در ابتدا ممکن است به نظر می رسد ترکیبی از دو ابزار باشد، مهم است که بدانیم جادوگر واردات متن به ابزار متن به ستون به عنوان بخشی از فرآیند واردات متکی است. بنابراین، اگر بتوانید تنظیمات را در ابزار Text to Column تغییر دهید، بر نحوه کار Text Import Wizard نیز تأثیر می گذارید.

مشکل این است که تنظیمات Text to Column از یک جلسه اکسل به جلسه دیگر ثابت نیستند. با این حال، آنها در همان جلسه ثابت هستند. بنابراین، می توانید یک ماکرو کوچک بنویسید که تنظیمات را تغییر دهد تا هر زمان که شروع به استفاده از اکسل می کنید، آماده باشید. این یک مثال است:

Private Sub Workbook_Open()
    Worksheets.Add
    Range("A1").Value = "TEST"

    Application.ScreenUpdating = False
    Range("A1").TextToColumns Destination:=Range("A1"), _
      DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
      Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,1), _
      TrailingMinusNumbers:=True

    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

هنگامی که در ماژول ThisWorkbook یک ورک بوک ذخیره می شود، این کنترل کننده رویداد هر بار که ورک بوک باز می شود فعال می شود. یک کاربرگ جدید اضافه می کند، مقداری متن ساختگی را در سلول A1 آن کاربرگ قرار می دهد و سپس یک عملیات Text to Columns را روی آن متن ساختگی انجام می دهد. سپس کاربرگ حذف می شود. از آنجایی که روش TextToColumns نقطه ویرگول را به عنوان جداکننده تنظیم می کند، که برای کل جلسه اکسل به خاطر سپرده می شود (مگر اینکه بعداً تغییر داده شود). این بدان معنی است که سپس می توانید از Text Import Wizard برای گرفتن اطلاعات خود استفاده کنید و جداکننده مورد نظر از قبل برای شما تنظیم شده است.

نوشتن روال واردات خود

البته، شما می توانید Text Import Wizard را به طور کامل دور بزنید و از ماکرو خود برای انجام وارد کردن استفاده کنید. اگر (1) می دانید ساختار داده های دریافتی چگونه است و (2) نیاز دارید که اغلب وارد کردن را انجام دهید، این یک رویکرد خوب است. نمونه ای از ماکرو که می تواند برای انجام وارد کردن استفاده شود:

Sub ImportDailyFile()
    Dim R As Range
    Dim S As String

    Const F = "C:UsersYour NameDocumentsDailyFile.txt"

    Worksheets.Add
    Open F For Input As #1
    Set R = ActiveCell
    Do Until EOF(1)
        Line Input #1, S
        R.Value = S
        Set R = R.Offset(1, 0)
    Loop
    Close #1

    Set R = R.Offset(-1, 0)
    Set R = Range(ActiveCell.Address, R.Address)
    R.TextToColumns Semicolon:=True
End Sub

برای استفاده از ماکرو، باید مقدار ثابت را تغییر دهید تا مسیر کامل فایل داده شما منعکس شود. ماکرو یک کاربرگ جدید اضافه می کند که داده های شما در آن قرار می گیرد. سپس فایل داده را باز می کند، هر خط را می خواند و آن خطوط را در ستون A صفحه کاری جدید قرار می دهد. هنگامی که تمام داده ها خوانده و ذخیره شدند، ماکرو سپس تمام سلول های ستون A را انتخاب می کند و با استفاده از نقطه ویرگول به عنوان جداکننده، عملیات Text To Columns را روی آن داده ها انجام می دهد.

استفاده از Power Query

بسیاری از مردم دوست دارند از Power Query برای وارد کردن داده ها از فایل های خارجی استفاده کنند. اگر در این اردوگاه رو به رشد قرار گرفتید، می توانید از Power Query برای تعریف نحوه برخورد اکسل با اطلاعات وارد شده استفاده کنید. در اینجا مراحل کلی وجود دارد:

1. تب Data روبان را نمایش دهید.
2. روی ابزار Get Data کلیک کنید. اکسل یک منوی کشویی را نمایش می دهد.
3. Launch Power Query Editor را انتخاب کنید. اکسل ویرایشگر Power Query را نمایش می دهد.
4. در ویرایشگر Power Query، مطمئن شوید که تب Home نوار نمایش داده می شود. (به طور پیش فرض باید نمایش داده شود.)
5. ابزار New Source را در گروه New Query در سمت راست نوار کلیک کنید. اکسل برخی از گزینه ها را برای انواع منابعی که می توانید استفاده کنید نمایش می دهد.
6. فایل | را انتخاب کنید متن/CSV. اکسل کادر محاوره ای Import Data را نمایش می دهد. (به نظر می رسد بسیار شبیه به یک جعبه گفتگوی Open استاندارد است.)
7. با استفاده از کنترل های موجود در کادر محاوره ای، فایلی را که می خواهید به عنوان منبع داده استفاده کنید، پیدا کرده و انتخاب کنید.
8. روی دکمه Open کلیک کنید. اکسل لحظه ای فکر می کند و پنجره سنتی Text Import Wizard را نمایش می دهد.
9. برای حذف پنجره روی OK کلیک کنید. اکسل داده های شما را در ویرایشگر Power Query نمایش می دهد.
10. تب Transform روبان را نمایش دهید.
11. روی ابزار Use First Row as Headers کلیک کنید و سپس Use First Row as Headers را انتخاب کنید. (بله، دو بار عبارت یکسان است.)
12. در صورت لزوم، ستون ها را انتخاب کنید و از گزینه های زیر ابزار Text Column برای تغییر قالب ستون های داده تان یا ایجاد تغییرات دیگری در داده های وارد شده استفاده کنید.
13. در تب Home نوار، روی ابزار Close & Load کلیک کنید. اکسل داده های وارد شده را در یک جدول داده نمایش می دهد.

اکنون می توانید با داده ها به دلخواه کار کنید.