Best method for importing data from text file into MS SQL Server

performance sqlbulkcopy sql-server vb.net

Question

In need of your opinions. Currently developing an application in VB.NET.

I have a text file which contains more than one thousand rows. Each rows contains the data needed to be inserted into the database. A sample of a row is as follows:

0001--------SCOMNET--------0.100---0.105

At first glance, one might figured that each column was separated with a tab but actually each column was separated by blank spaces (I used '-' to denote as blank spaces because somehow could not get SO text editor to show the blank spaces).

The first column is defined by 

Substring(0, 12) which is the data [0001--------]

second column 

Substring(12, 12) in which the data is [SCOMNET-----]

third column is 

Substring(24, 8) in which the data is [---0.100]

and last column is 

Substring(32, 8) in which the data is [---0.105]

My initial though is to extract the lines for the text file and stored in as a list of strings, then while looping, do the separation of the each string item in the list with the SubString() function and insert it one by one until the end of the list. But this will no doubt takes time.

In my scenario, how can I take advantage of the SqlBulkCopy? Or if there is any other ways to approach this for a faster insert? Say;

  • open file
  • start loop
    • read line
    • separate each column in the line with substring
    • save in a DataTable
  • end loop
  • BCP(DataTable)
1
2
3/20/2015 1:05:55 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

This includes a method that may be a more efficient way of reading your text file.

    Sub readFixWidthTextFileIntoSqlTable()

    Dim sqlConn As New SqlConnection("Connection String Goes Here")
    sqlConn.Open()
    Dim sqlComm As New SqlCommand
    sqlComm.Connection = sqlConn
    sqlComm.CommandType = CommandType.Text
    sqlComm.CommandText = "INSERT INTO YourTableNameHere VALUES(@Field1, @Field2, @Field3, @Field4)"

    sqlComm.Parameters.Add("@Field1", SqlDbType.Text)
    sqlComm.Parameters.Add("@Field2", SqlDbType.Text)
    sqlComm.Parameters.Add("@Field3", SqlDbType.Text)
    sqlComm.Parameters.Add("@Field4", SqlDbType.Text)



    Using IFReader As New FileIO.TextFieldParser(FileNameWithPath)
        IFReader.TextFieldType = FileIO.FieldType.FixedWidth
        IFReader.SetFieldWidths(12, 12, 8, 8)

        While Not IFReader.EndOfData
            Dim fields As String() = IFReader.ReadFields

            sqlComm.Parameters("@Field1").Value = fields(0)
            sqlComm.Parameters("@Field2").Value = fields(1)
            sqlComm.Parameters("@Field3").Value = fields(2)
            sqlComm.Parameters("@Field4").Value = fields(3)
            sqlComm.ExecuteNonQuery()
        End While

    End Using

    sqlConn.Close()
End Sub
1
3/20/2015 5:05:56 PM

Related Questions

With the SqlBulkCopy class, you may import data from SQLite to SQL Server.

This may or may not solve your problem, but you probably want to use the SqlBulkCopyOptions to specify that you don't want it to generate new identity values....SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
sqlbulkcopy sqlite
asked by Adam Hughes

MS Access bulk import and insert into SQL Server

private void Synchronize() { SqlConnection con = new SqlConnection("Database=DesktopNotifier;Server=192.168.1.100\\sql2008;User=common;Password=k25#ap;"); con.Open(); SqlDataAdapter adap = new SqlDataAdapter("SELECT * FROM CustomerData", con); DataSet ds = new DataSet(); adap.Fill(ds, "CustomerData"); DataTable d...
asp.net c# ms-access sqlbulkcopy sql-server
asked by Sangam Malik

SQL Bulk Copy to SQL Server with Data Modifications in Excel

This works fine for me:... public ActionResult Create(HttpPostedFileBase file) { string strConnection = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString; //file upload path var fileName = Path.GetFileName(file.FileName); // store the file inside ~/App_Data/uploads folder var pat...
excel sqlbulkcopy sql-server
asked by Vegeta_77

import text file(500 MB) data into SQL Server

You're talking about text files, right. Because your code has fileuploadexcel......Anyway - if it is text, by far the easiest way is to use ...http://www.filehelpers.com...It supports fixed and delimited formats and is fast and easy to use....It has various errormodes, one of them allows you to do this...engine.ErrorManager.ErrorMode = ErrorMode.Sa...
asp.net c# sqlbulkcopy sql-server
asked by Pleun

SQL Server import faster than bulk import

I have just spent the last few weeks fighting with the optimizing a very large load myself. BULK INSERT is the fastest way, I found with BCP, as opposed to SSIS or TSQL Bulk Insert, but there are things you can do to tune this. ...Try raising/lowering the rows per batch setting, to move resource pressure between CPU and memory (higher will reduce C...
bulkinsert sqlbulkcopy sql-server
asked by Pete Carter

Best method to copy a file to the SQL Server machine

You can BCP blobs in and out of the database. Also, I found a reference to scripts for importing and exporting files as blobs which ...might be helpful....
sqlbulkcopy sqlclr sql-server
asked by Michael Gardner

Import CSV data into SQL Server

Use MySQL load data: ...LOAD DATA LOCAL INFILE 'path-to-/filename.csv' INTO TABLE `sql_tablename` CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES; ...The part ...optionally enclosed by '\"'..., or escape character and quote, will keep the data in the first column together for the first field....IGNORE 1 LI...
bulkinsert sqlbulkcopy sql-server sql-server-2008 sql-server-2008-r2
asked by motorbaby

Import SQL records from file into sql DB and changing type

Importing into SQL Server, you can right-click on the database and select Tasks > Import Data. This will open up the Import/Export Wizard which is a slimmed down version of the same functionality in SSIS....Once the Wizard is open, you can navigate the through the prompts and eventually get to a screen, Select Source Data, where you can click on th...
biginteger sql sqlbulkcopy sql-server
asked by rhholt

Importing user-friendly Excel sheet into a SQL Server table that has many foreign keys

I would be creating an SSIS package in this scenario. SSIS can read from Excel, and you can get it to query the database for the extra information to build a valid dataset that will not violate the FK constraints.
c# excel sqlbulkcopy sqldataadapter sql-server
asked by DeanOC

Importing Data in Parallel in SQL Server

Load the file path details into a tracking table...Create table FileListCollection TABLE (Id int identity(1,1), filepath VARCHAR(500), ThreadNo tinyint, isLoaded int)...DECLARE @FileListCollection TABLE (filepath VARCHAR(500)) DECLARE @folderpath NVARCHAR(500) DECLARE @cmd NVARCHAR(100) SET @folderpath = '' SET @cmd = 'dir ' + @folderpath + ' /b /...
bulkinsert bulk-load parallel-processing sqlbulkcopy sql-server
asked by Syed

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow