Import excel colls to sql

Question

I'm looking to create a script to upload CSV files or xls and insert into the database. I've managed to make it until now, but I w

ID   CODE   CAP1   CAP2   CAP3
1    AA      50     60     70
2    BB      80     90     100
3    AA             120    130

What I would like to do is to insert it in a mysql table tbl_dimensions like this:

RECID   DRAFT_IMPORT   CODE   ID    HEADER    VALUE
1          XXXXX       AA      1     CAP1      50
2          XXXXX       AA      1     CAP2      60
3          XXXXX       AA      1     CAP3      70
4          XXXXX       BB      2     CAP1      80
5          XXXXX       BB      2     CAP2      90
6          XXXXX       BB      2     CAP3      100
7          XXXXX       AA      3     CAP1      NULL/0
8          XXXXX       AA      3     CAP2      120
9          XXXXX       AA      3     CAP3      130

Basically instead of creating a wide table in SQL in want to create a list in a table but I need to keep the item Code, AA, BB, etc. It's possible to have the same code multiple time so I want to use the row nr from CSV/XLS to coordinate the values from CAP1, CAP2, CAP3. Cap1,2 3, can be anything and changes every day so I cant define a table wide enough for each option that will occure.

Thank you very much

Answer 1

I have managed to figure out that will work with array. I still haven't sort it but I'll put my code here, maybe you can help me.

<?php
   function csv_to_array($filename='', $delimiter=',') {
    if(!file_exists($filename) || !is_readable($filename))      return
   FALSE;       $header = NULL;     $data = array();    if (($handle =
   fopen($filename, 'r')) !== FALSE)    {       while (($row =
   fgetcsv($handle, 1000, $delimiter)) !== FALSE)       {           if(!$header)
                $header = $row;
                        else
                $data[] = array_combine($header, $row);
                    }       fclose($handle);    }   return $data; } /**  * Example  */ print_r(csv_to_array('example.csv'));

   ?>

my csv file looks like this:

cod_piesa, lungime, latime, inaltime
gl, 5000,, 150
fd, 3000, 2000, 100

what I want to do is to insert into a mysql table like this

row = 0
cod_piesa = GL
header = lungime
value = 5000

next one

row = 0
cod_piesa = GL
header = latime
value = NULL

row = 0
cod_piesa = GL
header = inaltime
value = 150

row = 1
cod_piesa = FD..... and so on.

mysql table is called tbl_drafts and it has the columns draft_id, row, cod_piesa, header, value. draft_id is a temporary ID that I will generate let's say $draft_id=1

after insert I need to redirect him to a new page where I'll compare different results let's say 'verification.php'.

Thank you very much for all your answers

For time being I'm using a file that's uploaded on the server but I would need to have a manual upload option where visitor can upload the CSV or excel file.

I'm sure this will help a lot of people if solved. I had a look into transposition option but I couldn't figure it out.