Problems importing TXT files using DTS

Hello,

I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)

I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.

Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.

What am I doing wrong? Should I be using something else than DTS?

Any suggestions are greatly appreciated.

Thanks,
JByou should create a transform data task and connect the text file to the destination though the task.
create the destination table in the xform task and map the data types to the new destinations.

ps while you are there change the column mappings from x individual column copies to 1 single thread it will increase your performance.|||Thanks for the response. I'm going to look into this today. If anyone has any other suggestions, I'd appreciate them too.|||Ruprect,

Come to find out that yesterday I was trying the exact thing you mentioned in your first post. I examined it a little more thorough this morning. The only thing different that I wasn't using 1 thread.

I have 94 columns worth of data. If I select the first 30 columns it will import the text and insert it into the table with appropriate column headings to perfection. The first 30 columns are all character fields.

At Column 31 the first integer field appears. It will stop and give me an error message. It says this, "... conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Col031' (DBTYPE_STR), destination column 'MARKETVALUE' (DBTYPE_I4). There are all numbers as well, no letters or symbols in these columns.

I'm fairly certain I could convert the column to character and it would work fine. The problem is that I need the column to be an integer (as well as my other numeric columns) to perform various mathematical queries.

Thanks again,
JB|||Yeah, you've got bad data...

I would load the data to a table of a varchars...like you have now

Think of it as a staging table

Then audit the data...

For example

Col030 should be a date

Find out all the rows with bad dates

SELECT * FROM myTable99 WHERE ISDATE(Col030)=0

Will show you rows sql doesn't consider a date...

Same with numerics

ISNUMERIC(col1)=0

You won't be able to load those rows to your final destination table

either fix the input file, or ignore the rows...

in any case, go to who ever gave you the file and say...see here...this is garbage...fix it...

and wait a week while they try to figure out what to do...

Golf, Tennis, Long lunches...whatever you want...|||Brett,

You are correct it was bad data. That should have been the first place I looked.

I went into Query Analyzer and ran the query you posted on the columns of data that I wanted to turn into numeric data. 5 out of 32 potential columns had issues. It seems that some of the fields that I wanted to turn into numeric values had blank values. I guess you can't convert a blank character <NULL?> into a blank number.

Anyways, the problem is solved.

Thanks,
JB

Problem with uploading data SQL

Hello,

I have problem:

My *.txt file is like it:
"
12345612345678123
abcdefabcdefghabc
" etc.

i want upload data into table (for example TEST) i want to sql read this
file and automatically upload to table.(as job for example)
but i have 3 columns and i dont know how to separate this text to 3 diffrent
text columns

1 column | second column | third column
-------------
123456 | 12345678 | 123
abcdef | abcdefgh |abc

PLEASE HELP ME, i dont know how to do it.

Robert KlomaHi Robert,

i'm using the following stored proc (sp) for this. I suggest to use field
separators to make it
easier to separate the columns. This sp can be executed by a job.
The imported file should look like this:

1234;abcd;1212
321123;kdkdkd;121233

In the sp you have to replace CPRave15 with your database name.

Hope it helps.

Michael Zankl
http://www.zankl-it.de
Berlin

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
================================================== ==========================
==
Syno: imports file specified in @.UNCPathFileName into a table, specified
in @.DBTable
Use ';' as fieldterminator in the imported file

REMARKS:
- User, who runs this SP, has to be a member of SysAdmin
or BulkAdmin
- User must have Insert-Permission on specified Table or
has to be a member of db_owner

TEST:

DECLARE @.RC int,
@.UNCPathFileName varchar(1024),
@.DBTable varchar(128)

SET @.UNCPathFileName = '\\Absrv02\Components\Debitoren.csv'
SET @.DBTable = 'cprSYSMD_DebImp'

EXEC @.RC = cprIMP_File @.UNCPathFileName, @.DBTable
PRINT @.RC

select * from cprsysmd_debimp
--delete from cprSYSMD_DebImp

Author: MZA, http://www.zankl-it.de, 14.01.2003
================================================== ==========================
==
*/
CREATE PROCEDURE cprIMP_File @.UNCPathFileName varchar(1024),
@.DBTable varchar(128)
AS

DECLARE @.RetVal int,
@.Cmd varchar(8000)

--Example
-- BULK INSERT CPRave15.dbo.cprSYSMD_DebImp
-- FROM '\\Absrv02\Components\Debitoren.csv'

SET @.Cmd = '
BULK INSERT CPRave15.dbo.' + @.DBTable + '
FROM ''' + @.UNCPathFileName + '''
WITH (FIELDTERMINATOR = '';'')' --<== IMPORTANT: use a fieldterminator in
imported file

--print @.Cmd
EXEC (@.Cmd)

SET @.RetVal = @.@.ROWCOUNT

RETURN @.RetVal

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

"Robert K" <rkloma@.hotmail.com> schrieb im Newsbeitrag
news:bi1qj0$etr$1@.news.onet.pl...
> Hello,
> I have problem:
> My *.txt file is like it:
> "
> 12345612345678123
> abcdefabcdefghabc
> " etc.
> i want upload data into table (for example TEST) i want to sql read this
> file and automatically upload to table.(as job for example)
> but i have 3 columns and i dont know how to separate this text to 3
diffrent
> text columns
> 1 column | second column | third column
> -------------
> 123456 | 12345678 | 123
> abcdef | abcdefgh |abc
> PLEASE HELP ME, i dont know how to do it.
> Robert Kloma
>
>|||"Robert K" <rkloma@.hotmail.com> wrote in message news:<bi1qj0$etr$1@.news.onet.pl>...
> Hello,
> I have problem:
> My *.txt file is like it:
> "
> 12345612345678123
> abcdefabcdefghabc
> " etc.
> i want upload data into table (for example TEST) i want to sql read this
> file and automatically upload to table.(as job for example)
> but i have 3 columns and i dont know how to separate this text to 3 diffrent
> text columns
> 1 column | second column | third column
> -------------
> 123456 | 12345678 | 123
> abcdef | abcdefgh |abc
> PLEASE HELP ME, i dont know how to do it.
> Robert Kloma

One option is to create a staging table with one column, load the data
into that table without changing it, then insert into the final table
like this:

insert into dbo.TEST (col1, col2, col3)
select left(StagingColumn, 6), left(StagingColumn, 8),
left(StagingColumn, 3)
from dbo.StagingTable

Simon|||hello, thank you for hint but:

i want to read this data
My *.txt file is like it:
"
1234567890

" etc

1 column | second column | third column
-------------
12 | 3456 | 789

how to do it ,

> One option is to create a staging table with one column, load the data
> into that table without changing it, then insert into the final table
> like this:
> insert into dbo.TEST (col1, col2, col3)
> select left(StagingColumn, 2), left(StagingColumn, 4),
> left(StagingColumn, 4)
> from dbo.StagingTable

the result is of this is
1 column | second column | third column
-------------
12 | 1234 | 1234