Hello masters, have someone a script to transformation of data using isql?

Question

Hello masters, have someone a script to transformation of data using isql?

Hector Angello Silva Carmona

SSCrazy

Points: 2720
More actions
April 18, 2005 at 2:30 pm

#66192

Hello masters, have someone a script to do a transformation of datas
between a txt file and a table using isql.....
i need to use isql because a need to determinate an error into a register of txt file...
thanks friend..................

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Hello Hector,

I don't think you want to use ISQL to do this. BCP will not only determine errors, but any records that don't match the table definition can be automatically diverted to a different text table for repair, if possibe.

If it's a big table, BCP is one of the best ways to go... does a million records or more (depends on the number of indexes on the table) in only 100 seconds on just about any decent server.

If you like to post the record layout, 10 or 15 sample rows of data, and the schema for the table you'd like to load, I'll take a crack at it for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 2

Thanks friend, well let me explain the process that i do,

weekly a txt file of 64 MB is sent to me, i must to move the datas of file to a table of sqlserver, i am using a dts to do this work , but the dts crashes, appears the next message of error

" Delimiter not Found'

the file is using the ";" like limiter to separate the fields,and to find the register within error is very bothering , and the dts does not tell me which is the register that is bad...

how will able to solve quickly that problem?

Thanks a lot of my friend...

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Hector,

I'm on my way out, just now so I need to be brief but I'll be back.

The usual problem with "comma delimited" files is when an extra comma is added to a field as part of a name or whatever. If BCP is setup correctly (using a format file), BCP will inport only the good and can be easily programmed to put the bad stuff in an "error" file for correction, examination, resubmittal, whatever. It's all in the parameters and how you define the field.

If you could post the "CREATE" statement for the table (hopefully, without all the collation clauses), that would be a big help in me helping you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)