انتقال اطلاعات اکسل های مختلف به SQL Server با پایتون

در یکی از شرکت هایی که برای آنها داشبورد مدیریتی می ساختم، با صورت مسئله جالبی روبرو شدم. این شرکت اصرار دارد که برای داشبوردهای خود (که با ابزار Microsoft Power BI) ساخته شده اند حتما از یک بانک اطلاعات جامع (Data warehouse) استفاده نماید.

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

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

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

import pandas as pd

import glob

خب ابتدا ماژول های مورد نیاز را وارد می کنیم. ما برای وارد کردن اکسل ها به پایتون از Pandas و برای پیدا کردن فایل ها در سیستم عامل از ماژول glob استفاده می کنیم.

path = r'F:\Report'

filenames = glob.glob(path + &quot\*.xlsx&quot)

print('File names:', filenames)

finalexcelsheet = pd.DataFrame()

خب ابتدا مسیر را معرفی کرده و سپس تمامی فایل با پسوند xlsx را در آن پوشه می خوانیم.

for file in filenames:

     df = pd.concat(pd.read_excel(

     file, sheet_name=None), ignore_index=True, sort=False)

     finalexcelsheet = finalexcelsheet.append(df, ignore_index=True)

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

print('Final Sheet:')

display(finalexcelsheet)

خب حالا می توانیم نگاهی به نتیجه کار بیندازیم. کدهای لازم برای انتقال این اطلاعات به sql server به شکل زیر می باشد. لازم به توضیح است که PreProcessing لازم و تا حد زیادی حیاتی است. لذا پیش از انتقال اطلاعات اینکار را انجام دهید (از جمله fillna)

import pyodbc

conn = pyodbc.connect('Driver={SQL Server};'

             'Server=ServerName;'

             'Database=DataBaseName;'

             'Trusted_Connection=yes;'

             'UID=sa;'

             'PWD=sa;')

cursor = conn.cursor()

cursor.fast_executemany = True

در بالا با دیتابیس SQL مرتبط شدیم. حالا نیاز است کوئری insert را بنویسیم و از cursor بخواهیم آن را اجرا کند. توجه کنید تعداد ستون های نوشته شده جلوی نام جدول در دستور Insert با تعداد علامت های وال در VALUES و تعداد مقادیر در data باید یکی باشد.

همچنین در پایان Cursor و ارتباط را می بندیم.

query = '''INSERT INTO TableName ([A],[B],[C])

VALUES (?,?,?)'''

data = [(row.X, row.Y, row.Z,) for row in finalexcelsheet.itertuples()]

cursor.executemany(query, data)

cursor.commit()

cursor.close()

conn.close()

خب دیتای ما به دیتابیس منتقل شد!

فایل تکمیل تر را می توانید در گیت هاب من مشاهده نمایید.