首页 新闻 搜索 专区 学院

求助:用SQL语句从Excel将数据导入到SQL数据库

0
悬赏园豆:100 [已解决问题] 解决于 2011-05-13 11:16

这个是Excel的,比如是test.xls

欠费年份 欠费开始月份 欠费结束月份 应缴金额(月租)    

2001             9                 12            94.4    

2008             5                 12            88.8    

2010             8                 12            90.4

___________________________________________

这个是表:比如是a表

a(pk,int,not null)                        //主键,自动增长

b(varchar(19),null)                    //费款所属期

c(decimal(10,2),null)                 //应缴金额___________________________________________

现在我要将test.xls中的数据导入到a表,从开始月份到结束月份要做循环导入,比如第一条2001年的从9月到12月要录入4条数据到a表,导入后的格式如:

select * from a


a       b                c

1     2001-09     94.4

2     2001-10     94.4

3     2001-11     94.4

4     2001-12     94.4


数据库是:MS Sql server 2008


小弟我研究了好久,每走一步都很艰难,特别是要做循环导入的时候,字符串拼接,日期格式转换,这些都把我弄得晕晕的,忘各位大虾能够帮助小弟,有点急,在线等。。。。。。

问题补充: 感谢CSDN qgqch2008 同时也感谢所有的乐于助人的大虾们 正解:http://topic.csdn.net/u/20110501/16/c90b3c52-a883-4387-aea5-ea76e2667090.html?1466600436
逊王之王的主页 逊王之王 | 初学一级 | 园豆:9
提问于:2011-05-01 16:15
< >
分享
最佳答案
0

一、传统方法

http://www.cnblogs.com/downmoon/archive/2011/05/02/2034191.html

二、可以使用NPOI

先读取Exel,循环处理填入DataTable,再写入SQL Server

参看http://www.cnblogs.com/downmoon/archive/2011/04/16/2017603.html

收获园豆:100
邀月 | 高人七级 |园豆:25375 | 2011-05-01 16:43
Thank
逊王之王 | 园豆:9 (初学一级) | 2011-05-02 00:49
@
逊王之王:还可使用master..spt_values,看我的文章http://www.cnblogs.com/downmoon/archive/2011/05/02/2034191.html#
邀月 | 园豆:25375 (高人七级) | 2011-05-02 09:14
其他回答(2)
0

openquery

avic_ren | 园豆:373 (菜鸟二级) | 2011-05-03 14:10
0

可以在EXCEL中使用宏来实现生成要导入的T-SQL:

Sub CreateInsertScript()
Dim Row As Integer
Dim Col As Integer

'To store all the columns available in the current active sheet
Dim ColNames(100) As String

Col
= 1
Row
= 1
Dim ColCount As Integer
ColCount
= 0
'Get Columns from the sheet
Do Until ActiveSheet.Cells(Row, Col) = "" 'Loop until you find a blank.
ColNames(ColCount) = "[" + ActiveSheet.Cells(Row, Col) + "]"
ColCount
= ColCount + 1
Col
= Col + 1
Loop
ColCount
= ColCount - 1

'Inputs for the starting and ending point for the rows
Row = InputBox("Give the starting Row No.")
Dim MaxRow As Integer
MaxRow
= InputBox("Give the Maximum Row No.")

'File to save the generated insert statements
File = "c:\\InsertCode.txt"
fHandle
= FreeFile()
Open File
For Output As fHandle

Dim CellColCount As Integer
Dim StringStore As String 'Temporary variable to store partial statement

Do While Row <= MaxRow
StringStore
= ""
CellColCount
= 0
'ActiveSheet.Name will give the current active sheet name
'this can be treated as table name in the database
StringStore = StringStore + "insert into [" + ActiveSheet.Name + "] ( "
Do While CellColCount <= ColCount
StringStore
= StringStore + ColNames(CellColCount)
'To avoid "," after last column
If CellColCount <> ColCount Then
StringStore
= StringStore + " , "
End If
CellColCount
= CellColCount + 1
Loop
'Here it will print "insert into [TableName] ( [Col1] , [Col2] , ..."
Print #fHandle, StringStore + " ) "

'For printing the values for the above columns
StringStore = " values( "
CellColCount
= 0
Do While CellColCount <= ColCount
StringStore
= StringStore + " '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "'"
If CellColCount <> ColCount Then
StringStore
= StringStore + ", "
End If
CellColCount
= CellColCount + 1
Loop

'Here it will print "values( 'value1', 'value2', ..."
Print #fHandle, StringStore + ");"
Print #fHandle,
" "
Row
= Row + 1


Loop


Close #fHandle
MsgBox ("Successfully Done")
End Sub
PetterLiu | 园豆:354 (菜鸟二级) | 2011-05-11 10:30
貌似好复杂哦
支持(0) 反对(0) 逊王之王 | 园豆:9 (初学一级) | 2011-05-13 11:15
不复杂,可参看这篇文章:
http://www.cnblogs.com/wintersun/archive/2011/05/16/2047393.html
支持(0) 反对(0) PetterLiu | 园豆:354 (菜鸟二级) | 2011-05-16 10:20
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册