none
SQLBulkCopy Class to insert data from fixed-width text files. RRS feed

  • Question

  • It appears that the SQLBulkCopy is an evolution from BCP for inserting lots of data into SQL Server, but I'm confused by the fact that there isnt an obvious way to use it with text files..?  (historically a pretty core use of BCP and still well used/needed)

     

    Does anyone know how to use this class, to import large text files into SQL server..?

     

    looking at it it seems you need to have already read the file into memory/ADO classes (dataTable/Reader) to be able to use the SQLBulkCopy class - which defeats the purpose of using it (i.e. performance) ??

     

    I cant beleive MS would have created this class without having thought of that..?  there must be a way . .?

     

    anyone know..?

     

     

    ta..

     

    Jerome.

     

     

     

     

     

     

     

    Tuesday, February 26, 2008 10:57 AM

All replies

  • I believe in this case you could use SQL Server Integration Services (former DTS), which allow fast data transformation between data sources

    Tuesday, February 26, 2008 11:00 AM
    Moderator
  • I don't know what the format of your text file is (comma delimited?) but you should be able to read it into a DataTable or DataReader using Jet OLEDB and the Text ISAM driver.

     

    Code Snippet

    Dim ConnectionString As String

     

    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=" & "E:\My Documents\TextFiles" & ";" & _

    "Extended Properties=""Text;HDR=Yes"""

     

    Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)

    TextConnection.Open()

     

    Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM CSV#txt", TextConnection)

     

    Dim ds As New DataSet("TextFiles")

    da.Fill(ds, "CSV")

     

     

    Tuesday, February 26, 2008 1:49 PM
  • hi yes..   thanks.. .I've done this aready...  

     

     I was assuming that this approach would be much slower than BCP... as you'd have to load the data through JET first and then insert into into the database, as opposed to BCP which would read it straight out of the file and into the sql server database.   I was hoping there was a way to tell the SQLBulkCopy class to source the data from the the fixed width text files (i suppose somethng like a schema definition or disconnected recordset that allowed the fixed-width file to be read directly from the source file.)    perhaps there is no way..   seemed strange for MS to make 'new BCP' less functional....!?

     

    thanks for your help though..

     

    if anyone else has any thoughts/knows anything it would be much appreciated..?

     

    thanks,

     

    Jerome.

     

    Tuesday, February 26, 2008 8:50 PM
  • Not sure how it would be less functional. SQLBulkCopy will support any data source you can read into a DataTable or DataReader. BCP is rather limited in this respect. Sure it's probably faster, but then most command line tools typically are at the expense of functionality and flexibility.

     

    Other suggestions would be to create a linked server from SQL Server to the text file and then import, and, you should be able to import directly through Jet OLEDB, using a SQL statement, to a SQL Server table.

    Tuesday, February 26, 2008 10:28 PM
  • SqlBulkCopy is good if you are working with data in .NET already (in a DataSet or a reader) but not good for bulk copying in and out text files.  For this you should use the native bcp utility that ships with SQL Server.  If you are in a pinch and client does not have bcp utility, then SqlBulkCopy is ok solution but it's not going to meet the performance of native bcp.
    Wednesday, March 4, 2009 12:45 AM
    Moderator