How to extract data from spreadsheets or Excel to database using terminal in Ubuntu?
I'm using MySQL in Ubuntu and now I have 3 different Excel files using which I need to create a database in SQL.
Can someone help me with how can I extract that Excel data to MySQL?
The simplest way is to create a formula in the additional column. This formula has to make a correct INSERT statement. E.g.:
="INSERT INTO MYTABLE (field1, field2) VALUES ('" & A1 & "', '" & B1 & "');"
Then fill in the whole column with this formula (A1 and B1 become A2 and B2, A3 and B3, etc.)
Select the whole column and copy to clipboard, then activate the terminal and insert from clipboard.
A1, B1 and so on. At least we should reduplicate all single quotes possibly occurring in them, to prevent syntax errors while inserting.
It depends how complex your spreadsheet is, how many worksheets, whether someone has put all sorts of merged cells, fancy titles etc
I've had some success using Apache Tikka as a content extraction tool with basic Linux bash utilities such as grep, awk, sort etc. I've had to do this to determine which spreadsheets might contain GDPR sensitive data.
Tikka can extract data from over 1400 file formats and is a JAR file that can be called just like any other Java program. The useful output from a spreadsheet will be tab delimited. The name of the sheet will without leading tabs. Cells will be separated by tabs and the first column in any sheet will be prefixed by a tab. This makes it really easy to grab what output you need and use the MySQL COPY FROM statement to ingest it.
