Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2005 Flat File Import

Posted on 2006-07-10
14
Medium Priority
?
396 Views
Last Modified: 2008-01-09
I know this should be really simple but I am stumped.  I am trying to import a text file into a SQL table and I'm having problems.  

The original flat file is 38 columns of which I only need to import about 25.  On the advanced tab of the wizard, I delete out the columns I don't need and then finish the rest of the steps.  All of the column widths are set to NVARCHAR 50 except for six columns that I need to set to NVARCHAR 500.  The problem is that every time I try to finish the import it bombs out on those columns with truncation errors.  When I preview the text file after the deleted columns, it also looks like it kept the data but just shifted the column headers over.  

Any thoughts?

Thanks,

N
0
Comment
Question by:stretch73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +2
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17075277
Can you make the file or at least some sample data from the file available somewhere?
0
 

Author Comment

by:stretch73
ID: 17075772
Here is a sample of the data.  It's delimited by row by CRLF and by column by |.  Let me know if that works.


RBKZI1Q |33331111|10557866|JASHISFF                         |smith        |  |ASSISTANT I                                                                              |318|0005492|5492-GREATER NORTH BAY PREMIER|TMDABD..AA|415-451-2720      |415-151-2716      |Ø                     |Ø         |CA4-124-22-05   |sodabeh.kashi@yahoo.com                                   |SAN RAFAEL MAIN OFFICE                  |1000 4TH ST%SAN RAFAEL CA 94901-3121                                                                                                                                                                                            |SAN RAFAEL                    |CA|94901-3121  |smith, Brandi L.                               |SVP; PREMIER MARKET MANAGER                                                                             |b.campbell@yahoo.com                                 |            |     |Ø                 |A|5|TSXYM|2224        |Ø                   |UNITED STATES OF AMERICA                |F|DB|Y|AA600 |Ø
KLK918N |77778888|10557755|LUU                           |amy         |  |OPERATIONS REP                                                                               |318|0035122|MAILROOM WEEKEND              |FTOKLLN.AB|Ø                 |Ø                 |Ø                     |Ø         |CA9-725-21-13   |Ø                                                                 |Ø                                       |LOS ANGELES CA 90012-1514                                                                                                                                                                                                                     |LOS ANGELES                   |CA|90012-1514  |jones, Wendy L.                                   |OPERATIONS TEAM MANAGER                                                                                 |w.T.Gaona@yahoo.com                                   |            |     |Ø                 |A|5|64VXG|Ø           |Ø                   |UNITED STATES OF AMERICA                |F|DB|Y|OA601 |Ø
UC19198 |55554444|22455666|LINCOLN                       |THOMAS         |J |CASH SERVICES REP II                                                                                    |736|0022153|0202 HARTFORD MONEY CENTER CT |FTVVFCH.AC|160-716-1283      |Ø                 |Ø                     |Ø         |CT2-511-B2-01   |Ø                                                                 |Ø                                       |HARTFORD CT 06115                                                                                                                                                                                                                           |HARTFORD                      |CT|06115       |LeGe, Paul V.                                   |OPERATIONS MANAGER                                                                                      |v.legeytt@yahoo.com                                   |            |     |Ø                 |A|5|6D3BK|Ø           |Ø                   |UNITED STATES OF AMERICA                |F|DF|Y|OC601 |Ø
NB181MN |45454545|Ø       |FELTIONCAROLYN A.             |Ø              |Ø |CONTRACTOR                                                                                    |110|0007933|PHOTO ID                      |AFSHHS..ES|404-607-3604      |414-617-3386      |Ø                     |Ø         |GA2-002-01-01   |carofelton@yahoo.com                                |MIDTOWN CENTER                          | ST NE%ATLANTA GA 30308-1215                                                                                                                                                                                                       |ATLANTA                       |GA|30308-1215  |Ø                                                 |Ø                                                                                                       |Ø                                                                 |Ø           |     |Ø                 |A|Ø|D6YH6|Ø           |A.                  |UNITED STATES OF AMERICA                |O|WK|Y|Ø     |C
NBD3432 |65656565|11227000|BURKS                         |jones         |I |CNSLT - APPS PROG - CA                                                                              |318|0010323|ECS SYSTEMS                   |FKSBBG..AA|925-692-9356      |125-615-2979      |Ø                     |Ø         |CA4-701-09-05   |martha.burks@yahoo.com                                    |BUILDING A                              | GRANT ST%CONCORD CA 94520-2445                                                                                                                                                                                                       |CONCORD                       |CA|94520-2445  |gnalr, Kelley K.                                |VP; TECH MGR - APPS PROG                                                                                |Ihagerty@yahoo.com                                  |            |     |Ø                 |A|3|FYWQD|Ø           |Ø                   |UNITED STATES OF AMERICA                |F|DB|Y|TA018 |Ø
XBKW98C |14141414|10555628|DURAN                     |ann      |A |SERVICE REP                                                                                 |794|0000076|RIO CONSUMER TEAM 8           |FHRABCBDAH|505-462-4099      |501-412-4146      |Ø                     |Ø         |NM1-502-01-42   |patricia.anceau@yahoo.com                              |RIO RANCHO CONSUMER CALL CENTER         |4330 PICABO ST%RIO RANCHO NM 87144                                                                                                                                                                                             |RIO RANCHO                    |NM|87144       |Gamble, Lisa M.                                 |CUSTOMER SERVICE TEAM MANAGER                                                                           |Mgabaldon@yahoo.com                                 |            |     |Ø                 |A|5|YRCD2|Ø           |Ø                   |UNITED STATES OF AMERICA                |F|DB|Y|CA600 |Ø
NBy9414 |48484848|24134444|HENDERS                     |SHANE         |J |IMPROVEMENT CONSULTANT                                                                        |001|5790027|CRE LEARNING SALES - RETAIL AE|AEEFEJ..WC|518-512-1814      |818-512-8110      |Ø                     |Ø         |CA2-129-22-02   |sheyla.j.henderson@yahoo.com                              |GLENDALE MAIN OFFICE                    |GLENDALE MAIN OFFICE%345 N BRAND BLVD%GLENDALE CA 91203-2368                                                                                                                                                                                                   |GLENDALE                      |CA|91203-2368  |Ryyyd, Cynthia                                  |VP; PROGRAM DVLPMT MGR                                                                                  |CRErudolph@yahoo.com                                 |            |     |Ø                 |A|1|FXV2H|Ø           |Ø                   |UNITED STATES OF AMERICA                |F|DB|Y|HC018 |Ø
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17076934
I'm not sure what do you mean by " I delete out the columns I don't need " what you need to do is unmap the columsn that you do not need...

The failure is occuring because there is a field that is more than 50 in size... so some truncation will occur... try increasing the size (just for test sake) to 100...
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17078472
>>Here is a sample of the data. <<
Please post the structure of your target table and I will try and duplicate and suggest a solution.
0
 

Author Comment

by:stretch73
ID: 17080502
The table structure is pretty simple.  All fields are NVARCHAR(50) except columns 6, 16, 18, 22, and 24.  

I tried not mapping the fields but it threw an error saying that the columns were not part of the data flow and should be deleted.  
0
 

Author Comment

by:stretch73
ID: 17080542
Here's the error I get when I don't map the fields I don't want:

Warning 0x80047076: Data Flow Task: The output column "Column 0" (10) on output "Flat File Source Output" (2) and component "Source - CorpDir_txt" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
 (SQL Server Import and Export Wizard)



I get that for every field.
0
 

Author Comment

by:stretch73
ID: 17080701
I get that for every field I don't map
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17080712
Can you try it in Data Transformation services (DTS) sql 2000 to import this data?

and which version of sql 2005 you are using .

one more thing  create a temporary empty table and try to import data in that table make all the fields in that table nvarchar(1000) and see if you are getting any error.

if not check len(columnname) for each column and see which column is exceeding the limit.

Hope you understood.
0
 

Author Comment

by:stretch73
ID: 17080763
Okay, field 23 also needed to be blown out to NVARCHAR(500) and I needed to put the output in a new table.  It's running now but I'm pulling over 250k records.  I'm still getting the validation error, more of a suggestion really

Warning 0x80047076: Data Flow Task: The output column "Column 2" (16) on output "Flat File Source Output" (2) and component "Source - CorpDir_txt" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
 (SQL Server Import and Export Wizard)

so I'd like to know how to delete those columns.  I'm trying to schedule this to run weekly so the more streamlined the process the better.
0
 

Author Comment

by:stretch73
ID: 17080807
Here's the info on the SQL version I am using:

Microsoft SQL Server Management Studio      9.00.1399.00
Microsoft Analysis Services Client Tools      2005.090.1399.00
Microsoft Data Access Components (MDAC)      2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML                  2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer            6.0.2900.2180
Microsoft .NET Framework            2.0.50727.42
Operating System                  5.1.2600

0
 

Author Comment

by:stretch73
ID: 17089599
Couldn't get it to work.  I had to import the entire file and then just delete the columns out of the SQL table.  
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17089984
well if you are doing it frequently create a job with two steps
step 1 is the dts
step 2 is deletion
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 17125281
Closed, 200 points refunded.
Netminder
Site Admin
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

721 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