چگونه داده ها را از یک فایل CSV در MySQL وارد کنیم؟

فایل CSV مخفف (comma-separated values) یک فایل متنی با کاما است که اطلاعات را از هم جدا می کند. این فایل ها بیشتر در برنامه اکسل و پایگاه داده یافت می شوند و در حال حاضر عمدتاً برای مجموعه داده ها در علم داده و یادگیری ماشین استفاده می شوند. این فایل ها به کسب و کارها در اکسپورت مقادیر زیادی داده کمک می کنند. با این حال، ما نمی توانیم جستجوهای SQL را روی چنین داده های CSV اجرا کنیم، بنابراین باید آن را به جداول ساختاریافته تبدیل کنیم.

روش های زیادی برای تبدیل داده های CSV به یک جدول ساختاریافته وجود دارد. ما به سه مورد از محبوب‌ترین روش‌ها برای تبدیل فایل‌های csv به فرمت SQL خواهیم پرداخت. این کار زمانی پیچیده تر می شود که داده های جدا شده با کاما که برای این کار استفاده می کنیم حاوی یک timestamp باشد.

در این مقاله، سه روش مختلف برای وارد کردن فایل‌های csv. به جداول MySQL همراه با ویژگی timestamp را خواهیم دید.

وارد کردن فایل‌های CSV. به جداول MYSQL:

این را می توان به روش های مختلفی انجام داد. سه روشی که در مورد آنها بحث خواهیم کرد از روش های مختلفی برای وار کردن فایل های .csv در جداول MySQL استفاده می کنند. روش های زیر را بررسی خواهیم کرد:

1. با استفاده از خط فرمان
2. با استفاده از PhpMyAdmin
3. استفاده از MySQL Workbench

با استفاده از خط فرمان:

مرحله 1: پنجره ترمینال را باز کنید و با استفاده از رمز عبور وارد MySQL Client شوید. به دستور زیر مراجعه کنید:

mysql -u root -p

مرحله 2: یک پایگاه داده ایجاد کنید و سپس یک جدول در داخل آن پایگاه داده ایجاد کنید. داده های فایل CSV داده های ورودی این جدول خواهد بود:

#To create a database
CREATE DATABASE database_name;

#To use that database
use database_name;

#To create a table
CREATE TABLE table_name(
id INTEGER,
col_1 VARCHAR(100),
col_2 INTEGER,
col_3 DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);

یک پایگاه داده و یک جدول به نام table_name در داخل آن ایجاد می کنیم. جدول شامل ویژگی های مختلفی مانند id، col_1، col_2 و col_3 است. ویژگی col_3 از نوع DATETIME است و هم داده و هم زمان را در قالب YYYY-MM-DD hh:mm:ss دارد. علاوه بر ویژگی‌های عدد صحیح و varchar، به نحوه وارد کردن ویژگی نوع DateTime از یک فایل CSV به جدول MySQL خواهیم پرداخت.

مرحله 3: اکنون باید متغیر MySQL “secure_file_priv” را بررسی کنیم . به طور پیش فرض، سرور MySQL با گزینه -secure-file-priv شروع می شود. هنگام استفاده از دستور LOAD DATA INFILE ، دایرکتوری را مشخص می کند که فایل های داده از آن می توانند در یک پایگاه داده انتخاب شده بارگذاری شوند. برای مشاهده دایرکتوری پیکربندی شده از دستور زیر استفاده کنید:

SHOW VARIABLES LIKE 'secure_file_priv';

خروجی:

متغیر “secure_file_priv” روی یک فهرست فایل پیکربندی شده توسط سرور MySQL تنظیم می شود. ما اکنون دو گزینه داریم تا دستور LOAD DATA INFILE به درستی کار کند:

1. فایل csv. ورودی را به ساختار پوشه مشخص شده منتقل کنید.
2. تغییر ساختار پوشه پیکربندی شده به محلی که فایل csv قرار دارد.

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

رویکرد 1:

در این روش، ما باید فایل CSV ورودی را در ساختار پوشه مشخص شده قرار دهیم، و پس از آن قادر خواهیم بود به فایلی که قرار است در پایگاه داده بارگذاری شود، دسترسی پیدا کنیم. هنگامی که این کار را انجام دادیم، آماده هستیم تا دستور وارد کردن فایل‌های CSV به پایگاه داده را اجرا کنیم. به دستور زیر مراجعه کنید:

LOAD DATA INFILE '{folder_structure}/{csv_file_name}'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

1. LOAD DATA INFILE – محل فایل CSV ورودی را مشخص می کند.
2. INTO TABLE – جدولی را که قرار است داده ها در آن پر شوند را مشخص می کند.
3. FIELDS TERMINATED BY – جداکننده ای را مشخص می کند که از طریق آن مقادیر در فایل از هم جدا می شوند.
4. ENCLOSED BY – نمادی را مشخص می کند که مقادیر موجود در فایل CSV درون این نماد قرار گرفته اند
5. LINES TERMINATED BY  – کد مربوط به انتهای خط مشخص می کند.
6. IGNORE 1 ROWS – تعداد خطوطی که باید از یک فایل CSV ورودی نادیده گرفته شود که ممکن است حاوی نام ستون و غیره باشد را مشخص می کند.

خروجی:

رویکرد 2: 

برای ویندوز:

مرحله 1: ساختار پوشه موجود در my.ini باید تغییر کند. به This PC -> C Drive -> ProgramData -> MySQL -> MySQL Server * -> my.ini بروید [پوشه ProgramData پنهان است. برای دیدن پوشه، به View در نوار منوی بالا بروید و موارد پنهان را فعال کنید.]

مرحله 2: فایل را باز کنید و “secure-file-priv” را جستجو کنید. چیزی شبیه به این خواهد شد.

# Secure File  Priv.
secure-file-priv=”C:/ProgramData/MySQL
/MySQL Server 5.7/Uploads”

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

مرحله 4: اکنون، “Ctrl + R” را بزنید -> تایپ کنید “services.msc” و سپس “Enter” را فشار دهید. با این کار تب Services باز می شود.

مرحله 5: MySQL57 را جستجو کنید [شماره 57 ممکن است در رایانه شخصی شما متفاوت باشد]. روی آن راست کلیک کرده و سپس روی دکمه راه اندازی مجدد کلیک کنید.

مرحله 6: وارد سرویس گیرنده MySQL Command Line شوید و دستور زیر را اجرا کنید:

LOAD DATA LOCAL INFILE '{file_location}'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

با پیروی از مراحل ذکر شده در بالا، می‌توانیم یک فایل CSV را به جدول MySQL در سیستم‌عامل ویندوز وارد کنیم بدون اینکه با هیچ خطایی مواجه شویم.

برای لینوکس:

مرحله 1: باید ساختار پوشه جدید مورد نظر خود را در فایل my.cnf اضافه کنیم. فایل در پوشه /etc/mysql/ قرار دارد. فایل my.cnf را با استفاده از ویرایشگر انتخابی خود باز کنید و خطوط زیر را به فایل اضافه کنید:

[mysqld]
secure-file-priv={new_folder_structure}

مرحله 2: پس از انجام تغییرات، فایل را ذخیره کرده و سرویس MySQL را مجددا راه اندازی کنید. برای آن به دستور زیر مراجعه کنید:

sudo systemctl restart mysql

مرحله 3: دوباره با استفاده از دستور زیر وارد کلاینت MySQL شوید:

mysql -u root -p --local-infile

مرحله 4: پس از ورود به سیستم، دستور زیر را اجرا کنید:

set global local_infile = 1;
SHOW VARIABLES LIKE 'local_infile';

خروجی:

مرحله 5: اکنون، قرار است داده های فایل CSV را به جدول MySQL وارد کنیم. به دستور زیر مراجعه کنید:

LOAD DATA LOCAL INFILE '{file_location}'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

خروجی:

با پیروی از مراحل ذکر شده در بالا، می‌توانیم یک فایل CSV را به جدول MySQL در سیستم‌عامل لینوکس وارد کنیم بدون اینکه با هیچ خطایی مواجه شویم.

از دستور زیر می توان برای تعیین اینکه آیا ویژگی dob با نوع timestamp به درستی وارد شده است یا خیر استفاده می شود. به این ترتیب، ما می توانیم اطمینان حاصل کنیم که ویژگی در جدول با نوع داده DateTime واقعی MySQL مطابقت دارد:

SELECT * FROM table_name WHERE col_3 = TIMESTAMP('{YYYY-MM-DD HH:MM:SS}');

مرحله اختیاری: تبدیل داده ها در حین import

وقتی می‌خواهید داده‌ها را از یک فایل در جدول MySQL بارگیری کنید، ممکن است متوجه شوید که قالب داده‌های دریافتی دقیقاً با جدول هدف مطابقت ندارد. به عنوان مثال، قالب col_3 timestamp را در موارد در نظر بگیرید. ممکن است قالب جدول هدف “yyyy/mm/dd %H:%i:%s.%f” و فرمت فایل csv “dd/mm/yyyy%H:%i:%s.%f” باشد. بند SET در عبارت LOAD DATA INFILE می تواند برای تبدیل فرمت تاریخ فایل CSV به فرمت مورد استفاده جدول مقصد در MySQL استفاده شود. در حین انجام عملیات بارگذاری داده ها از فایلی به جدولی در MySQL، می توانید از بند SET در پایان به همراه تابع str_to_date() استفاده کنید. به دستور زیر مراجعه کنید:

LOAD DATA INFILE '{file_location}'
INTO TABLE table_name
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS 
(id, col_1, col_2, @col_3) 
SET col_3 = timestamp(str_to_date(@col_3,'%d-%m-%Y %H:%i:%s.%f'));

استفاده از PhpMyAdmin:

phpMyAdmin یک ابزار نرم افزاری رایگان مبتنی بر PHP است که برای مدیریت MySQL از طریق اینترنت طراحی شده است. phpMyAdmin می تواند طیف گسترده ای از عملیات MySQL را انجام دهد. رابط کاربری را می توان برای انجام عملیات های پرکاربرد (مانند مدیریت پایگاه های داده، جداول، ستون ها، روابط، فهرست ها، کاربران و مجوزها) استفاده کرد، البته در حالی که همچنان می توانید هر دستور SQL را مستقیماً اجرا کنید.

بیایید ببینیم چگونه می توانیم از PhpMyAdmin برای وارد کردن یک فایل CSV به پایگاه داده MySQL استفاده کنیم.

مرحله 1: هنگامی که PhpMyAdmin را راه اندازی می کنید، پایگاه داده ها و جداول مختلفی را در سمت چپ رابط مشاهده خواهید کرد. جدولی ایجاد کنید که می خواهید داده های فایل CSV را در آن وارد کنید. ستون ها و نوع داده ها باید با داده های مربوطه در فایل CSV مطابقت داشته باشند.

مرحله 2: جدول را انتخاب کنید و سپس روی دکمه import از نوار منوی سمت بالا کلیک کنید.

مرحله 3: روی دکمه “Choose file” کلیک کنید و به حافظه محلی خود بروید تا فایل CSV ورودی را پیدا کنید. در همین صفحه می توانید تعداد خطوطی که باید در فایل پرش کنید را نیز مشخص کنید.

مرحله 4: صفحه را به پایین اسکرول کنید تا فرمت فایل را انتخاب کنید که در مورد ما csv است. مشابه رویکرد خط فرمان، ما چندین گزینه خاص فرمت را در اینجا داریم. وقتی کارمان تمام شد، روی دکمه «Go» کلیک کنید.  

مرحله 5: فایل CSV به جدول MySQL وارد می شود. هنگامی که import با موفقیت انجام شد، پیام های زیر را مشاهده می کنیم.

مرحله 6: اکنون می توانیم به جدول برگردیم و داده های وارد شده از فایل CSV را بررسی کنیم. 

استفاده از MySQL Workbench:

MySQL Workbench یک ابزار طراحی پایگاه داده بصری است که توسعه SQL، مدیریت، طراحی پایگاه داده، ایجاد و نگهداری برای سیستم پایگاه داده MySQL را در یک محیط توسعه یکپارچه ترکیب می کند.

بیایید ببینیم چگونه می توانیم از MySQL Workbench برای وارد کردن یک فایل CSV به پایگاه داده MySQL استفاده کنیم.

مرحله 1: هنگامی که MySQL Workbench را راه اندازی می کنید، پایگاه داده ها و جداول مختلفی را در سمت چپ رابط مشاهده خواهید کرد. جدولی ایجاد کنید که می خواهید داده های فایل CSV را در آن وارد کنید. ستون ها و نوع داده ها باید با داده های مربوطه در فایل CSV مطابقت داشته باشند.

مرحله 2: روی جدول کلیک راست کرده و گزینه “Table Data Import Wizard” را انتخاب کنید. برای انتخاب فایل CSV ورودی، فضای ذخیره‌سازی محلی خود را مرور کنید.

مرحله 3: encoding فایل را انتخاب کنید و ستون های مبدا و مقصد را برای پیکربندی تنظیمات import مطابقت دهید. وقتی کارتان تمام شد، روی دکمه Next کلیک کنید.

مرحله 4: داده های فایل CSV ورودی به جدول MySQL وارد می شود. سپس می‌توانیم داده‌های وارد شده را در رابط کاربری MySQL بررسی کنیم.

نتیجه:

این مقاله سه روش را برای وارد کردن یک فایل CSV به MySQL نشان داد. اکنون می دانید که چگونه با استفاده از خط فرمان، phpMyAdmin و MySQL Workbench، فایل های CSV را به MySQL وارد کنید.