Import CSV and Excel to SQL

Most of us have been there

It’s late afternoon. You look forward to going home to your family and have a nice evening off relaxing. Then, all of a sudden, your PM stops by with a wish. You have to import some customers data into a system now. Today. As it can’t wait till tomorrow. Then you realize it’s a pretty big file at stake. You get on the phone and tell your spouse you are gonna be late. Yes. Again…

Importing CSV to SQL databases can be pretty easy when it comes to small files containing a determined and “known” data. But when the file is bigger and the data type is unknown, often you just end up defining column types that might either be unoptimized or plain wrong, thus resulting in data truncation and/or failed imports.

Although some GUI applications and database systems support this out of the box, some problems remain. The biggest one of those problems being that you have to define the schema for it by hand. Secondly, let`s not forget about how trying to import big files via GUI applications can result in system freeze ups, or in the worst of cases (one which windows users are well aware of) in “the blue screen of death”.

When dealing with big imports, using CLI is often the safest way to go. Combine this with batching the imports and you will get more control over how much of your resources your system takes.

With PostgreSQL for example, you can utilize the COPY functionality from the CSV file directly. In the case just mentioned you can import CSV data into a defined table. But it stills leaves you with the job of defining the table schema before you can do so.

Dilemma: Data truncation and optimized table structure

In the best of worlds, columns contain one type of data, for example, integers, decimals, timestamps or strings.

But what happens when there is mixed data in the columns? Not uncommon for input files created with a human touch. Humans are not perfect, as we all know, yet they are cool. 🙂

Well, in the worst of cases, defining a wrong column type for your table will result in data truncation. The data will not be supported for that type of columns and eventually will be rejected by the database server on import.

Going through a file of thousands of rows by hand is also problematic. Think from your own perspective on time about how long that would take. Is it even practical at the end of the day?

Time to say hello to Convert2SQL!

Keeping it straight to the point, Convert2SQL solves this dilemma effectively; and by effectively I mean with little effort from the user. All in all, you just have to:

• Select the database server type the import is for: MySQL, MariaDB, PostgreSQL or MS SQL Server.
• Apply options such as adding an auto incremental primary key and a table name for your new import.
• Upload the file that brought you to the service in the first place.
• Now, with the upload done, the system takes over analyzing and converting your file.

Once the conversion is completed, a download link is sent to you with a defined schema and the converted SQL dump. All ready to be imported!

The service will analyze the entire file and define a schema that is data truncation safe and optimized for your SQL Server type.

Laravel Migration

“Laravel Migration too. Imports managed all way through.”

When we put together the presentation video for this product we tried our best to be poetic, but first of all sincere. I love the Laravel Framework with its Migration feature, so I had to include it in this odyssey. Reasons to why I made this addition can be many; Version control for your imports, rollback functionality, database environment pivoting. Pretty neat huh?

Ok…I´ll go now and let you try Convert2SQL at www.convert2sql.com. As for myself, I´ll be looking forward to your feedback.