CSV to excel and excel to SQL

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
uid::sex::age::occupation_code::zip_code

...
1::F::1::10::48067
2::M::56::16::70072
3::M::25::15::55117
4::M::45::7::02460
...


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) :


...
1,F,1,10,48067
2,M,56,16,70072
3,M,25,15,55117
4,M,45,7,02460
...

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.

excel

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 :

excel2

And then copy all of the data, process it then the output text will be like this :

excel3

That’s it.. we have done.😀
Eh, if you have a more simple way than this.. don’t hesitate to comment below!😀

Satu pemikiran pada “CSV to excel and excel to SQL

  1. Just want to say what a great blog you got here!
    I’ve been around for quite a lot of time, but finally decided to show my appreciation of your work!

    Thumbs up, and keep it going!

    Cheers
    Christian, Satellite Direct Tv

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s