如果只是一个csv表,那么,你用ultraedit打开它, 直接编辑它,改造成 create table .... insert into <t> values(.... 应该很快.
这个场景中可以用正则表达式,例如: 第一行有四个列a,b,c,d 那么可以用正则: ^(.*?),(.*?),(.*?),(.*)$ 匹配后用正则替换: create table table_name\(\r\n`\1` varchar(45),\r\n`\2` varchar(45),\r\n`\3` varchar(45),\r\n`\4` varchar(45),\r\n primary key\(`\1`\)\r\n\);\r\n 用python或者java等支持正则的语言写个小脚本吧,很快可以搞定。 另外,附上我的一个python小脚本,可在此基础上改进:import redef _build_regex_str(len): if len<1: return if len==1: return r"^(.*)$" result = r"^(.*?)" for i in range(len-2): result +=r",(.*?)" result +=r",(.*)$" return resultdef _build_create_table_str(table_name,column_count): if column_count <1: return if column_count == 1: return r"create table %s (\r\n'\1' varchar(45),\r\nprimary key(`\1`))\r\n"%table_name result = r"create table %s (\r\n'\1' varchar(45)"%table_name; for i in range(column_count-2): result +=r",\r\n'\%i' varchar(45)"%(i+2) result +=r",\r\nprimary key('\1'))" return resultdef _create_sql_file(sql_file_name,source_file_name): src_file=open(source_file_name+".csv") tar_file=open(sql_file_name+".sql","w") s = src_file.readline() column_count = len(s.split(",")) if column_count>=1: regex = re.compile(_build_regex_str(column_count)) tar_file.write(regex.sub(_build_create_table_str("some_table_name",column_count),s) tar_file.close(); src_file.close();if __name__=="main": _create_sql_file("target_file_name","source_file_name"):
可以用正则: 如一个csv有a,b,c,d 四列 匹配正则如下: ^(.*?),(.*?),(.*?),(.*)$ 替换正则如下: create table table_name\(\r\n`\1` varchar(45),\r\n`\2` varchar(45),\r\n`\3` varchar(45),\r\n`\4` varchar(45),\r\n primary key\(`\1`\)\r\n\);\r\n 顺便附上我的一个python小脚本:import redef _build_regex_str(len): if len<1: return if len==1: return r"^(.*)$" result = r"^(.*?)" for i in range(len-2): result +=r",(.*?)" result +=r",(.*)$" return resultdef _build_create_table_str(table_name,column_count): if column_count <1: return if column_count == 1: return r"create table %s (\r\n'\1' varchar(45),\r\nprimary key(`\1`))\r\n"%table_name result = r"create table %s (\r\n'\1' varchar(45)"%table_name; for i in range(column_count-2): result +=r",\r\n'\%i' varchar(45)"%(i+2) result +=r",\r\nprimary key('\1'))" return resultdef _create_sql_file(sql_file_name,source_file_name): src_file=open(source_file_name+".csv") tar_file=open(sql_file_name+".sql","w") s = src_file.readline() column_count = len(s.split(",")) if column_count>=1: regex = re.compile(_build_regex_str(column_count)) tar_file.write(regex.sub(_build_create_table_str("some_table_name",column_count),s) tar_file.close(); src_file.close();if __name__=="main": _create_sql_file("target_file_name","source_file_name"):
sorry,拷错了 把tar_file.write那一行替换成sql_file_name 脚本如下:import redef _build_regex_str(len): if len<1: return if len==1: return r"^(.*)$" result = r"^(.*?)" for i in range(len-2): result +=r",(.*?)" result +=r",(.*)$" return resultdef _build_create_table_str(table_name,column_count): if column_count <1: return if column_count == 1: return r"create table %s (\r\n'\1' varchar(45),\r\nprimary key(`\1`))\r\n"%table_name result = r"create table %s (\r\n'\1' varchar(45)"%table_name; for i in range(column_count-2): result +=r",\r\n'\%i' varchar(45)"%(i+2) result +=r",\r\nprimary key('\1'))" return resultdef _create_sql_file(sql_file_name,source_file_name): src_file=open(source_file_name+".csv") tar_file=open(sql_file_name+".sql","w") s = src_file.readline() column_count = len(s.split(",")) if column_count>=1: regex = re.compile(_build_regex_str(column_count)) tar_file.write(regex.sub(_build_create_table_str(sql_file_name,column_count),s) tar_file.close(); src_file.close();if __name__=="main": _create_sql_file("target_file_name","source_file_name")
直接编辑它,改造成
create table ....
insert into <t> values(....
应该很快.
第一行有四个列a,b,c,d
那么可以用正则:
^(.*?),(.*?),(.*?),(.*)$
匹配后用正则替换:
create table table_name\(\r\n`\1` varchar(45),\r\n`\2` varchar(45),\r\n`\3` varchar(45),\r\n`\4` varchar(45),\r\n primary key\(`\1`\)\r\n\);\r\n
用python或者java等支持正则的语言写个小脚本吧,很快可以搞定。
另外,附上我的一个python小脚本,可在此基础上改进:import redef _build_regex_str(len):
if len<1:
return
if len==1:
return r"^(.*)$"
result = r"^(.*?)"
for i in range(len-2):
result +=r",(.*?)"
result +=r",(.*)$"
return resultdef _build_create_table_str(table_name,column_count):
if column_count <1:
return
if column_count == 1:
return r"create table %s (\r\n'\1' varchar(45),\r\nprimary key(`\1`))\r\n"%table_name
result = r"create table %s (\r\n'\1' varchar(45)"%table_name;
for i in range(column_count-2):
result +=r",\r\n'\%i' varchar(45)"%(i+2)
result +=r",\r\nprimary key('\1'))"
return resultdef _create_sql_file(sql_file_name,source_file_name):
src_file=open(source_file_name+".csv")
tar_file=open(sql_file_name+".sql","w")
s = src_file.readline()
column_count = len(s.split(","))
if column_count>=1:
regex = re.compile(_build_regex_str(column_count))
tar_file.write(regex.sub(_build_create_table_str("some_table_name",column_count),s)
tar_file.close();
src_file.close();if __name__=="main":
_create_sql_file("target_file_name","source_file_name"):
如一个csv有a,b,c,d 四列
匹配正则如下:
^(.*?),(.*?),(.*?),(.*)$
替换正则如下:
create table table_name\(\r\n`\1` varchar(45),\r\n`\2` varchar(45),\r\n`\3` varchar(45),\r\n`\4` varchar(45),\r\n primary key\(`\1`\)\r\n\);\r\n
顺便附上我的一个python小脚本:import redef _build_regex_str(len):
if len<1:
return
if len==1:
return r"^(.*)$"
result = r"^(.*?)"
for i in range(len-2):
result +=r",(.*?)"
result +=r",(.*)$"
return resultdef _build_create_table_str(table_name,column_count):
if column_count <1:
return
if column_count == 1:
return r"create table %s (\r\n'\1' varchar(45),\r\nprimary key(`\1`))\r\n"%table_name
result = r"create table %s (\r\n'\1' varchar(45)"%table_name;
for i in range(column_count-2):
result +=r",\r\n'\%i' varchar(45)"%(i+2)
result +=r",\r\nprimary key('\1'))"
return resultdef _create_sql_file(sql_file_name,source_file_name):
src_file=open(source_file_name+".csv")
tar_file=open(sql_file_name+".sql","w")
s = src_file.readline()
column_count = len(s.split(","))
if column_count>=1:
regex = re.compile(_build_regex_str(column_count))
tar_file.write(regex.sub(_build_create_table_str("some_table_name",column_count),s)
tar_file.close();
src_file.close();if __name__=="main":
_create_sql_file("target_file_name","source_file_name"):
把tar_file.write那一行替换成sql_file_name
脚本如下:import redef _build_regex_str(len):
if len<1:
return
if len==1:
return r"^(.*)$"
result = r"^(.*?)"
for i in range(len-2):
result +=r",(.*?)"
result +=r",(.*)$"
return resultdef _build_create_table_str(table_name,column_count):
if column_count <1:
return
if column_count == 1:
return r"create table %s (\r\n'\1' varchar(45),\r\nprimary key(`\1`))\r\n"%table_name
result = r"create table %s (\r\n'\1' varchar(45)"%table_name;
for i in range(column_count-2):
result +=r",\r\n'\%i' varchar(45)"%(i+2)
result +=r",\r\nprimary key('\1'))"
return resultdef _create_sql_file(sql_file_name,source_file_name):
src_file=open(source_file_name+".csv")
tar_file=open(sql_file_name+".sql","w")
s = src_file.readline()
column_count = len(s.split(","))
if column_count>=1:
regex = re.compile(_build_regex_str(column_count))
tar_file.write(regex.sub(_build_create_table_str(sql_file_name,column_count),s)
tar_file.close();
src_file.close();if __name__=="main":
_create_sql_file("target_file_name","source_file_name")
target_file_name和source_file_name
分别改成目标文件名称,csv文件名称