×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

here is an easy

As u said u know how to import manually from SQL Server , I don't want to repeat it again. what is I want to say is that when you use the DTS and go to the "when" and "save" page, check the save box and save the DTS package as Visual Basic File, then you get a .pas file which is a Visual Basic Module file, it contains the code using DTS object to import the data as u wanted. then u can use those codes wherever u like, u can set and change the option such as the Qualifier as below:
oConnection.ConnectionProperties("Text Qualifier") = "'"

The difference to your code is that here it doesn't reference the SQLDMO object but the DTS object .
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / urgent:I tried to import .csv file into SQL Server database in VB code (SQLDMO)
    the data format is :
    'ID',' username'
    '001','name001'
    '002','name002'

    How can I get ride of single quote?

    Thank you for your help!
    • replace or trim
      • it's bulkcopy,I do not want to import line by line
        • sorry, not trim, should be LEFT RIGHT or MID. :-)
    • 应该是不用自己去吧,直接读入就可以吧
      • this is what I want , but how can I set the property to get rid of it?I know if I import manually from SQL Server, I can set the text qualifier to be single quote
    • mid(fieldnvalue, 2, len(fieldvalue)-2)
      • 那我的就不对了。我原来问过一个倒入数据的oracle的问题。#634487
    • Here is the code I have, How can I change it get what I want,thanks again
      本文发表在 rolia.net 枫下论坛Public Function BCP(ByVal Server As String, ByVal UserName As _
      String, ByVal Password As String, ByVal Database As String, _
      ByVal Table As String, ByVal FileName As String, _
      Optional Import As Boolean) As Boolean

      '**************************************************************
      'PURPOSE: Do a SQL Server BulkCopy
      'ASSUMPTIONS: You understand what a bulk copy is; otherwise
      ' consult SQLServer books online
      'PARAMETERS: Server: ServerName
      ' UserName: UserName for Server
      ' Password: Password for Server
      ' Database: Name of Database
      ' Table: Table Name or SQL Statement for BCP
      ' FileName: File Name to Import from or Export To
      ' Import: If set to true, works as an import
      ' ("in") Otherwise, defaults to export
      ' ("out")
      'RETURNS: True if successful, false otherwise
      'EXAMPLE:
      'BCP "MySQLServer", "username", "pwd", "MyDataBase", "MyTable", _
      "C:\MyTable.out"
      '
      'Bulk Copies all rows of MyTable in MyDatabase on MySQLServer
      'to a file named C:\MyTable.out
      'REQUIRES: A reference to Microsoft SQLDMO Object Library
      'LIMITATIONS: -- Assumes SQL Server Authentication as opposed
      ' to NT integrated authentication.
      '
      ' -- Only allows full tables to be
      ' imported/exported i.e., you cannot specify
      ' where criteria
      '
      ' -- Uses mostly default options of bcp.
      ' If you want to set more options, the BulkCopy
      ' object permits this. Refer to documentation or
      ' object browser
      '***********************************************************

      Dim objServer As New SQLDMO.SQLServer
      Dim objBCP As New SQLDMO.BulkCopy
      Dim objDB As SQLDMO.Database




      'if file doesn't exist and it's an import,
      'don't waste time
      If Import = True And Dir(FileName) = "" Then Exit Function

      On Error GoTo ErrorHandler
      objServer.Connect Server, UserName, Password
      objServer.EnableBcp = True

      Set objDB = objServer.Databases(Database)

      With objBCP
      .DataFilePath = FileName
      .DataFileType = SQLDMODataFile_UseFormatFile



      'Below speeds things up
      'but does not log the bulk copy operation
      'comment out if this is not what you
      'desire
      .UseBulkCopyOption = True
      End With

      If Import Then
      objDB.Tables(Table).ImportData objBCP
      Else
      objDB.Tables(Table).ExportData objBCP
      End If

      BCP = True

      ErrorHandler:
      Set objBCP = Nothing
      Set objServer = Nothing
      End Function更多精彩文章及讨论,请光临枫下论坛 rolia.net
      • May be the only way is to remove single quote from csv file and save as another temp file, use that temp file to do bulk insert.
        A simple way is to Use "','" as fieldterminator and "'/n" as rowterminator, but this won't remove the first single quote.
      • objDB.DataFileType=SQLDMODataFile_SpecialDelimitedChar objDB.ColumnDelimiter ="'"
        • 这个好象不大好用,因为如果字符串里有'的话,会被变成''的吧。应该是掐头去尾,替换中间的
    • here is an easy
      As u said u know how to import manually from SQL Server , I don't want to repeat it again. what is I want to say is that when you use the DTS and go to the "when" and "save" page, check the save box and save the DTS package as Visual Basic File, then you get a .pas file which is a Visual Basic Module file, it contains the code using DTS object to import the data as u wanted. then u can use those codes wherever u like, u can set and change the option such as the Qualifier as below:
      oConnection.ConnectionProperties("Text Qualifier") = "'"

      The difference to your code is that here it doesn't reference the SQLDMO object but the DTS object .
      • GOOD
    • Thank you for your help, I find the way it may works by using store procedure
      SET QUOTED_IDENTIFIER OFF