Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
|
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 26th, 2005, 03:49 AM
Registered User
 
Join Date: Sep 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default DTS Fixed Width File Import

Hi,

I am really new to DTS so please excuse if you find my query to simple.

I have a text file which has lots of records which I am trying to import into a SQL table using a DTS package.

The format of the text file is like this

Field1 ,Field2 ,Field3 ,Field4
Field1 ,Field2 ,Field3 ,Field4
Field1 ,Field2 ,Field3 ,Field4
Field1 ,Field2 ,Field3 ,Field4

As you can see, the text is properly organized but I am facing the following problem

1) [u]If I try to import it as a comma delimited file</u>

   Some of the fields inside the text have comma within them. For example field1 in row 1 is like AA,BB,CC and field1 in row 2 is like FFFF. This causes a problem as DTS puts AA and FFFF in one column and BB and Field2 in the next column and so on. Effectively thus, the no. of columns keep increasing and I end up getting an error
  "non-white spaces have been found at the end of last column"

2) [u]If I try to import it is a fixed width file </u>

 It does extract properly but also places the comma along with the fields in the table. How do I get rid of them?

 I will appreciate if someone can give me a solution fix for both the above methods or atleast one of the above.

Thanks a lot


 
Old September 26th, 2005, 04:12 AM
Registered User
 
Join Date: Feb 2005
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Modify method 2 - create an import table with a one character field between each data field to accept the commas. (e.g. F1, C1, F2, C2 etc). After import, use insert SQL or a DTS transformation to move the imported data to your target table.

 
Old September 26th, 2005, 04:46 AM
Registered User
 
Join Date: Sep 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks a lot abob. Your solution seems to work. Will post again if I face more difficulties. Thanks again

 
Old September 26th, 2005, 09:12 AM
Authorized User
 
Join Date: Sep 2005
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Another solution is creating columns for the comma fields in the file properties, your nest step would be just to map the fields you need in the transformation task
This way you eliminate the extra step of a temp table

http://sqlservercode.blogspot.com/





Similar Threads
Thread Thread Starter Forum Replies Last Post
Fixed Width text file to MSDE with C# mgudmund C# 2005 3 November 9th, 2007 05:09 AM
Import a fixed width txt file to access subhanak Access VBA 12 May 5th, 2006 05:35 AM
Automate import (fixed width) U.N.C.L.E. Access VBA 0 February 3rd, 2006 06:55 PM
Best way to setup DTS text file import pjmair SQL Server DTS 7 September 20th, 2004 09:52 AM
import text file to SQL Server DB via DTS pabloron SQL Server 2000 0 July 31st, 2003 06:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.