[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I import this txt file in SQL Server?

Posted on 2007-08-07
13
Medium Priority
?
250 Views
Last Modified: 2008-02-01
Hello,
I have a large txt file (107 million rows), that has fields that are tab delimited, and lines that are linefeed delimited. It is from MySQL. Anyways, I try to import this file into SQL Server through the Enterprise Manager, and nada, it quits out with a too many columns found error on row 5,212,342. How do I import this file into SQL Server, and have it ignore any errors that could occur?

Please provide me with an example on how to do this.

Worth 500 points.

Thanks,
Rick
0
Comment
Question by:richardsimnett
  • 6
  • 5
  • 2
13 Comments
 
LVL 8

Expert Comment

by:k_rasuri
ID: 19650226
did u try using IMPORT/EXPORT wizard or SSIS components to load the flat file
0
 

Author Comment

by:richardsimnett
ID: 19650379
import / export wizard.
0
 

Author Comment

by:richardsimnett
ID: 19650380
Also this is SQL Server 2000 Enterprise.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 8

Expert Comment

by:k_rasuri
ID: 19650455
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
0
 

Author Comment

by:richardsimnett
ID: 19650526
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.
0
 
LVL 39

Expert Comment

by:appari
ID: 19650718
you better try to use bcp utility, using bcp you can ignore till specified number of errors.
0
 
LVL 39

Expert Comment

by:appari
ID: 19650726
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
0
 

Author Comment

by:richardsimnett
ID: 19650969
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
0
 
LVL 39

Expert Comment

by:appari
ID: 19650978

i think it is \r
0
 

Author Comment

by:richardsimnett
ID: 19651400
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
0
 
LVL 39

Accepted Solution

by:
appari earned 1500 total points
ID: 19651456
when you run the bcp command you must be getting message with current row number. copy that record from your text file and check the data.

and can you post bcp.fmt file and a sample record from the text file here?
0
 

Author Comment

by:richardsimnett
ID: 19651566
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

0
 
LVL 39

Expert Comment

by:appari
ID: 19651620
is this data you posted is from line 6001?
i want to see the row where bcp reports the first error.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question