تجمیع چند فایل با پاور کوئری | گزینه from folder

اگر نوع کارهای شما هم به نحوی باشد که به صورت دوره ای، فایل های مختلفی( در قالب های مختلف txt, csv, xlsx و …) را دریافت میکنید و می بایست این فایل ها را به صورت منظم وارد اکسل کنید؛ احتمالا میدانید که انجام این کار تکراری چقدر آزار دهنده است و تا چه اندازه وقت شما را میگیرد. اما در پاور کوئری امکانی فراهم شده است که تمام این فایل ها را در یک پوشه ذخیره سازی کنید و برای به روز رسانی فقط دکمه refresh را فشار دهید.

قبلا و در زمانی که امکان استفاده از پاور کوئری وجود نداشت تنها راهی که میتوانستید این کار را انجام دهید استفاده از VBA بود، در این شیوه شما می بایست یک ماکرو مینوشتید که عملیات فراخوانی را برای شما انجام میداد. اما از آنجایی که اکثریت کاربران اکسل با ماکرونویسی آشنایی چندانی ندارند، پاور کوئری تا حدود زیادی باعث تسهیل کار شده است. در ادامه روش انجام کار برای تجمیع فایل با پاور کوئری مشاهده میکنید.

فیلم آموزش تجمیع فایل های مختلف در پاور کوئری

در پایان پیشنهاد میکنیم فیلم آموزشی تهیه شده توسط تیم Skillpro در زمینه نحوه استفاده از گزینه From folder برای فراخوانی و تجمیع چندین فایل مختلف در پاور کوئری را مشاهده کنید. شما با مشاهده این کلیپ های آموزش علاوه بر یادگیری موضوع مربوط به صورت تصویری، نکات بسیار زیاد دیگری را نیز فراخواهید گرفت.

فرض کنید به صورت ماهانه، یک فایل text را دریافت میکنید که خلاصه اطلاعات مربوط به آن ماه را به شکل زیر برای شما نشان میدهد:

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

در تب پاور کوئری (در نسخه های جدیدتر اکسل، پاور کوئری در تب data و گروه Get and transofrm قرار گرفته است)، گزینه From file و سپس From folder را انتخاب کنید. با انجام این کار، یک پنجره به نام Foder باز خواهد شد که میتوانید آدرس پوشه مورد نظر را در آن انتخاب کرده یا paste کنید.

در اکسل 2016 و 2019 از گزینه زیر استفاده کنید:

در اکسل 2013 و 2010 در صورت نصب پاور کوئری از گزینه زیر استفاده کنید:

با وارد کردن آدرس، محتویات پوشه مورد نظر در یک پنجره جدید به شما نمایش داده میشود و سه گزینه برای انتخاب در اختیار شما قرار داده میشود:

گزینه Edit را انتخاب کنید؛ با انتخاب این گزینه پنجره Query editor باز خواهد شد( در مورد دیگر گزینه ها در فیلم آموزشی پایین همین صفحه توضیح داده شده است). 

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

با فشار دادن این دکمه محتویات فایل ها به صورت زیر برای شما نشان داده میشود. 

حال، شروع به اصلاح و ادغام فایل ها در پاورکوئری میکنیم. در طول این فرایند، تا حد زیادی از دکمه دستورات پر کاربرد پاور کوئری که قبلا در مورد آن صحبت شد استفاده میکنیم.

ابتدا، می بایست شش ردیف ابتدایی را حذف کنیم. بنابراین، با استفاده از لیست کشویی، دستور Remove Top Rows را انتخاب میکنیم. 

با انتخاب این دستور، یک پنجره به همین نام باز میشود، و شما می بایست تعداد ردیف هایی را که میخواهید حذف کنید، در آن وارد کنید و OK را بزنید. 

سپس، دستور ساده use first Row as column را انتخاب کنید.

تا اینجای کار شما شش سطر اول فایل تکست اول را حذف کرده اید، با اینحال، می بایست این عملیات را بر روی سایر فایل های اکسل نیز انجام دهید.  برای انجام این کار از فیلتر ستون  NAme استفاده میکنیم. در ابتدا فیلتر تعداد خاصی از آیتم ها را نمایش میدهد و شما می بایست شبیه به تصویر زیر ار دستور Load more استفاده کنید.

حال تیک گزینه  Blanks را حذف میکنیم:

سپس ردیف های شامل نام را حذف میکنیم( این ردیف ها، ردیف های عنوان دیگر فایل های txt هستند)

حال، ما داده های مورد نیاز خود را در اختیار داریم و می بایست فقط برای اکسل مشخص کنیم که این اطلاعات چه نوع داده هایی هستند. بنابراین نوع داده های ستون ها را مشخص میکنیم. برای انجام این کار، هرکدام از ستون ها را انتخاب میکنیم و در تب home پاور کوئری، شبیه به تصویر زیر نوع مناسب داده ها را انتخاب میکنیم.

حال، فقط می بایست تغییرات جزیی بر روی داده ها ایجاد کرد. برای مثال، در ستون City، یک علامت سوال، جایگزین علامت اپاستروف(‘) شده است. برای تصحیح این تغییر، از دستور Transform/Replace Values استفاده میکنیم. 

با انتخاب این دستور پنجره ای شبیه به پنجره Find/replace در اکسل باز خواهد شد که کار آنها بسیار مشابه هم است. 

نتیجه کار به شکل زیر خواهد شد.

حال می بایست از دستور Close&load استفاده کرد. 

اما کاری که تاکنون انجام شد، دقیقا همان کاری بود که همیشه به محض دریافت فایل های تکست انجام میشد. احتمالا شما سوال خواهید کرد که ” تفاوت این روش با روش های مرسوم چیست؟ ” و پاور کوئری چه مزیتی را برای ما به ارمغان آورده است.

تاکنون ما چهار فایل txt را به صورت همزمان فراخوانی کرده ایم و 4000 ردیف داده در اختیار داریم.

در واقع پاور کوئری در اینجا مزیت فوق العاده خود را به شما نشان خواهد داد. فرض کنید در ماه جدید یک فایل تکست جدید برای شما ارسال شده است. اینبار، فقط کافیست فایل جدید را در پوشه مربوطه و در کنار سایر فایل های قبل کپی کنید و در در اکسل بر روی داده ها راست کلیک کنید و  گزینه refresh را انتخاب کنید.

با انجام اینکار، 1000 ردیف جدید داده که در فایل جدید قرار داشتند، به داده های شما اضافه میشوند.

پس پاور کوئری تمام کارهایی را که تاکنون به صورت دستی انجام میدادید، به صورت اتواماتیک و تنها با یک کلیک برای شما انجام میدهد.

اما این تمام ماجرا نیست. فرض کنید فایل تکستی را در این ماه دریافت میکنید که به لحاظ شکلی تفاوت هایی را با سایز فایل ارسال شده در گدشته دارد. در این موارد میتوانید،بر روی کوئری در سمت راست، راست کلیک کنید و گزینه edit را انتخاب کنید. پنجره query editor باز خواهد شد و شما میتوانید هرکدام از گام های تعریف شده در قبل را اصلاح یا حذف کنید.

اما اگر فایلی را دریافت کردید که به کلی با فایل های قبل تفاوت داشت، می بایست یک کوئری جدید شبیه به مراحل گفته شده در بالا ایجاد کنید با استفاده از دستور Apeend داده ها را به هم ملحق کنید.

[/vc_column_text][/vc_column][/vc_row]