How to import text files into tables automatically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

How to import text files into tables automatically

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2008-11-06 : 14:05:20

Hello All,

I need help in importing text files into tables in a SQL Server 2005 database. The text files are located in a specified location and I want the files to be imported automatically does anyone know how to accomplish this task?

Sample of the script to create the table:

USE [SAMPLE]
GO
/****** Object: Table [dbo].[SJA_INFO] Script Date: 11/05/2008 18:54:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SJA_INFO](
[SJA_ID] [int] NOT NULL,
[COLOR] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SHAPE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Sample of text file which will be inserted into the sample table:

text file 1:

SJA SUMMARY

SJA ID: 293

COLOR: BLUE
SHAPE: SQUARE
DESCRIPTION: THIS IS A TEST

text file 2:

SJA SUMMARY

SJA ID: 1034

COLOR: RED
SHAPE: CIRCLE
DESCRIPTION: THIS IS A TEST

Any assistance would be appreciated. Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-06 : 14:51:39

Create SSIS package with flat file source and OleDB destination and schedule it through SQL Agent Jobs. Use Foreachloop container to loop the folder where your files reside and import whenver it sees file .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources