导入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
/ pre>
gl,5000 ,, 150
fd,3000,2000,100
我想做的是插入到这样的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屋!