Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home SQL Server SQL SERVER How to import Text/CSV file into table

SQL SERVER How to import Text/CSV file into table

Import data from Text File or CSV file into table is a popular task in software development area.The usual way to solve this is to use programming languague. For example, we can use CSharp, Java or anything else to read CSV file into many lines, parse string data of each lines, then insert this row into table.This is common approach way!
In SQL Server 2000 or later, it supports SQL statement to import data effectively and quickly. The SQL Statement I want to mention here is BULK INSERT.

Now, take a look at simple requirement as following
++ The input : CSV file

1,4rapid,dev,4rapiddev.com,20110501,[email protected].com
2,Hoan,Huynh,HoanHuynh,19830601,[email protected].com
3,Quynh,Nguyen,QuynhNguyen,19830701,[email protected].com

1,4rapid,dev,4rapiddev.com,20110501,[email protected] 2,Hoan,Huynh,HoanHuynh,19830601,[email protected] 3,Quynh,Nguyen,QuynhNguyen,19830701,[email protected]mail.com

CSV Member Info data

CSV Member Info data

++ The output : Data in CVS file are inserted into below table

Table MemberInfo

Table MemberInfo

Steps to import data for this sample

1. Create table MemberInfo with the below statement

CREATE TABLE MemberInfo
(
	ID		INT NOT NULL,
	FirstName	NVARCHAR(20) NOT NULL,
	LastName	NVARCHAR(20) NULL,
	FullName	NVARCHAR(50) NULL,
	Birthday        DATETIME NULL,
	Email		NVARCHAR(30) NULL
)

CREATE TABLE MemberInfo ( ID INT NOT NULL, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NULL, FullName NVARCHAR(50) NULL, Birthday DATETIME NULL, Email NVARCHAR(30) NULL )

2. Run BULK INSERT statement to import data into table MemberInfo

Please change your Text file path on your computer.

  BULK
INSERT MemberInfo
  FROM 'D:\4RapidDevDemo_Memeber.txt'  -- Text Path File
  WITH
 (
	FIELDTERMINATOR = ',', -- Field terminator
	ROWTERMINATOR = '\n'   -- Row terminator
 )

BULK INSERT MemberInfo FROM 'D:\4RapidDevDemo_Memeber.txt' -- Text Path File WITH ( FIELDTERMINATOR = ',', -- Field terminator ROWTERMINATOR = '\n' -- Row terminator )

3. Perform checking imported data :

SELECT  [ID]
       ,[FirstName]
       ,[LastName]
       ,[FullName]
       ,[Birthday]
       ,[Email]
  FROM [dbo].[MemberInfo]

SELECT [ID] ,[FirstName] ,[LastName] ,[FullName] ,[Birthday] ,[Email] FROM [dbo].[MemberInfo]

Table MemberInfo After Import

Table MemberInfo After Import

4. Copy full script here

USE [rapiddev.com]
GO 
-- Create imported table
CREATE TABLE MemberInfo
(
	ID		INT NOT NULL,
	FirstName	NVARCHAR(20) NOT NULL,
	LastName	NVARCHAR(20) NULL,
	FullName	NVARCHAR(50) NULL,
	Birthday        DATETIME NULL,
	Email		NVARCHAR(30) NULL
)
 
 
-- Perform importing data into table MemberInfo
GO 
BULK
INSERT MemberInfo
FROM 'D:\4RapidDevDemo_Memeber.txt'  -- Text Path File
WITH
(
	FIELDTERMINATOR = ',', -- Field terminator
	ROWTERMINATOR = '\n'   -- Row terminator
)
 
-- Check imported data
GO
SELECT  [ID]
       ,[FirstName]
       ,[LastName]
       ,[FullName]
       ,[Birthday]
       ,[Email]
  FROM [dbo].[MemberInfo]

Use [rapiddev.com] GO -- Create imported table CREATE TABLE MemberInfo ( ID INT NOT NULL, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NULL, FullName NVARCHAR(50) NULL, Birthday DATETIME NULL, Email NVARCHAR(30) NULL ) -- Perform importing data into table MemberInfo GO BULK INSERT MemberInfo FROM 'D:\4RapidDevDemo_Memeber.txt' -- Text Path File WITH ( FIELDTERMINATOR = ',', -- Field terminator ROWTERMINATOR = '\n' -- Row terminator ) -- Check imported data GO SELECT [ID] ,[FirstName] ,[LastName] ,[FullName] ,[Birthday] ,[Email] FROM [dbo].[MemberInfo]

Hope it helpful for you :)!

Mar 28, 2012quynhnguyen
Setup Free SSL Certificate For Testing On Development Environment In Windows IIS 7Visual Studio Set default web browser and change size of browser window
You Might Also Like:
  • Replace String With NText Or Text Data Type In MS SQL Server
  • PHP Connect To MS SQL Server
  • Export/Import Data From Local Database To Remote Database With MS SQL Server 2008
  • MSSQL Split Function returns table data
  • Save Table Structure Changes When Remote Access To SQL Server
  • MSSQL Export A Table Data To CSV
  • ASP.NET C# Export Data To CSV And Prompt Download
  • Import RSS From Blog To Facebook Failed
  • How To Install Compare Text Plugin In Notepad Plus Plus
  • How To Compare The Differences Between 2 Files (Text File)
quynhnguyen
9 years ago SQL ServerSQL Function, Store Procedure372
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
22,209 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
20,077 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
15,854 views
JQuery Allow only numeric characters or only alphabet characters in textbox
13,334 views
C# Read Json From URL And Parse/Deserialize Json
9,828 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Photo Editor App – How Good Is it?

  • The Best Way To Write An Essay – Creating A Good Essay
  • Free Online Photo Editor
  • Easy Tips For Writing An Essay
  • What Can I Expect From An Academic Essay Service?

Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (28)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (114)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development