How do I import this txt file in SQL Server?

did u try using IMPORT/EXPORT wizard or SSIS components to load the flat file

import / export wizard.

Also this is SQL Server 2000 Enterprise.

Here is an article on your question
http://support.microsoft.com/kb/292588

seems like you have not set your DELIMITER properly. Make sure you set it to TAB in your IMPORT/EXPORT wizard Source

I am not using fixed field imports. I am using tab delimited, with line feed, import wizard works perfectly on the first 5.2mm records, then it hits one record that causes the whole thing to stop. How do I make it ignore the error? I think its something with the command line, or you do from query analyzer, but I have no idea how to do it.

you better try to use bcp utility, using bcp you can ignore till specified number of errors.

you can find bcp utility paramaters explanation here
http://msdn2.microsoft.com/en-us/library/ms162802.aspx

if you want to use format files for format file formats check
Format Files for Importing or Exporting Data
http://msdn2.microsoft.com/en-us/library/ms190393.aspx

appari,
Ok that is the solution I need, but I have one issue in making it work.... here is the command Im using:

bcp emaildata..masteroptins107mm in masteroptins107mm.txt -m 1000000 -r \n -U sa

so the question is, how do I tell bcp to use <linefeed> instead of \n... cuz \n in unix is different than \n in windows.


Thanks,
Rick

i think it is \r

appari,
Ok I ran this:

bcp email_data..masteroptins107mm in masteroptins107mm.txt -m 1000000 -U sa -f bcp.fmt

The table I defined masteroptins107mm has all columns of varchar(8000),.....

for some reason when I am importing using BCP I keep getting this error:

String Data, Right Truncation
SQLState = 22001, NATIVE_ERROR=0

what does that mean? how do I correct this... I have the columns set big just to avoid any truncation.

Thanks,
Rick

appari,
ok it repeats that error over and over an uncountable # of times... I see it get to about 6000 rows before that happens.... but then it sits there spitting out errors until it finishes.

Here is the bcp.fmt:

8.0
14
1       SQLCHAR       0       255     "\t"                      1     email         SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       50      "\t"                      2     IP            SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       50      "\t"                      3     DateStamp     SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       50      "\t"                      4     firstname     SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       50      "\t"                      5     lastname      SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       50      "\t"                      6     age           SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       0       255     "\t"                      7     address       SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR       0       255     "\t"                      8     city          SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR       0       255     "\t"                      9     state         SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR       0       50      "\t"                      10    zip           SQL_Latin1_General_CP1_CI_AS
11      SQLCHAR       0       25      "\t"                      11    gender        SQL_Latin1_General_CP1_CI_AS
12      SQLCHAR       0       25      "\t"                      12    birthday      SQL_Latin1_General_CP1_CI_AS
13      SQLCHAR       0       255     "\t"                      13    site          SQL_Latin1_General_CP1_CI_AS
14      SQLCHAR       0       50      "\r"                      14    phone         SQL_Latin1_General_CP1_CI_AS


Here is the sample, fields are tab delimited, and rows are linefeed terminated:

email      IP      DateStamp      firstname      lastname      age      address      city      state      zip      gender      birthday      site      phone
BPINNOCK@TELVIDA.COM      24.29.109.6      May 18 2005 12:00AM      BRIAN      PINNOCK            10335 178TH ST      JAMAICA      NY      11433                  Naviant Network      


Thanks,
Rick

is this data you posted is from line 6001?
i want to see the row where bcp reports the first error.