Hi all,

I need to import the data from flat file to SQL 2000.
My flat file has fixed length but no delimeters.

For eg.

Firstname varchar(20)
Lastname varchar(10)
Age int

Pls help in resolving the issue...

Chandru

__________________
Thanks,
Chandra

You can do it one of two ways...

1. Import the whole line into a single varchar column in a staging table and use substring to split it...
2. Create a BCP format file to identify how to do the split (can be used with BCP or BulkInsert). See Books OnLine for more info on how to build the format file.

--Jeff Moden

Thanx for the reply.
It would be better to provide some eg in BCP/Bulk Insert so that i can work around the said problem.
Creating a staging table and then splitting is a tedious job, so i would prefer to go for BCP/Bulk Insert.

Chandru

I think everyone would prefer just going strait to bcp/bulk insert. Problem is if you have errors in your source data it simply fails and doesn't allow you to write code to fix it. In this very simple example you probably won't have any problems. But when things get more complex and you have bad data coming in BCP/Bulk insert is basically a pass/fail process if you get bad data for one row the whole load could fail. Hard to get bad data in your super simple example here but as complexity grows the easiest solution isn't always the best solution.

Then, because it is a fixed field format, you MUST create a format file whether you use BCP or BULK INSERT. Please refer to "bcp utility, format files, using format files" in Books Online... there's a bit too much than to write here. It's not difficult, just "bulky".

--Jeff Moden