zipcode ÆÄÀÏÀ» ´Ù¿î ¹Þ½À´Ï´Ù.
http://www.zipfinder.co.kr/
º¸Åë ¿ìÆí¹øÈ£³ª Excel ÆÄÀÏ¿¡ ÀÖ´Â ÀڷḦ SQL ·Î º¯È¯À» ÇÊ¿ä°¡ ÀÖ½À´Ï´Ù.
ÀÌ·±°æ¿ì ¸¹ÀÌ »ç¿ëÇÏ´Â ¹æ¹ýÀ» °£´ÜÈ÷ ¾Ë¾Æº¸°Ú½À´Ï´Ù.
ÀüüÀûÀÎ ¼ø¼´Â ´ÙÀ½°ú °°½À´Ï´Ù.
1. Excel ÆÄÀÏÀ̳ª DBF ÆÄÀÏÀ» Excel ¿¡¼ ¿¬´Ù. 2. ´Ù¸¥À̸§À¸·Î ÀúÀåÀ» ÇÑ ÈÄ 'csv ·Î ÀúÀå'À» ¼±ÅÃÇÑ´Ù. ¸ðµç ÇʵåµéÀÌ ½°Ç¥·Î ±¸ºÐµÈ ÅؽºÆ® ÆÄÀÏ¿¡ ÀúÀåµÈ´Ù.
3. ÀڷḦ ÀÔ·ÂÇÒ Å×À̺íÀ» ¸¸µç´Ù.(zipcode) 4. mysql ¸¦ ÀÌ¿ëÇؼ csvÆÄÀÏÀ» ÀúÀåÇÑ´Ù. csv ÆÄÀÏÀ» ¿¾î ùÁÙÀ» »èÁ¦ÇÑ´Ù. --> ZIPCODE,SIDO,GUGUN,DONG,BUNJI,SEQ ¿¹) ZIPCODE,SIDO,GUGUN,DONG,BUNJI,SEQ 135-806,¼¿ï,°³²±¸,°³Æ÷1µ¿ °æ³²¾ÆÆÄÆ®,,1 135-807,¼¿ï,°³²±¸,°³Æ÷1µ¿ ¿ì¼º3Â÷¾ÆÆÄÆ®,(1¡6µ¿),2 135-806,¼¿ï,°³²±¸,°³Æ÷1µ¿ ¿ì¼º9Â÷¾ÆÆÄÆ®,(901¡902µ¿),3 135-770,¼¿ï,°³²±¸,°³Æ÷1µ¿ ÁÖ°ø¾ÆÆÄÆ®,(1¡16µ¿),4 135-805,¼¿ï,°³²±¸,°³Æ÷1µ¿ ÁÖ°ø¾ÆÆÄÆ®,(17¡40µ¿),5 135-966,¼¿ï,°³²±¸,°³Æ÷1µ¿ ÁÖ°ø¾ÆÆÄÆ®,(41¡85µ¿),6 135-807,¼¿ï,°³²±¸,°³Æ÷1µ¿ ÁÖ°ø¾ÆÆÄÆ®,(86¡103µ¿),7 135-805,¼¿ï,°³²±¸,°³Æ÷1µ¿ ÁÖ°ø¾ÆÆÄÆ®,(104¡125µ¿),8 135-807,¼¿ï,°³²±¸,°³Æ÷1µ¿ Çö´ë1Â÷¾ÆÆÄÆ®,(101¡106µ¿),9 135-805,¼¿ï,°³²±¸,°³Æ÷1µ¿,565,10 135-806,¼¿ï,°³²±¸,°³Æ÷1µ¿,649¡651,11 135-807,¼¿ï,°³²±¸,°³Æ÷1µ¿,652¡653,12 135-810,¼¿ï,°³²±¸,°³Æ÷1µ¿,660,13
1) LOAD ¸í·ÉÀ» »ç¿ë 2) mysqlimport ¸¦ ½ÇÇà
¿©±â¼ Áß¿äÇÑ°Ç 4) ÀúÀå ºÎºÐÀÔ´Ï´Ù. zipcode Å×À̺íÀ» ´ÙÀ½°ú °°ÀÌ ¸¸µé°í.
CREATE TABLE zipcode ( ZIPCODE VARCHAR(7), SIDO VARCHAR(4), GUGUN VARCHAR(15), DONG VARCHAR(52), BUNJI VARCHAR(17), NO int(5) PRIMARY KEY ); mysql ¸í·É¾î¸¦ »ç¿ëÇؼ ÀÔ·ÂÀ» ÇÒ ¼ö ÀÖ½À´Ï´Ù.
4-1) mysql ¿¡ Ä¿¸Çµå¶óÀο¡ Á¢¼Ó
mysql > LOAD DATA LOCAL INFILE './zipcode_20090623.csv' INTO TABLE `zipcode` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
4-2) mysqlimport ¸í·ÉÀ» »ç¿ë
Âü°í ÀÚ·á : http://postman.pe.kr/zipcode/ http://www.phpschool.com/bbs2/inc_view.html?id=8886&code=tnt2&start=0&mode=&field=&operator=&period=&category_id=&s_que=
################################################### ### ÇüÈ ÀÛ¾÷ ¿¹Á¦
1. ÀúÀåÇÒ Å×ÀÌºí »ý¼º
-- ¿ìÆí¹øÈ£ --DROP TABLE postcode CASCADE CONSTRAINTS ;
CREATE TABLE postcode ( post_no VARCHAR(7) DEFAULT '' NOT NULL, region1 VARCHAR(12) DEFAULT NULL, region2 VARCHAR(80) DEFAULT NULL, note VARCHAR(50) DEFAULT NULL, etc1 VARCHAR(50) DEFAULT NULL, etc2 VARCHAR(50) DEFAULT NULL );
region1 : Ưº°½Ã,±¤¿ª½Ã,µµ¸í region2 : À¾,¸é,µ¿,¸®,°Ç¹°¸í note : ¹øÁö,¾ÆÆÄÆ®µ¿,È£¼ö etc1,2 Á¦°ÅÇÑ Çʵå
2. http://postman.pe.kr/zipcode/zipcode030217t6.dbf ´Ù¿î¹Þ´Â´Ù. 3. Excel ¿¡¼ zipcode030217t6.dbf ¸¦ Àаí zipcode030217t6.csv ·Î ÀúÀåÇÑ´Ù. - ZIPCODE, - "´Ù¸§À̸§À¸·Î ÀúÀå" => csv Çü½ÄÀ¸·Î ÀúÀå
4. csv ÆÄÀÏÀ» ¼¹ö¿¡ ¿Ã¸®°í, postcode.csv ·Î ÆÄÀϸíÀ» º¯°æÇÑ´Ù.
5. LOAD DATA INFILE À» »ç¿ëÇÒ ¼ö ÀÖµµ·Ï ¼³Á¤Çϱâ --local-infile ¿É¼ÇÀ» ÁÖ°í mysqlÀ» ½ÇÇàÇÑ´Ù.
6-1) LOAD DATA LOCAL INFILE './postcode.csv' INTO TABLE `postcode` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
6-1). mysqlimport ¸í·ÉÀ» ÀÌ¿ëÇؼ ÀúÀåÇϱâ mysqlimport ¸¦ Ä¡¸é help °¡ ¶á´Ù. ÀúÀåµÉ Å×À̺í À̸§Àº È®ÀåÀÚ¸¦ Á¦¿ÜÇÑ À̸§ÀÇ Å×ÀÌºí¿¡ ÀúÀåÀÌ µÈ´Ù. Å×À̺íÀº ¹Ì¸® ¸¸µé¾îÁ® ÀÖ¾î¾ß ÇÑ´Ù.
Usage: mysqlimport [OPTIONS] database textfile... mysqlimport survey postcode.csv
###################################################
################################################### # 6.4.9 LOAD DATA INFILE Syntax ###################################################
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ] ] [LINES TERMINATED BY '\n'] [IGNORE number LINES] [(col_name,...)]
|