How to read text file into SQL Table

Question

How to read text file into SQL Table

BOR15K

SSCertifiable

Points: 5894
More actions
February 21, 2011 at 9:48 am

#235429

Back in Oracle 10, I had developed a solution based on treating a text file as a SQL Table. Now I have a customer, requiring the same solution, but for SQL (2008).
Any ideas, please?
THANKS!
A way to do it in Oracle was easy:
create table alert_log
(txt_line varchar2(512))
organization external
(type ORACLE_LOADER
default directory log_dir
access parameters (records delimited by newline
fields
(txt_line char(512)))
location (’alert_orcl.log’)
);

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I guess it depends on the type and structure of text in the file.

I generally use OpenRowset for querying text into SQL Server. It's pretty straightforward on structured data or XML, but it can be tricky if the data isn't reasonably well-structured.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

Thanks for the quick reply.

It says that OpenRowset is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB.

The file will be always deployed on a same server and it is quite basic text file (also could be XML if required) of a standard structure, i.e.

from position X1 to X2 - Code

from position X2+1 to X3 - Name

from position X3+1 to X4 - Description

and so on....

Thanks again for your reply

GSquared SSC Guru Points: 260824 More actions · Answer 3

You could definitely set up a linked server for that.

I use OpenRowset for that kind of thing, instead. A definition file, that maps out the columns based on the lengths you mentioned, will make it very efficient to query.

Either way works.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sivaj2k Ten Centuries Points: 1188 More actions · Answer 4

Hi

It is very easy to load data from text,csv etc to oracle tables using External Table concept.

You load external data (.txt,.csv etc) into sql table in following way.

step1:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test_load]') AND type in (N'U'))

DROP TABLE test_load

create table test_load

(

empno int,

ename varchar(50),

sal int,

dob smalldatetime

)

step2:

-- sample text file (c:\emp.txt) -- save it as c:\emp.txt

1,scott,6000.35,19750211

2,miller,10000.00,19801120

3,john,2150.50,19850830

4,smith,15000.80,19700101

5,tiger,16755.22,19770323

step3:

BULK

INSERT test_load

FROM 'c:\emp.txt'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

Step4:

select * from test_load

I hope this will help you.

Regards

Siva Kumar J

BOR15K SSCertifiable Points: 5894 More actions · Answer 5

Hi Siva,

Not sure I have understood your answer - I know how to do it in Oracle (to be honest, I wouldn't post

Oracle question on SQL forum 😀 but on Oracle's one). My question was how to do it with SQL Server.

But still, thanks for your efforts.

sivaj2k Ten Centuries Points: 1188 More actions · Answer 6

I given you the solution how to extract data from text file to Sql server Table. This code is purely Sql Server.:w00t:

Siva

BOR15K SSCertifiable Points: 5894 More actions · Answer 7

BOR15K

SSCertifiable

Points: 5894

March 3, 2011 at 7:38 am

#1294019

Then I will take a look in your steps.

Thanks