导入excel colls到sql [英] Import excel colls to sql

问题描述

我正在寻找创建脚本来上传CSV文件或xls并插入数据库。我已经设法使它到现在为止，但我w

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

喜欢做的是将它插入到一个mysql表tbl_dimensions中，如下所示：

  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 
  

基本上而不是在SQL中创建一个宽表在表中创建一个列表，但是我需要保留项目代码，AA，BB等。可以多次使用相同的代码，所以我想使用CSV / XLS中的行nr来协调CAP1， CAP2，CAP3。 Cap1,2 3，每天都可以做任何改变，所以我无法为每个选项定义足够的表格。

非常感谢你

解决方案

我已经设法弄清楚将会使用数组。我仍然没有排序，但我会把我的代码放在这里，也许你可以帮助我。

 <？php 
函数csv_to_array（$ filename =''，$ delimiter ='，'）{
 if（！file_exists（$ filename）||！is_readable（$ filename））return 
 FALSE; $ header = NULL; $ data = array（）;如果（（$ 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'））; 
 
？> 
  

我的csv文件如下所示：

  cod_piesa，lungime，latime，inaltime 
 gl，5000 ,, 150 
 fd，3000，2000，100 
  / pre> 
 
 
我想做的是插入到这样的mysql表中
  row = 0 
 cod_piesa = GL 
 header = lungime 
 value = 5000 
 
下一个
 
 row = 0 
 cod_piesa = GL 
 header = latime 
 value = NULL 
 
 row = 0 
 cod_piesa = GL 
 header = inaltime 
 value = 150 
 
 row = 1 
 cod_piesa = FD .....等等。 
  
 mysql表称为tbl_drafts，它具有draft_id，row，cod_piesa，header，value的列。 draft_id是我将生成的临时ID，在插入后，我需要将其重定向到一个新页面，我将比较不同的结果，让我们说$ draft_id = 1 
 
 
 
 'verify.php'。
 
 
 
非常感谢您的所有答案
 
 
 
暂时我是使用在服务器上上传的文件，但我需要一个手动上传选项，访问者可以上传CSV或excel文件。
 
 
 
我确定这将有助于很多人如果解决了。 
我看过转置选项，但我无法想像。
 
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
解决方案
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.

                        
这篇关于导入excel colls到sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！