RAGbasedERP

RAGbasedERP is a Java-based utility designed to import data from Excel files (.xls or .xlsx) directly into a MySQL database. This project automates the process of reading Excel files, dynamically creating tables based on the file's headers, and efficiently loading the data into the specified database table.

Features

• Universal Excel Import: Supports both .xls and .xlsx formats using Apache POI.
• Automatic Table Creation: Dynamically creates a database table based on the header row of the Excel file, with cleaned column names.
• Robust Data Insertion: Handles empty rows, blank cells, and multiple data types (string, numeric, boolean, date, formula).
• Progress Feedback: Prints progress for large files and details on import success.
• Custom Table Naming: Allows users to specify the target table name, defaulting to excel_data if omitted.

Getting Started

Prerequisites

• Java 8+
• MySQL Database
• Apache POI Library
  • Add poi-ooxml and poi to your project dependencies.

Database Configuration

By default, the utility connects to:

• URL: jdbc:mysql://localhost:3306/project1
• User: root
• Password: System@305

Edit these constants at the top of ExcelDB.java if your database configuration differs.

private static final String DB_URL = "jdbc:mysql://localhost:3306/project1";
private static final String USER = "root";
private static final String PASSWORD = "System@305";

Build & Run

1. Clone the repository:

   git clone https://github.com/RADson2005official/RAGbasedERP.git
   cd RAGbasedERP

2. Ensure dependencies are available: Download Apache POI jars and place them in your classpath.

3. Compile:

   javac -cp ".:path/to/poi.jar:path/to/poi-ooxml.jar:mysql-connector-java.jar" ExcelDB.java

4. Run:

   java -cp ".:path/to/poi.jar:path/to/poi-ooxml.jar:mysql-connector-java.jar" ExcelDB

5. Follow prompts:

   • Enter the path to your Excel file (e.g., data.xlsx)
   • Enter your target database table name (or press enter to use the default)

How It Works

• Excel Parsing: Reads the first row for headers, cleans column names, and creates a corresponding table.
• Dynamic Table Creation: If the table does not exist, it is created with columns as TEXT.
• Data Import: Each subsequent row is parsed and inserted. Progress is printed every 100 rows.
• Error Handling: Handles errors such as missing JDBC driver, database connection issues, file format problems, and unexpected exceptions.

Example Usage

Enter path to Excel file: data.xlsx
Enter database table name: sales_data
Connecting to database...
Connection successful!
Table structure verified/created: sales_data
Table has 5 columns (including id)
Excel data successfully imported to database table: sales_data
Imported 1200 rows into table sales_data

Dependencies

• Apache POI for Excel file parsing
• MySQL JDBC Driver for database connectivity

Contribution

Contributions are welcome! Please follow these guidelines:

1. Fork the repository and create a new branch.
2. Ensure your code is well-commented and follows Java conventions.
3. If adding features, update this README and provide example cases.
4. Submit a pull request with a clear description of changes.

License

No explicit license file is provided; please contact the repository owner for usage terms.

Support & Issues

Please use the GitHub Issues tab for bug reports, feature requests, or questions.

---

Author: RADson2005official