How to Convert Rocket LMS Data to CSV

Follow these steps to extract course, chapter, and lesson data from the rocket-lms.sql database dump and convert it into a CSV format for importing into learning.gfavip.com.

1 Identify Relevant Tables

The key tables in the SQL dump containing course and lesson data are:

• webinars: Course metadata (ID, creator, teacher, category, price, status).
• webinar_translations: Localized course titles and descriptions.
• webinar_chapters: Chapters within a course (chapter ID, webinar ID, order).
• webinar_chapter_translations: Localized chapter titles.
• webinar_chapter_items: Links lessons to chapters (lesson type, order, file/text lesson ID).
• text_lessons and text_lesson_translations: Text-based lesson content.
• files: File-based lesson details (e.g., file paths).

2 Define CSV Structure

The CSV should include course, chapter, and lesson details. A sample structure:

CourseID,CourseTitle,CourseDescription,CategoryID,Price,Status,ChapterID,ChapterTitle,ChapterOrder,LessonID,LessonTitle,LessonType,LessonContent,LessonFilePath,LessonOrder
2023,"Cross Border Summit 2024: Planting Seeds, Building Streams","Step into the world of cross-border insights...",612,0,"active",42,"Day 1 - Planting Seeds: Insights and Strategies",1,406,"Introduction to Cross Border Summit","text_lesson","Welcome to the summit...",NULL,1

3 Extract Data

Use a database tool or script to extract data.

Option 1: Database Tool

1. Import SQL Dump: Use phpMyAdmin, MySQL Workbench, or Adminer to import rocket-lms.sql.
2. Query the Data: Run this SQL query to join tables:

SELECT 
    w.id AS CourseID,
    wt.title AS CourseTitle,
    wt.description AS CourseDescription,
    w.category_id AS CategoryID,
    w.price AS Price,
    w.status AS Status,
    wc.id AS ChapterID,
    wct.title AS ChapterTitle,
    wc.order AS ChapterOrder,
    wci.item_id AS LessonID,
    COALESCE(tlt.title, f.title) AS LessonTitle,
    wci.item_type AS LessonType,
    tlt.description AS LessonContent,
    f.file AS LessonFilePath,
    wci.order AS LessonOrder
FROM webinars w
LEFT JOIN webinar_translations wt ON w.id = wt.webinar_id AND wt.locale = 'en'
LEFT JOIN webinar_chapters wc ON w.id = wc.webinar_id
LEFT JOIN webinar_chapter_translations wct ON wc.id = wct.webinar_chapter_id AND wct.locale = 'en'
LEFT JOIN webinar_chapter_items wci ON wc.id = wci.chapter_id
LEFT JOIN text_lessons tl ON wci.item_id = tl.id AND wci.item_type = 'text_lesson'
LEFT JOIN text_lesson_translations tlt ON tl.id = tlt.text_lesson_id AND tlt.locale = 'en'
LEFT JOIN files f ON wci.item_id = f.id AND wci.item_type = 'file'
WHERE w.status = 'active'
ORDER BY w.id, wc.order, wci.order;

3. Export to CSV: Use the tool’s export feature to save as CSV.

Option 2: Python Script

1. Set Up Environment: Install Python and libraries:

pip install mysql-connector-python pandas

2. Run Script: Use this Python script to export data:

import mysql.connector
import pandas as pd

# Connect to MySQL database
conn = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Define the SQL query
query = """
SELECT 
    w.id AS CourseID,
    wt.title AS CourseTitle,
    wt.description AS CourseDescription,
    w.category_id AS CategoryID,
    w.price AS Price,
    w.status AS Status,
    wc.id AS ChapterID,
    wct.title AS ChapterTitle,
    wc.order AS ChapterOrder,
    wci.item_id AS LessonID,
    COALESCE(tlt.title, f.title) AS LessonTitle,
    wci.item_type AS LessonType,
    tlt.description AS LessonContent,
    f.file AS LessonFilePath,
    wci.order AS LessonOrder
FROM webinars w
LEFT JOIN webinar_translations wt ON w.id = wt.webinar_id AND wt.locale = 'en'
LEFT JOIN webinar_chapters wc ON w.id = wc.webinar_id
LEFT JOIN webinar_chapter_translations wct ON wc.id = wct.webinar_chapter_id AND wct.locale = 'en'
LEFT JOIN webinar_chapter_items wci ON wc.id = wci.chapter_id
LEFT JOIN text_lessons tl ON wci.item_id = tl.id AND wci.item_type = 'text_lesson'
LEFT JOIN text_lesson_translations tlt ON tl.id = tlt.text_lesson_id AND tlt.locale = 'en'
LEFT JOIN files f ON wci.item_id = f.id AND wci.item_type = 'file'
WHERE w.status = 'active'
ORDER BY w.id, wc.order, wci.order;
"""

# Execute query and load into DataFrame
df = pd.read_sql(query, conn)

# Save to CSV
df.to_csv('courses_lessons.csv', index=False, encoding='utf-8')

# Close connection
conn.close()
print("Data exported to courses_lessons.csv")

4 Clean and Format the CSV

• Verify Data: Open the CSV in Excel or a text editor to check for errors.
• Handle Missing Data: Ensure NULL fields (e.g., LessonContent) are compatible with the LMS.
• Remove HTML Tags: Use Python’s BeautifulSoup to strip HTML:

from bs4 import BeautifulSoup
df['CourseDescription'] = df['CourseDescription'].apply(lambda x: BeautifulSoup(x, 'html.parser').get_text() if pd.notnull(x) else x)
df['LessonContent'] = df['LessonContent'].apply(lambda x: BeautifulSoup(x, 'html.parser').get_text() if pd.notnull(x) else x)

• Adjust for LMS: Rename columns or split into multiple CSVs as per learning.gfavip.com requirements.

5 Import into learning.gfavip.com

• Check Documentation: Review the LMS import guide.
• Test Import: Import a small CSV subset to verify.
• Handle Files: Upload files from the files table to the LMS.
• Validate: Ensure courses, chapters, and lessons display correctly.

6 Considerations

• File Lessons: Update file paths in the files table to match the new LMS.
• Localization: Include other locales if the LMS supports multiple languages.
• Dependencies: Map category_id, creator_id, etc., correctly.
• Bundles: Include bundles and bundle_webinars if needed.