<p><span style="color: #404040; font-family: 굴림;">엑셀의 데이타는 .xls라는 확장자이다 보통 엑셀에서 로딩을 한후 다른이름으로 저장시&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">탭으로 분리되는 txt와 콤마로 분리되는 csv파일로 저장할수있다.&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">둘다 곧바로 mysql에는 load문으로 입력할수있다 당연히 table구조도 엑셀구조와 같아야&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">한다. 하지만 곧바로 넣기전에 약간의 가공이 필요하다면 중간에 php스크립트로 처리를 해서&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">sql파일로 만들어서 복원하는게 좋다. 그리고 반대로 mysql에서 엑셀로의 방법도 여러가지이다.&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">데이타를 뽑아내서 table을 만들어 상단에 엑셀다운해더를 추가해서 다운을 받게 하는방법과&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&nbsp;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">ex)상단에 다음의 소스를 기술하며 filename부분을 filename.xls(엑셀) filename.txt(텍스트파일)&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">filename.cvs(cvs파일)로 원하는 다운 파일로 바꾼후&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&nbsp;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">엑셀ex) 탭으로 나눔&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header( "Content-type: application/vnd.ms-excel; charset=ks_c_5601-1987" );&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header( "Content-Disposition: attachment; filename=leejunsik.xls" );&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header( "Content-Descript-xion: PHP4 Generated Data" );&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">echo "a\ta\ta\ta\ta\r\n";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">echo "a\ta\ta\ta\ta\r\n";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&nbsp;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">cvs ex) 쉄표로 나눔(,)&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header( "Content-type: application/vnd.ms-excel; charset=ks_c_5601-1987" );&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header( "Content-Disposition: attachment; filename=leejunsik.cvs" );&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header( "Content-Descript-xion: PHP4 Generated Data" );&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">echo "a,a,a,a,a\r\n";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">echo "a,a,a,a,a\r\n";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&nbsp;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">txt ex) 탭으로 나눔&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header( "Content-type: application/vnd.ms-excel; charset=ks_c_5601-1987" );&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header( "Content-Disposition: attachment; filename=leejunsik.txt" );&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header( "Content-Descript-xion: PHP4 Generated Data" );&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">echo "a\ta\ta\ta\ta\r\n";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">echo "a\ta\ta\ta\ta\r\n";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&nbsp;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">mysql데이타를 뽑아내서 파일에 tab으로 나누면서 파일에 써서 txt로 저장하거나 ,로 분리해서&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">파일에 저장하여 scv로 만들어서 엑셀에서 로딩해도 된다.&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&nbsp;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">엑셀_html형식으로 ex)html문법 사용&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;?&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header("Content-type: application/vnd.ms-excel");&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header("Content-Disposition: attachment; filename=leejunsik.xls");&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header("Expires: 0");&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header("Cache-Control: must-revalidate, post-check=0,pre-check=0");&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">header("Pragma: public");&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">?&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;table border="1"&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;tr align="center"&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;!--valign은 top과 bottom만 먹음--&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;td width="100" height="50" valign="top"&gt;순번&lt;/td&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;td width="100" height="50" valign="middle"&gt;이름&lt;/td&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;td width="100" height="50" valign="bottom"&gt;값&lt;/td&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;/tr&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;tr align="center" valign="middle"&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;td width="100" height="50" bgcolor="blue"&gt;&lt;font color="white"&gt;&amp;nbsp;001&lt;/font&gt;&lt;/td&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;td width="100" height="50" bgcolor="red"&gt;&lt;font color="white"&gt;순신&lt;/font&gt;&lt;/td&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;td width="100" height="50" bgcolor="blue"&gt;&lt;font color="white"&gt;노량해전&lt;/font&gt;&lt;/td&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;/tr&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&lt;/table&gt;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&nbsp;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">방법1&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$srcfn = "dat.txt";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$linebuf = file($srcfn);&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">//파일의 내용을 배열로 \n단위로 잘라서 대입한다.(좋은함수)&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$fp = fopen("insert.sql","w");&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">//쓰기 모드로 없으면 만들며 있으면 덮어쒸운다.&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">for($i=0;$i&lt;count($linebuf);$i++) {&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$tok = explode("\t",$linebuf[$i]);&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$aaa=trim($tok[0]);&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">//빈공간제거&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$bbb=trim(ereg_replace(",","", $tok[1]));&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">//빈공간제거와,제거&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">...&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$ccc=trim($tok[7]);&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$query = "INSERT INTO table (aaa, bbb, ..., ccc) VALUES ($aaa, '$bbb', ..., '$ccc');";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">fputs($fp,$query,strlen($query));&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">fputs($fp,"\n",strlen("\n"));&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">//입력시 엔터를 줘야 줄바꿈을 한다.&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">}&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">fclose($fp);&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&nbsp;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">방법2&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$file = "경로/파일.csv";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$fp = fopen($file,"r");&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$data = "";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">while($buffer = fread($fp,4096)) $data .= $buffer;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$rows = explode("\n",$data); // 한줄씩 나눠서 배열에 저장&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">for($i=0; $i&lt;sizeof($rows); $i++){&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$field = explode(",",$rows[$i]);&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$sql = "insert into table values('$field[0]', '$field[1]', '$field[2]', '$field[3]')";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">mysql_query($sql);&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">}&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">&nbsp;&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">방법3&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">csv를 이용하는 것은 특정 목록을 PHP에서 불러다가 인식하여 처리하기가 편하기 때문입니다.&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">csv 파일을 열어보면 레코드가 줄별로 나누어지고 한 행은 ,(콤마)를 기준으로 필드가 나누어서 저장이 됩니다.&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">a, 1, 1, 1&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">b, 2, 2, 2&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">c, 3, 3, 3&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">위와 같이 저장이 된 csv 파일이 있다면 이것을 php에서는&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">fopen()함수를 이용하여 불러들입니다.&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$file = "경로/파일.csv";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$fp = fopen($file,"r");&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$data = "";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">while($buffer = fread($fp,4096)) $data .= $buffer; //모든자료받아옴&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$rows = explode("\n",$data); // 한줄씩 나눠서 배열에 저장&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">for($i=0; $i&lt;sizeof($rows); $i++){&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$field = explode(",",$rows[$i]);&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$sql = "insert into table values('$field[0]', '$field[1]', '$field[2]', '$field[3]')";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">mysql_query($sql);&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">}&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">file 함수로 이용하시면 편리합니다.&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">예)&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">while ($line = file($file)) {&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$value = str_replace(",","','",$line);&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">$sql = "insert into table values('".$value."')";&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">mysql_query($sql);&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">}&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">**csv보다는 txt파일이 좋은이유는 단어 중에 , 가 들어있으면 그것도 분리가 되서&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">에러가 날가망성도 있다. txt파일에서의 탭(\t)은 보통 입력을 안하므로 위에 오류가 일어날&nbsp;</span><br style="color: #404040; font-family: 굴림;" /><br style="color: #404040; font-family: 굴림;" /><span style="color: #404040; font-family: 굴림;">확률이 적다.</span></p>