Bulk Insert Question

I need to move some data from an ascii file to a database. I want to use
BULK INSERT. The problem I have is there is no field seperation in the
ascii file. For example, columns 1-5 constitute a number, 6-28 constitute a
description, etc. Is there a way to use BULK INSERT without having to put a
comma or something between each field of data. Below is a small example of
data :
item description cost retail
12345thisistheitemdescription00245903599On Fri, 25 Feb 2005 16:13:13 -0500, sqlnewbie wrote:

>I need to move some data from an ascii file to a database. I want to use
>BULK INSERT. The problem I have is there is no field seperation in the
>ascii file. For example, columns 1-5 constitute a number, 6-28 constitute
a
>description, etc. Is there a way to use BULK INSERT without having to put
a
>comma or something between each field of data. Below is a small example of
>data :
>item description cost retail
> 12345thisistheitemdescription00245903599
>
Hi sqlnewbie,
I think you can do this with a formatfile. The easiest way to do this,
is to first run the bcp utility from a DOS prompt, answering all
questions and saving the information in a format file. Then, use a text
editor to check the contents of the format file and tweak it as needed.
Finally, use the FORMATFILE option of the BULK INSERT to specify this
format file for your date import.
Check out the subjects "bcp Utility (overview)" and "Using Format Files"
in Books Online for more information.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks, I'll play around with it...
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:g77v11dgleakllnsplfl2aacookufkbcum@.
4ax.com...
> On Fri, 25 Feb 2005 16:13:13 -0500, sqlnewbie wrote:
>
> Hi sqlnewbie,
> I think you can do this with a formatfile. The easiest way to do this,
> is to first run the bcp utility from a DOS prompt, answering all
> questions and saving the information in a format file. Then, use a text
> editor to check the contents of the format file and tweak it as needed.
> Finally, use the FORMATFILE option of the BULK INSERT to specify this
> format file for your date import.
> Check out the subjects "bcp Utility (overview)" and "Using Format Files"
> in Books Online for more information.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Use a format file. Here is part of the description:
If specifying a prefix length of 0 and no terminator, bcp allocates the
maximum amount of space shown in the field length prompt because this is the
maximum space that may be needed for the data type in question. The field is
treated as if it were of fixed length so that it is possible to determine
where one field ends and the next begins.
RLF
"sqlnewbie" <1@.1.com> wrote in message
news:uGbyT73GFHA.2736@.TK2MSFTNGP09.phx.gbl...
>I need to move some data from an ascii file to a database. I want to use
>BULK INSERT. The problem I have is there is no field seperation in the
>ascii file. For example, columns 1-5 constitute a number, 6-28 constitute
>a description, etc. Is there a way to use BULK INSERT without having to
>put a comma or something between each field of data. Below is a small
>example of data :
> item description cost retail
> 12345thisistheitemdescription00245903599
>