Hello:

Can anyone help me with this upload.  I have a non-delimitted text file that
i need to upload in  a table.  How do i separate the fields?

Junior in SQL SERVER!

thanks

mx

If by non-delimitted  you mean fixed length you can use DTS for that
try this site (http://www.sqldts.com) to find tutorials


http://sqlservercode.blogspot.com/

The two ways that I know of are

-Use DTS (or SSIS in 2005), specify a text file data source, and use the
graphical wizards to specify a non-delimited file and set the fixed-column
lengths.

-Use the bcp utility, like
bcp [YourDB].dbo.[YourTable] in "c:\yourfile.txt" -T

When prompted to enter the formatting details for each field, enter char for
data type, 0 for prefix length, enter the character width of the fixed fields
in your text file, and enter [none] for a terminator. Repeat for all columns
in the text file, but at the last column specify the lenght of the column and
a terminator of \n.

Show quoteHide quote

mx (m*@discussions.microsoft.com) writes:
If you use BCP or BULK INSERT, you will need to use a format file. Here is a
sample format file (indentation is for format of the post, all text must
start in column 1):

  8.0
  4
  1 SQLCHAR 0 20 "" 1   1 col1 ""
  2 SQLCHAR 0 0  "\t"   2 col2 ""
  3 SQLCHAR 0 50 ""     3 col3 ""
  4 SQLCHAR 0 0  "\r\n" 0 ""   ""

The first two lines state the version of the format file, and then
how many fields there are in the file. Remaining lines describes the
fields.

First column is the field number in the file, starting with 1.

Second column is the data type in the file. This is always SQLCHAR for
a text file, or SQLNCHAR it it is a Unicode file.

Third column is the prefix length. This field applies to binary formats
only. (The field first has 1, 2 or 4 bytes that gives the length of
the field.)

Fourth column is one that interest you. This column gives the fixed
length of the field.

Fifth column gives a delimiter that terminates the field.

Sixth column maps the field to a database column. 0 means that field
is not stored. 1 is the first column etc.

Seventh column can be used for the column name, but it is ignored by
bulk load.

Eighth column finally, is the collation.

In this example the first and third fields are fixed-length, 20 and 50
characters. But the second and third fields are separated by a tab.
In the example I have defined end-of-line as a field of its own.

Notice that as far as bulk load is concerned, CRLF are just two
characters, and the record separator is just the terminator for the
last field. This permits for embedded newlines in data, but also makes
BCP somewhat inflexible. (It can be impossible to make it ignore headers.)

An alternative is to use DTS, but I have never used that, so someone
else will have to explain that part.

Column X is same, column Y is different?
Imbedded subroutine prefix error
NULL in Joins
Memory problems using Sql Server 2000
multiple inserts in transaction