اهمیت موضوع
شاید برای شما هم پیش آمده باشد که بخواهید چندین فایل با فرمت txt را به فایل اکسل خود وارد یا import کنید. اگر تعداد فایل های شما کم باشد به راحتی می توانید از ریبون data و گزینه get external data استفاده کنید. (در این مطلب بخوانید.)
ولی در صورتی که تعداد فایل های شما زیاد باشد این فرایند زمان زیادی را از شما خواهد گرفت. مثلا فرض کنید 50 یا 100 فایل با فرمت txt یا csv دارید و میخواهید این اطلاعات را وارد یک فایل اکسل کنید. اگر از روش بالا استفاده کنید دو مشکل عمده برای شما به وجود خواهد آمد:
1- زمان بر بودن: فرض کنید ورود هر فایل به اکسل 1 دقیقه از زمان شما را بگیرد، برای ورود 50 فایل باید 50 دقیقه وقت بگذارید.
2- بالا بودن احتمال خطا: به دلیل تکراری بودن این فرایند و خستگی ذهن، احتمال وارد نکردن یک فایل یا وارد کردن چندین باره یک فایل امری بسیار محتمل است.
اما راهکار چیست؟
برای این مسئله می توانید از دو راهکار استفاده کنید:
1- استفاده از کدهای vba برای وارد کردن فایل های به اکسل
2- استفاده از افزونه powre query (در این مطلب بخوانید.)
ما در اینجا قصد داریم با استفاده از کدهای vba چندین فایل txt که در یک پوشه قرار گرفته اند، در کمتر از 1 دقیقه به اکسل وارد کنیم.
مراحل کار:
1- ابتدا صفحه کاری اکسل که می خواهید داده ها را در آن وارد کنید باز کنید.
2- با زدن کلید ترکیبی alt+F11 پنجره vba برای شما فعال خواهد شد.
3- از زبانه insert گزینه module را انتخاب کنید تا ماژول جدید به پروژه شما اضافه شود.
4- کد زیر را کپی کنید و در ماژول خود پیست کنید.
Sub Test() 'UpdatebyExtendoffice6/7/2016 Dim xWb As Workbook Dim xToBook As Workbook Dim xStrPath As String Dim xFileDialog As FileDialog Dim xFile As String Dim xFiles As New Collection Dim I As Long Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker) xFileDialog.AllowMultiSelect = False xFileDialog.Title = "Select a folder [Kutools for Excel]" If xFileDialog.Show = -1 Then xStrPath = xFileDialog.SelectedItems(1) End If If xStrPath = "" Then Exit Sub If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\" xFile = Dir(xStrPath & "*.txt") If xFile = "" Then MsgBox "No files found", vbInformation, "Kutools for Excel" Exit Sub End If Do While xFile <> "" xFiles.Add xFile, xFile xFile = Dir() Loop Set xToBook = ThisWorkbook If xFiles.Count > 0 Then For I = 1 To xFiles.Count Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I)) xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count) On Error Resume Next ActiveSheet.Name = xWb.Name On Error GoTo 0 xWb.Close False Next End If End Sub
5- با زدن کلید F5 کد را اجرا کنید.
6- از پنجره باز شده، پوشه مورد نظر که فایل ها با فرمت txt در آن قرار دارد انتخاب کنید. (مانند عکس زیر)
7- بر روی گزینه ok کلیک کنید تا همه فایل ها با فرمت txt که در پوشه مورد نظر است در فایل اکسل قرار بگیرد.
سلام ی سوال دارم من ی فایل اکسل دارم میخوام مطالب کپی شده بهش اضافه کنم اما نمیشه میره روی مطالب قبلی میشه بگین با چ روشی برم
سلام
باید دقیق تر توضیح بدید.
ی سوال خدمتتون داشتم
من فایل تکست که دارم هر ردیف اطلاعتی داره که با کاما درج شده از هم جدا شده،چه طور میشه به همین شکل دسته ای این کاما رو جوری تنظی کرد که در ستون های مختلف قرار بگیرند
سلام
راه اول: تب data گزینه from text رو انتخاب کنید و بعد مرحله به مرحله پیش برید و فایل داخل اکسل وارد کنید.
راه دوم: نرم افزار اکسل باز کنید و فایل txt بکشید داخلش.
ممنون از پاسخگویی شما،بنده تعداد زیادی شیت به همین شکل دارم(400 عدد)میخواستم به صورت دسته ای اینکار را انجام بدم،مثل این موضوع آموزشی که شما قرار دادید دسته ای تعداد زیادی فایل تکست را ایمپروت کرد من میخوام به صورت دسته ای هم اطلاعات ستون ها را تفکیک کنم(با سلکت کردن همه شیت ها امتحان کردم اما ریبون دیتا که جهت اینکار است غیر فعال هست)
اگر میخواید هر 400 فایل در شیت های جدا باشن، باید یک ماکرو ضبط کنید و با استفاده از قابلیت Text to column در تب Data ستون ها رو از هم تفکیک کنید و کد ماکرو ضبط شده رو به کد این قسمت اضافه کنید.
آموزش ضبط ماکرو
اگر میخواید هر 400 فایل در یک شیت اکسل تجمیع بشه می تونید از تب data گزینه get data و ابزار file –> from folder رو انتخاب کنید و همه فایل ها رو تجمیع کنید. از صفحه زیر آموزش قسمت پنجم رو ببنید:
آموزش رایگان پاور کوئری
خیلی ممنون از راهنمایی و لطفتون
با این روش هر فایل تکست رو دراکسل های جدا از هم قرار میده. مثلا 20 تا فایل اکسل باز میشه.
در اکسل های جدا قرار نمی ده. به ازای هر فایل یک شیت میسازه.
سلام. ممنون بابت راهنمایتون
شرمنده سوالی داشتم. ببخشید اینجا کامنت میزارم
بنده یه ستون تاریخ دارم. و یک ستون مقدار بارش
بنده قصددارم روز های تداوم بارش را استخراج کنم برای مثال:
rain3day __rain2day__rain1 day____Rain _______date______
19480329 ____0
19480330 ____0
19480331 ____0
19480401 ____0.3 _____1 __________
19480402 ____0
19480403 ___1.12________________1
19480404 ___1.11 _______________2
19480405 ___0
19480406 ___0.02 ________________________1
19480407 ___0.51 ________________________2
19480408 ___0.2 _________________________3
19480409 ___0
19480410 ___0.27_____1 __
میخواستم بدانم راهی دارد که تداوم بارش ها را بدین شکل در اکسل در ستون های مجزا استخراج نماییم؟
سلام
کار با ابزار پاور کوئری رو در این مطلب و فیلم های آموزشی و سایر سایت ها یاد بگیرید. خیلی از این کارها به سادگی قابل انجام هست.
فیلم و آموزش پاور کوئری در اکسل
سلام ممنون بابت این مطلب مفید
شرمنده سوالی داشتم.
بنده داده های تکست دارم که انتهای انها .dat هست در کدی که قرار دادید بنده به جای تکس فرمت دت راقرار داده ام،
xFile = Dir(xStrPath & “*.dat”)
داده ها فرا خوانده می شود. اما از آنجا که کل ستون ها در یک ستون ریخته می شود راهکاری دارید که هر ستون داده بر اساس فاصله ایی که در داده های دیت وجود دارد در ستون های مجزا قرار گیرد؟
سپاس از راهنمایی که خواهید نمود
سلام از پاور کوئری استفاده کنید.
در آموزش های پاور کوئری آموزش قسمت 5م ببینید.
فیلم و آموزش پاور کوئری در اکسل