Import Data from Excel file into mySQL 2000 database ? [请使用中文发问]

海星1988

what is the vb coding to import specific data column from MS excel into Ms SQL 2000, i don't hv't experience for import data before.., so hope somebody can help thx..!!

what i think to do is like when user click a button , then the program will automatic run this import process!! iniatially i write a command leke that , but it can't work, and i also don know how to select only some specific column into specific location in Ms SQL 2000,
tht's means (jst example):
Excel "Stud.xls"got columns : Name   Tel  Address  Sex
Ms SQL table "Student" : Name   Tel   
here i only want to import column "Name" and " Tel" from "Stud.xls" into table "Student"

my initially coding like tht :

Private Sub cmdimp1_Click()

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=D:\Systems\PES\PES\Stud.xls;" & _
        "Extended Properties=Excel 8.0"

   ''Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=local;Database=PESData;" & _
        "UID=123;PWD=123].Stundent " & _
        "FROM [Sheet1$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing

End Sub

Although i want to import all column into SQL , also can't !! so somebody help.., thx


[注意：请使用中文发问。谢谢合作！]

[ Last edited by Sirius on 29-10-2004 at 09:26 PM ]

goatstudio

你的 excel coding 基本上没问题, 但是你的 strSQL 为什么写成这样?
你可以先正常的从 excel SELECT, 然后再 loop 的时候 insert 进去 ms sql server 呀.
这样的话, 你需要多一个 connection, 那是连接去 ms sql server 的.

海星1988

goatstudio 于 28-10-2004 09:29 AM  说 :
你的 excel coding 基本上没问题, 但是你的 strSQL 为什么写成这样?
你可以先正常的从 excel SELECT, 然后再 loop 的时候 insert 进去 ms sql server 呀.
这样的话, 你需要多一个 connection, 那是连接去 ms sq ...

it's like that :
In my "Module"  :
Public Const ConSTR As String = "Provider=SQLOLEDB.1;User ID=123 ;Password =123 ;Persist Security Info=False;Initial Catalog=PESData ;Data Source=PesData"


In my Import Form :
Private Sub cmdimp1_Click()
Dim cn As ADODB.Connection
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

    dbConn.ConnectionString = ConSTR
    rs.Open
   
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=D:\Systems\PES\PES\HROPRMST.xls;" & _
        "Extended Properties=Excel 8.0"

    ''Import by using Jet Provider.
    strSQL = "SELECT * INTO Student FROM [Sheet1$]"
    rs.ActiveConnection = dbConn
    rs.Open SQL, , , adCmdTable
   
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing

End Sub

哈哈。。， 我自己也觉得我写得很乱，不懂在写甚么，可以给我一个明确的改法吗？？至与那个loop, 我还是不明白。

need to do something like ?? :
dim  A as string
dim  B as string

A = Name (from Stud.xsl)
B = tel (from Stud.xsl)

so
replase Student.name as A
replace Student.tel as B

I know above is not correct coding lar.., jst a 慨念, but i don't know how to write correct command !!

goatstudio

试试看, 也许会有点 bug.
先在你的 excel 里把 data 全部 highlight, 再给一个名字, 例如 Range

Dim Recordset1 As ADODB.Recordset
Recordset1.ActiveConnection = "DBQ=c:\test.xls;Driver={Microsoft Excel Driver (*.xls)};"
Recordset1.Source = "SELECT * FROM [Range]"
Recordset1.Open()

If Not Recordset1.EOF Then
  Dim conn As ADODB.Connection
  conn.Open Your_MSSQL_Connection
       
  While Not Recordset1.EOF
   conn.Exceute "INSERT INTO TABLE_NAME(FIELD1, FIELD2) VALUES ('" & Recordset1(0) & "', '" & Recordset1(1) & "')"
  Wend
       
  conn.Close
  Set conn = Nothing
End If

Recordset1.Close
Set Recordset1 = Nothing

海星1988

goatstudio 于 28-10-2004 11:06 AM  说 :
试试看, 也许会有点 bug.
先在你的 excel 里把 data 全部 highlight, 再给一个名字, 例如 Range

Dim Recordset1 As ADODB.Recordset
Re ...

thx..., i try 1st

海星1988

还是有点问题...,我重新写的coding如下 ：

Dim cn As ADODB.Connection
Dim SQL As String
Dim LngRec As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Systems\student.xls; Extended Properties=Excel 5.0"

SQL = "Select  * Into [odbc;Driver={SQL Server}; Server =Local; Database =StuData ; UID =123; PWD =123].StuProfile from [Student$]"

Debug.Print SQL
cn.Execute SQL, LngRec, adExecuteNoRecords
Debug.Print "Record affected : " & LngRec

cn.Close
Set cn = Nothing

出现的error是 "ODBC-call failed" , debug 显现在
"cn.Execute SQL, LngRec, adExecuteNoRecords"的这行coding上。

是我ODBC Setting出错了吗？？可是我已经成功通过ODBC进入到我frmlogin的form里了wor.., 可以帮帮我吗？？？

海星1988

有人可以回答我的问题吗？？

goatstudio

1. Student$ 是什么来的?
2. 有没有在 Excel 制定一个 section?
3. 你的 sql statement 很奇怪, 可以的话不要这样写.

海星1988

goatstudio 于 3-11-2004 11:18 AM  说 :
1. Student$ 是什么来的?
2. 有没有在 Excel 制定一个 section?
3. 你的 sql statement 很奇怪, 可以的话不要这样写.

1.[Student$] 相等于sheet11,
2.Excel 制定一个 section??不明白
3.sql statement 很奇怪?? 上次用过你suggest的coding，不成功，因为自己也不是很明白，所 以又try了这个！！

goatstudio

1. 不能用 sheet 的名字, 除非你用 Office Object
2. highlight 你的资料的 area, 在左上角填上你的 section 名字.
3. 用 "SELECT * FROM secti_name" 就可以了.