Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

Import Data from Note Pad Files to Sql Server 2008 using PL-Sql.

Author: Zunnair

In This Simple Article I will Explain the way in which we can Import Data From simple note pad txt Files to Sql Server 2008 Database Table.

For Import Data we need First of All Format File. Format File is a file in which you explain your columns, their data Type and also their delaminate   Sign like  ‘-’.

 

After that we have to Create table in sql Server 2008 with respect to format File as describe in following PL-SQL Querry.

 

 

 

Create table Temp

{

[Column1] as Varchar(200),

[Column2] as Varchar(200),

[Column3] as varchar(200),

[Column4] as varchar(200)

}

 

After that Set each Column Data Type varchar because while importing txt File we don’t want errors related to data type conversions.

 

So now write the following final Query.

 

INSERT INTO [tblTemp]

                       ([Column1], [Column2], [Column3], [Column4])

 

SELECT    A.[Column1], A.[Column2], A.[Column3], A.[Column4]

FROM OPENROWSET (BULK 'D:\Test_Data.txt',

FORMATFILE = 'D:\Test_Data.fmt') AS A;

This query insert NotePad txt  file records to your database tables. This is very simple and easy Article, I hope you will understand this.

 

Article Comments
Hi,

Thanks itz so easy. But how to do the same in a EDI file?

Any suggestion, links are highly appreciated.

Regards,
Viji

Posted on 11/7/2008 12:16:03 AM by viji

hi

hi use your code but, it show massage

"Msg 4860, Level 16, State 1, Line 3
Cannot bulk load. The file "d:\a.txt" does not exist."


why
I dont know .

Posted on 5/12/2010 1:06:59 PM by murad

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< Import data From Excel File to Sql Server 2008 Using PL-SQL

Disclaimer - Privacy
© 2002-2017 DevASP.net