BULK INSERT from a text file without delimiters

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 2000 Forums

Import/Export (DTS) and Replication (2000)

BULK INSERT from a text file without delimiters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-05 : 08:12:04

Neeraj writes "I need to perform the BULK INSERT operation for a text file. The file would not be having delimiters to seperate the columns. But it would have the information in terms of number of characters which specify a column. For example we have the information that first 10 characters are FirstName column, the next 15 are LastName, the next 35 are Address and so on. Can I use Bulk Insert for this kind of file. If yes please tell me how and if no then please suggest any alternative approach to do it."

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-05 : 15:13:25

I'd choose smth like this:

create table temp (m varchar(8000))

bulk insert temp from 'D:\my.txt'

insert into mainTable

select substring(m,1,10), substring(m,11,15), substring(m,26,35), ... from temp

drop table temp

PaulRayner
Starting Member

3 Posts

Posted - 2005-01-08 : 14:27:46

If the columns are larger than 8000 chars or for speed and without the need for a temp table you can use BCP style FMT files to specify the layout (or use BCP in the same way).

For BULK INSERT :

BULK INSERT myTable
FROM 'c:\myInputFile.txt'
WITH (FORMATFILE = 'c:\myFormat.fmt')

For the 3 column example the format file would look like:

8.0
3
1 SQLCHAR 0 10 "" 1 FirstName SQL_Latin1_General_Cp1_CI_AS
2 SQLCHAR 0 15 "" 2 LastName SQL_Latin1_General_Cp1_CI_AS
3 SQLCHAR 0 35 "\n" 3 Address SQL_Latin1_General_Cp1_CI_AS

Lookup the BCP Utility and Format Files in the Books Online for a full description, anything that works for BCP works for BULK INSERT.
You didn't say how much data you have or if the lines have terminators I have assumed the lines have a single new line character at the end of the line but if it is a pure 'rectangular' file then take out the "\n".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources