Importing Flat file with Fixed-length columns

Question

Importing Flat file with Fixed-length columns

Paul McCurdy

Ten Centuries

Points: 1377
More actions
April 1, 2004 at 11:25 am

#65050

Is there any easy way of importing a very large flat file, which has columns defined by their length, not with a delimiter of some sort.
I've used DTS regularly to import delimited files, but this is a first. And I can't seem to find my way through either the DTS Wizard or the Books Online to get a solution.

Thanks!

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

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

In the DTS Designer add a text file connection, locate the file, then hit the properties button, select fixed field, hit next and check that the markers are in the correct places for the fields.

Shamless self promotion - read my blog http://sirsql.net

Paul McCurdy Ten Centuries Points: 1377 More actions · Answer 2

Paul McCurdy

Ten Centuries

Points: 1377

April 1, 2004 at 11:44 am

#501505

Thanks - it works!

Larissa-136281 SSC Veteran Points: 292 More actions · Answer 3

Bulk insert is better for large files.

In Bulk Insert Task select use Format File and then define formatting with wizard the same way as for text file.

Coach James Ten Centuries Points: 1272 More actions · Answer 4

For future reference & a little bit of old school:

create <tablename>(<fieldname> Nvarchar(1000))

grant all on <tablename> to public

EXEC master..xp_cmdshell "bcp DB..<tablename> in C:\<filename> /U<user> /P<password> /n"

Now you have all your data (batch loaded, 100,000 records per second) in a table with one record&field for each row...

You read each row using a fetch cursor & parse out your fixed data using the substring(<fieldname>,start,end) for each field...

and put it where ever you want...

Hope this helps!

I use this for importing data from all kinds of sources & have several udf's & SP's which are called from within my application website in populating our database for all sorts of requirements!

Who needs a wizard, anyway?

Regards,

Coach James

paul2003lopez Newbie Points: 5 More actions · Answer 5

Coach:

Can you give an example? I have a flat file import coming in with fixed column lengths (huge amount of data). The column lengths would always be the same. I thought about writing a vb.net application to prepare a CSV file, but bcp looks more efficient. I lost you on the fetch cursor part.

Thanks!

Coach James Ten Centuries Points: 1272 More actions · Answer 6

Check out the syntax on BCP

I use it for uploading raw data files with only one field, per record terminated by /n (carrage return) and parse out a 80 char string (location specific data) by reading this uploaded table from within a stored procedure and then parse it using substring(s,e)...

BCP can also read any number of fields per record by specifiing your "field teminator" (such as tab, pipe, or what ever your incoming file supports)...

BCP is a command line import/export utility; and with it you can unload and load in SQL tables; this is a utility which is called by other SQL products which import and export data!

Regards,

Coach James