使用bulkcopy将excel文件导入我的SQL [英] Importing excel files to my SQL using bulkcopy

问题描述

你好伙计们我​​在构建一个项目时遇到问题

我正在尝试使用bulkcopy将excel文件导入到sql server 2016并且我收到了这个错误



'DAFTAR1 $'不是有效名称。请确保它不包含无效字符或标点符号且不会太长



我认为此代码存在问题

Dim query_excel As String =SELECT * from [&档案& $]



i在另一台电脑上尝试了所有这些代码并且它工作得很好但是在我的电脑上却出现了错误，我不知道我错了什么，我正在使用visual basic 2010专业版，sql管理服务器2016和微软办公室2016



谁能帮我弄清楚代码有什么问题？



我的尝试：

 <预> 
 私有  Sub  Button1_Click（ ByVal  sender  As  System。 Object ， ByVal  e  As  System.EventArgs）句柄 Button1.Click 
 如果 OpenFileDialog1.ShowDialog = DialogResult.OK 那么 
 TextBox1.Text = OpenFileDialog1.FileName 
 file = System.IO。 Path.GetFileNameWithoutExtension（TextBox1.Text）
 
 结束 如果 
 
 结束  Sub  
 
 私人  Sub  Button2_Click（ ByVal 发​​件人作为系统。对象， ByVal  e < span class =code-keyword> As  System.EventArgs）句柄 Button2.Click 
 
  Dim  koneksi_excel 作为 新 System.Data.OleDb.OleDbConnection（  Provider = Microsoft.ace.OLEDB.12.0; Data Source ='& TextBox1.Text&  ';扩展属性=Excel 12.0 Xml; HDR = YES;）
 koneksi_excel.Open（）
 
  Dim  query_excel  As  字符串 =   SELECT * from [&档案&   $] 
  Dim  cmd 作为 OleDb.OleDbCommand = 新 OleDb.OleDbCommand（query_excel，koneksi_excel）
  Dim  rd  As  OleDb.OleDbDataReader 
 
 rd = cmd.ExecuteReader（）
 
  Dim  koneksi 作为 新 SqlClient.SqlConnection（）
  Dim  koneksidatabase  As  字符串 =   server = DESKTOP-KJQ8PNO \ SVQPRESS; database = otto; Integrated Security = True  
 koneksi.ConnectionString = koneksidatabase 
 
 
 koneksi.Open（）
  Dim  da  As  新 OleDb.OleDbDataAdapter 
  Dim  ds  As  新 DataSet（） 
  Dim  dt 作为 新 DataTable 
 ds.Tables.Add（dt）
 da = 新 OleDb.OleDbDataAdapter（query_excel，koneksi_excel）
 da.Fill（ dt）
 
 
 使用 bulkcopy 作为 SqlClient.SqlBulkCopy = < span class =code-keyword>新 SqlClient.SqlBulkCopy（koneksi）
 bulkcopy.DestinationTableName = file 
 bulkcopy.BulkCopyTimeout =  600  
 bulkcopy.WriteToServer（rd）
 rd.Close（）
 
 
 MsgBox（ 数据上传到数据库，MsgBoxStyle.Information， 已上传）
 TextBox1.Text =   
 结束 使用 
 < span class =code-keyword> End   Sub  

解决方案

'不是有效名称。确保它不包含无效字符或标点符号并且不会太长



我认为此代码存在问题

Dim query_excel As String =SELECT * from [& file&

i尝试全部这个代码在另一台PC上运行完美，但在我的电脑上却出现了错误，我不知道我错了什么，我使用的是visual basic 2010专业版，sql管理服务器2016和微软办公室2016



任何人都可以帮我弄清楚代码有什么问题吗？



我尝试了什么：

< pre> 
 私人  Sub  Button1_Click（ ByVal  sender  As  System。 对象， ByVal  e  As  System.EventArgs）句柄 Button1.Click 
 如果 OpenFileDialog1.ShowDialog = DialogResult.OK 然后 
 TextBox1.Text = OpenFileDialog1.FileName 
 file = System.IO.Path.GetFileNameWithoutExtension （TextBox1.Text）
 
 结束 如果 
 
 结束  Sub  
 
 私有  Sub  Button2_Click（ ByVal  sender  As  System。 Object ， ByVal  e  As  System.EventArgs）句柄 Button2.Click 
 
  Dim  koneksi_excel 作为 新 System.Data.OleDb.OleDbConnection（  Provider = Microsoft.ace.OLEDB.12.0; Data Source ='& TextBox1.Text&  ';扩展属性=Excel 12.0 Xml; HDR = YES;）
 koneksi_excel.Open（）
 
  Dim  query_excel  As  字符串 =   SELECT * from [&档案&   

Dim cmd As OleDb.OleDbCommand = 新 OleDb.OleDbCommand（query_excel ，koneksi_excel）
Dim rd As OleDb.OleDbDataReader

rd = cmd.ExecuteReader（）

Dim koneksi As 新 SqlClient.SqlConnection（）
Dim koneksidatabase As 字符串 = server = DESKTOP-KJQ8PNO \ SVQPRESS; database = otto; Integrated Security = True
koneksi.ConnectionString = koneksidatabase


koneks i.Open（）
Dim da As 新 OleDb.OleDbDataAdapter
Dim ds 作为 New DataSet（）
Dim dt As 新 DataTable
ds.Tables.Add（dt）
da = 新 OleDb.OleDbDataAdapter（query_excel ，koneksi_excel）
da.Fill（dt）


使用 bulkcopy 作为 SqlClient.SqlBulkCopy = 新 SqlClient.SqlBulkCopy（koneksi）
bulkcopy.DestinationTableName = file
bulkcopy.BulkCopyTimeout = 600
bulkcopy.WriteToServer（rd）
rd.Close（）


MsgBox（ 数据上传到数据库，MsgBoxStyle.Information， 已上传）
TextBox1.Text =
结束 使用
结束 Sub

hello guys I'm having a problem with a project that I build
I'm trying to import excel files to sql server 2016 using bulkcopy and I got this error

"'DAFTAR1$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long"

I think there is a problem with this code
Dim query_excel As String = "SELECT * from [" & file & "$]"

i tried all of this code on another pc and it works perfectly but on my pc it's getting that error, I don't know what did I wrong, I'm using visual basic 2010 professional edition, sql management server 2016 and microsoft office 2016

can anyone help me to figure what is wrong with the code ?

What I have tried:

<pre>
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If OpenFileDialog1.ShowDialog = DialogResult.OK Then
            TextBox1.Text = OpenFileDialog1.FileName
            file = System.IO.Path.GetFileNameWithoutExtension(TextBox1.Text)

        End If

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim koneksi_excel As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ace.OLEDB.12.0;Data Source='" & TextBox1.Text & "';Extended Properties=""Excel 12.0 Xml;HDR=YES;""")
        koneksi_excel.Open()

        Dim query_excel As String = "SELECT * from [" & file & "$]"
        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(query_excel, koneksi_excel)
        Dim rd As OleDb.OleDbDataReader

        rd = cmd.ExecuteReader()

        Dim koneksi As New SqlClient.SqlConnection()
        Dim koneksidatabase As String = "server=DESKTOP-KJQ8PNO\SQLEXPRESS;database=otto;Integrated Security=True"
        koneksi.ConnectionString = koneksidatabase


        koneksi.Open()
        Dim da As New OleDb.OleDbDataAdapter
        Dim ds As New DataSet()
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        da = New OleDb.OleDbDataAdapter(query_excel, koneksi_excel)
        da.Fill(dt)


        Using bulkcopy As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy(koneksi)
            bulkcopy.DestinationTableName = file
            bulkcopy.BulkCopyTimeout = 600
            bulkcopy.WriteToServer(rd)
            rd.Close()


            MsgBox("Data uploaded to database", MsgBoxStyle.Information, "Uploaded")
            TextBox1.Text = ""
        End Using
    End Sub

解决方案

' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long"

I think there is a problem with this code
Dim query_excel As String = "SELECT * from [" & file & "

"

i tried all of this code on another pc and it works perfectly but on my pc it's getting that error, I don't know what did I wrong, I'm using visual basic 2010 professional edition, sql management server 2016 and microsoft office 2016

can anyone help me to figure what is wrong with the code ?

What I have tried:

<pre>
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If OpenFileDialog1.ShowDialog = DialogResult.OK Then
            TextBox1.Text = OpenFileDialog1.FileName
            file = System.IO.Path.GetFileNameWithoutExtension(TextBox1.Text)

        End If

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim koneksi_excel As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ace.OLEDB.12.0;Data Source='" & TextBox1.Text & "';Extended Properties=""Excel 12.0 Xml;HDR=YES;""")
        koneksi_excel.Open()

        Dim query_excel As String = "SELECT * from [" & file & "

" Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(query_excel, koneksi_excel) Dim rd As OleDb.OleDbDataReader rd = cmd.ExecuteReader() Dim koneksi As New SqlClient.SqlConnection() Dim koneksidatabase As String = "server=DESKTOP-KJQ8PNO\SQLEXPRESS;database=otto;Integrated Security=True" koneksi.ConnectionString = koneksidatabase koneksi.Open() Dim da As New OleDb.OleDbDataAdapter Dim ds As New DataSet() Dim dt As New DataTable ds.Tables.Add(dt) da = New OleDb.OleDbDataAdapter(query_excel, koneksi_excel) da.Fill(dt) Using bulkcopy As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy(koneksi) bulkcopy.DestinationTableName = file bulkcopy.BulkCopyTimeout = 600 bulkcopy.WriteToServer(rd) rd.Close() MsgBox("Data uploaded to database", MsgBoxStyle.Information, "Uploaded") TextBox1.Text = "" End Using End Sub

这篇关于使用bulkcopy将excel文件导入我的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！