• • •

Excel To Database: Fast Bulk Import for SQL Server and Oracle

What a Nightmare

When someone comes to us with an Excel Workbook to load into a database..., several things immediately come to mind:

  • The headache of loading an Excel Workbook into a database.
  • The person giving us the Excel Workbook usually has no idea what a database is.
  • Pray that it doesn't have too many rows.
  • If it's going to be a repetitive process, please ask them to deliver flat files, like ".csv" and in the correct format.

We immediately start searching the internet and can find, among other things, the following possibilities:

  • Convert the Excel Workbook to ".csv" files and then load those flat files:
    • Horror, we will have to save each sheet as a separate .csv, probably manually.
    • Horror, the Excel Workbook contains texts with line breaks.
    • Horror, I have to create a macro to format the cells and make a good export to .csv.
    • Horror, the .csv loaders for Oracle and SQL Server, for example, cause many headaches.
  • Use tools like SQLizer:
    • Horror, the non-commercial version only handles 5000 lines.
    • Horror, it generates an INSERT statement for each line, which will be very slow. For SQL Server and Oracle, there are faster alternatives.
    • Horror, the types deduced from the Excel Sheet do not match those in the database table.
  • Use Dropbase to create an instant database:
    • Horror, back to loading .csv files.
    • Horror, there is a 50MB limit.
  • Use the data import wizards provided by Microsoft, Oracle, etc.:
    • Horror, defining the load is tedious and horrible.
    • Horror, it's very slow.
    • Horror, after waiting for hours they stop at the first error (without giving any explanation) and do not continue.
    • Horror, I probably need to have the Microsoft Access engine installed.
    • Horror, I have problems between 32 and 64 bits.

The solution

Your problems are over. Excel To Database will do all the work for you: you will only supervise and make occasional corrections.

  • It has no size limit.
  • It is extremely fast in SQL Server and Oracle.
  • Data loads can be executed from the command line. It is very easy to schedule loading tasks.
  • Mapping assignments between columns based on name.
  • Data loading does not stop at the first data error encountered, but reports all of them so they can be corrected.
  • Option to not load cells that contain errors.
  • Option to not load cells that cause errors in data conversion.

Excel To Database is the ultimate tool to import Excel to database automatically. Forget about manual errors when migrating Excel files to SQL Server, Oracle, or MS Access. Our solution optimizes your data workflow, ensuring integrity and speed in every upload.

Data Loading Strategy

Many data loaders perform a preliminary analysis of the data contained in the Excel Sheet before loading. However, they do not use all the data, but usually analyze a limited number of rows. Obviously, this causes them to fail more often than not, giving us a headache, and requiring us to handle the loader with patience and care. Subsequently, during data loading, they typically read and insert data row by row, stopping at the first error encountered, which forces us to make corrections to the loader's parameters or correct the data. This process can be tedious, slow, and frustrating.

The approach of Excel To Database is different:

  1. It obtains the fields of the table and the data type of each field.

  2. Reading the data contained in the Excel Sheet. Checking the data, performing the necessary conversions, and making the corrections indicated in the load parameters.

    During this phase, all necessary information regarding data issues is shown to the user so they can redefine the loader's parameters or correct the data so it can be loaded.

  3. Data loading into the database using bulk loading methods (SQL Server and Oracle), or using very fast custom loaders that insert data row by row for ODBC and OLE DB data providers.