Using spreadsheets is a comfortable and flexible system to manage and consult data; a user of Microsoft Excel or Google Spreadsheets is free to combine cells, format tables or add comments to their content.
But flexibility is a double-edged sword, which is accompanied by greater data inconsistency. This means that there is a greater probability, for example, of making accidental changes to the data and that the data type (numeric, textual, etc.) of a cell is not what is expected.
Also, from a certain amount of data, spreadsheets tend to become unwieldy due to their slowness: theoretically Excel is capable of supporting up to a million rows, but the multiple tabs, pivot tables and formulas end long before their endurance and our patience.
However, the different technologies of relational databases (with SQL, the most used, in the lead) They are designed precisely for this task, being faster to use and more secure when handling data.
Therefore, we have prepared you this list of free tools that will allow you to convert all your data already collected and organized with Excel in SQL databases:
DOMINA MICROSOFT EXCEL: 17 ESSENTIAL FORMULAS
MySQL for Excel
Available for Windows on the same official website of the MySQL project, ‘MySQL for Excel’ is an add-in for Microsoft’s spreadsheet editor, which is accessed from the same ‘Data’ panel of the latter, and that offers a wizard that guides the user with a less technical profile when it comes to exporting MySQL data from Excel (although it also allows the inverse conversion, from MySQL to Excel).
This plugin allows user to create MySQL table from Excel data they select– The data types will be automatically recognized and set as the default for the new table, and the names of each SQL column will automatically be set from the first row of data on the sheet.
Excel to MySQL
Two aspects differentiate this tool from the previous option: first, ‘Excel to MySQL’ is a simple online tool to which we must upload our spreadsheet; second, it not only provides us with the already converted .sql file, but also the SQL code in case we want to generate the database ‘by hand’.
Once the file is uploaded (it is compatible with the .xls / .xlsx, .csv, .xml and .ods formats), the platform allows us to configure aspects such as the inclusion of an extra column for the ‘Id’ field, or the name from the table. In the next step, there is also the option to indicate the type of data of each row or if we want to do without any of them.
Beautify Tools is the name of a (very) wide collection of free online tools ranging from code minifiers and validators to SEO tools, through favicon and QR code generators, and unit or file converters.
It is among the latter where we can find the utilities ‘Excel to SQL Converter’ and ‘CSV to SQL Converter’, which allow us to load our spreadsheets and obtain an SQL code that we can edit, if necessary, before downloading it.
BeautifyConverter appears to be a fixed and augmented version of the Beautify Tools: contains all the utilities present in the previous tool, and adds new ones, among which is ‘TSV to SQL Converter’, thus covering yet another spreadsheet format.
The differences between the two options are few, apart from the graphical interface, the main one is that BeautifyConverter adds, in some cases, the option to load the file to convert from a URL (and not only from our PC).
Mr. Data Converter
Another free web conversion tool, although there are several things that differentiate ‘Mr. Data Converter ‘from its already reviewed rivals. First of all, we will not have to select and upload any files: the data is incorporated from Excel through the copy paste lifelong (And it also gives us the option to write the data separated by commas or tabs on the fly).
Second, from the same interface we have the option to select the type of code to which the spreadsheet will be converted: not only MySQL, but also XML, JSON, HTML, etc.. We will not be able, of course, to download it, only to copy and paste the generated code again.
Using SQLizer it is simple and designed to be solved in a single step: once we enter the web page, we select the data sheet file that we want to convert (not only Excel files with the extension .xls / .xlsx, also .csv, .tsv, .xml and .json), and the type of database to convert it to (either MySQL, PostgreSQL or Microsoft SQL Server).
Then, we can configure other aspects Obligations such as the name of the sheet and the range of cells that will be taken into account for the conversion process, as well as the name of the final file.
The gratuitousness of this service remains, unfortunately, limited to non-commercial use of files with less than 5000 rows of data. From there, you have several options starting at $ 15.
Image | Pixabay & RRZEicons (via Wikimedia)