Once a time when i was doing my assignment, i get a little tricky problem. And the problem is to convert a dataset of movies to an SQL query. Sound it’s easy.. but not as you thinking of it.
Owkay, lets assume that we are have a bunch of dataset and the dataset have a format :
//example user table
We want to parse it to an sql “insert” query to the table that given name “tbl_user”. The first thing you have to do is to replace the “::” characters to “,” character. So, we have a comma separated values format text (CSV) :
The next step is to import this CSV to the excel format and represent the data with have columns and rows.
Open the MS. Excel 2007, we use the text import wizard feature to do this. In the Main menu, click on Data, then click the From Text button in the Get External Data menu. This will bring up the Text Import Wizard.
In the first step, make sure the “Delimited” set as the first option. We can also select which row we would like to start from.
After that, make sure you check the box to Comma, This will then make the data separated with column. And the final step is you just to set up the type of data each column, if it’s text, date, or general.
Ok, now we have already the dataset in excel worksheet and we gonna porcess (again) to an sql query 😀 Actually, there are many tools for MS. Excel to do this, but mostly the tools are not for free. hehehe…
I have been searching the add ons or software to do that, but as i mentioned before the software is not free to use and the performance didn’t meet with my expectation. Hmm.. Finally i got this link , in there we just simply copy and paste the data that we have in excel worksheet to the text area and hit “go”. Automatically the script query “input” generated with this application, but before that, we have to follow the rules of this application and the rules is :
data in excel worksheet must be formated like this :
And then copy all of the data, process it then the output text will be like this :
That’s it.. we have done. 😀
Eh, if you have a more simple way than this.. don’t hesitate to comment below! 😀