SIGN UP MEMBER LOGIN:
   
ARTICLE

Comma Separated Values (CSV) from table column in the SQL Server2005

Posted by Sapna Malik Articles | SQL Server March 16, 2010
Tags: SQL Server
In this article, we will see how to use Comma Separated Values (CSV) from table column and text file load in the table in the SQL Server2005.
Reader Level:

In this article, we will see how to use Comma Separated Values (CSV) from table column and text file load in the table in the SQL Server2005.You can specify row and column delimiters, making it easy to import files in comma-separated values. A comma-separated values file contains the values in a table as a series of ASCII text lines organized so that each column value is separated by a comma from the next column's value and each row starts a new line.

Example of CSV:-

USE CeilInn3

GO

CREATE TABLE Student

(ID INT,

F_Name VARCHAR(50),

L_Name VARCHAR(50),

RollNo VARCHAR(50))

GO

USE CeilInn3;

GO

 

SELECT F_Name FROM Student

GO

SELECT SUBSTRING((SELECT ','+Student.F_Name

FROM Student

ORDER BY F_Name

FOR XML PATH ('')),3,3000) AS CSV

 

GO

 

Output:-1

CSV1.bmp

Create CSV file in drive C: with name Student.txt with following content. The location of the file is C:\Student.txt

Image of Textfile:-

 CSV2.bmp

Now run following script to load all the data from CSV to database table. I am trying to load text files into a table in SQL Server 2005 management studio using bulk insert. I am able to load the file into the table but the bulk insert process isn't identifying correct row delimiter. My text file has different number of columns for each row.

Example:-

USE CeilInn3

GO

BULK

INSERT Student

FROM 'c:\Student.txt'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

SELECT *FROM Student

GO

After load the textfile in the table,Show the output:-

 CSV3.bmp

 

Summary

In this article, you saw how to use Comma Separated Values (CSV) in the SQL Server2005.

 

Login to add your contents and source code to this article
This Feature is Sponsored By
post comment
COMMENT USING