BULK INSERT with format file

Hi all,
I have a fixed width text file that I need to import into SQL Server. The
file is very wide and extremely long, so I dont want to go through and
manually create the columns in DTS. I do however want to try and use BULK
INSERT to copy the records into my table. I thought the only way would be t
o
create a format file based on my fixed width text file so it could read the
appropriate columns. The problem is I have never done this and cant seem to
get it working. I guess I need to know if I can use BULK INSERT with a
format file to do this operation or I have to manually size out the columns?
The error I keep getting is some kind of EOF message. I have no idea with I
am creating the format file correctly either, but I made it look exactly wha
t
was on BOL. Thanks in advance for anyones help!!!!Post a few rows of the file, the format file you have created, and the error
msg you are getting...
"Andre" wrote:

> Hi all,
> I have a fixed width text file that I need to import into SQL Server. The
> file is very wide and extremely long, so I dont want to go through and
> manually create the columns in DTS. I do however want to try and use BULK
> INSERT to copy the records into my table. I thought the only way would be
to
> create a format file based on my fixed width text file so it could read th
e
> appropriate columns. The problem is I have never done this and cant seem
to
> get it working. I guess I need to know if I can use BULK INSERT with a
> format file to do this operation or I have to manually size out the column
s?
> The error I keep getting is some kind of EOF message. I have no idea with
I
> am creating the format file correctly either, but I made it look exactly w
hat
> was on BOL. Thanks in advance for anyones help!!!!|||longkimber dba qwestcsql
lessdavid dba qwestcsql
rondtimme dba qwestcsql
8.0
5
1 SQLCHAR 0 4 "\0" 1 lastname SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 5 "\0" 2 firstname SQL_Latin1_General_CP1_CI_A
S
3 SQLCHAR 0 4 "\0" 3 type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 8 "\0" 4 company SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 3 "\r\n" 5 code SQL_Latin1_General_CP1_CI_AS
"CBretana" wrote:
> Post a few rows of the file, the format file you have created, and the err
or
> msg you are getting...
> "Andre" wrote:
>|||OK, You said this was su[pposed to be a "Fixed Width" data file, if so,
then there ARE NO field terminators, enter an empty string "" for the
terminators in all but the last field. Second, The BCP automatically adds
the Carriage Return, when you specify the new Line... (\n) so not specify
(\r\n) or you will get two carriage returns...
Finally, the three data rows you passed DO NOT have the same number of
characters in them... a fixed width file MUST have exactly the same number
of characters (BYTES) In each row... R U Sure you have a fixed width file '
"Andre" wrote:
> longkimber dba qwestcsql
> lessdavid dba qwestcsql
> rondtimme dba qwestcsql
> 8.0
> 5
> 1 SQLCHAR 0 4 "\0" 1 lastname SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 5 "\0" 2 firstname SQL_Latin1_General_CP1_CI_A
S
> 3 SQLCHAR 0 4 "\0" 3 type SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 0 8 "\0" 4 company SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 0 3 "\r\n" 5 code SQL_Latin1_General_CP1_CI_AS
>
> "CBretana" wrote:
>|||Oh I was typing in the data and added a character by accident. Yes there ar
e
no field terminators. I was giving a file that looks this like with a data
dictionary with how long each field should be. OK, I will do the empty
string, but what do I put for the last line? Also, I cant write out the
entire error message, but it says at the end some kind of EOF error message.
If you give me your email I can take a screen shot and send it to you.
Thanks for all your assistance.
"CBretana" wrote:
> OK, You said this was su[pposed to be a "Fixed Width" data file, if so,
> then there ARE NO field terminators, enter an empty string "" for the
> terminators in all but the last field. Second, The BCP automatically add
s
> the Carriage Return, when you specify the new Line... (\n) so not specify
> (\r\n) or you will get two carriage returns...
> Finally, the three data rows you passed DO NOT have the same number of
> characters in them... a fixed width file MUST have exactly the same numbe
r
> of characters (BYTES) In each row... R U Sure you have a fixed width file
'
> "Andre" wrote:
>|||Yes, send me the actual file, or if it's too big, the first thousand lines o
r
so... And the format file, and the error msg screen shot...
Send to cbretana@.areteind.com
"Andre" wrote:
> Oh I was typing in the data and added a character by accident. Yes there
are
> no field terminators. I was giving a file that looks this like with a dat
a
> dictionary with how long each field should be. OK, I will do the empty
> string, but what do I put for the last line? Also, I cant write out the
> entire error message, but it says at the end some kind of EOF error messag
e.
> If you give me your email I can take a screen shot and send it to you.
> Thanks for all your assistance.
> "CBretana" wrote:
>|||In addition to the other poster's comments there is one gotcha with BULK
INSERT and format files. Your format file must contain a hard return at the
end or else you will get an error. This error is different depending on the
format version, but it often makes no sense.
So if you are editing your format file in notepad go to the end and hit "Ent
er."
Seriously.
> longkimber dba qwestcsql
> lessdavid dba qwestcsql
> rondtimme dba qwestcsql
> 8.0
> 5
> 1 SQLCHAR 0 4 "\0" 1 lastname SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 5 "\0" 2 firstname SQL_Latin1_General_CP1_CI_A
S
> 3 SQLCHAR 0 4 "\0" 3 type SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 0 8 "\0" 4 company SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 0 3 "\r\n" 5 code SQL_Latin1_General_CP1_CI_AS
> "CBretana" wrote:
>|||There is a shortcut to creating format files that I discovered and have used
religiously ever since.
Create a dummy DTS package and create a "Bulk Insert" task on the package.
On the properties page for the task, select the table (you will need a SQL
connection task first), then click "Use Format File" and then click the
Generate button. Browse to your input file and enter a name for the format
file. Then click next and follow the prompts. You will have to indicate
where the fields end (same dialogs as when importing a flat file to Excel).
After you finish the 'generate' dialogs...the format file will be created
and you can delete the Bulk Insert icon from the DTS package or just close
DTS without saving the package at all.
At this point, if you need to add, modify or delete fields...it is easy to
do manually because all the goofy formatting requirements of this file are
taken care of.
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:CC4A24C5-D822-4FE2-A724-58D094D298AE@.microsoft.com...
> Hi all,
> I have a fixed width text file that I need to import into SQL Server. The
> file is very wide and extremely long, so I dont want to go through and
> manually create the columns in DTS. I do however want to try and use BULK
> INSERT to copy the records into my table. I thought the only way would be
to
> create a format file based on my fixed width text file so it could read
the
> appropriate columns. The problem is I have never done this and cant seem
to
> get it working. I guess I need to know if I can use BULK INSERT with a
> format file to do this operation or I have to manually size out the
columns?
> The error I keep getting is some kind of EOF message. I have no idea with
I
> am creating the format file correctly either, but I made it look exactly
what
> was on BOL. Thanks in advance for anyones help!!!!