Bulk insert Negative Values 2

timroop · May 17, 2006

I have a TXT file that I am importing with Bulk Insert. The format is such that negative values are reported with the sign at the end of the number (ex. 500.1-).

I get an error with type mismatch when I run the bulk insert. It works fine if I manually edit the txt file and place the negative sign at the beginning of the number (ex. -500.1).

Is there a way to define the format to allow SQL to import the trailing sign?

Thank you.

Tim

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

gmmastros · May 17, 2006

Why do people always expect programmers to clean up goofy stuff like this? This sort of thing happens to me all the time.

Anyway, I'm not real good with DTS, but take a look at this example to see if it can help you at all.

Code:

Declare @Temp Table(Value VarChar(100))

Insert Into @Temp Values(NULL)
Insert Into @Temp Values('500.1-')
Insert Into @Temp Values('-500.1')
Insert Into @Temp Values('20.5')

Select *,
       (-2 * sign(CharIndex('-', Value)) + 1) * Convert(Decimal(10,2), Replace(Value, '-', ''))
From   @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

SQLDenis · May 17, 2006

or like this

Code:

create table #Tempt (Value VarChar(100))

Insert Into #Tempt Values(NULL)
Insert Into #Tempt Values('500.1-')
Insert Into #Tempt Values('-500.1')
Insert Into #Tempt Values('20.5')


select *, case when Value like '%-' then '-' + replace(value,'-','') else value end
 From   #Tempt

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

timroop · May 17, 2006

That works for values in a table. What I have is a table already in place and I am bulk inserting into it. The two values in question are hours and pay_rate. There are times when the text file will have a negative pay_rate to indicate that pay is to be deducted from an employee. When I bulk insert the raw text file SQL errors out on the lines with negatives saying that they are an improper data type.

Tim

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

gmmastros · May 17, 2006

Try bulk inserting the data in to a temporary table (with payrate defined as varchar), then use an insert statment from the temp table to the real table (using the conversions that Denis or I show).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

timroop · May 17, 2006

That's just crazy enough to work.

Thanks gmmastros and SQLDenis

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

SQLDenis · May 17, 2006

Aha BULK INSERT

Well either insert into a staging table and then select from there or change the file

I don't know that you have any oother choice expect file import in DTS
and doing something like

also you can do OPENROWSET

SELECT * FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;',
'select * from updates.csv')

then you can do that manipulation in this part SELECT * FROM OPENROWSET

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Bulk insert Negative Values 2

timroop

IS-IT--Management

gmmastros

Programmer

SQLDenis

Programmer

timroop

IS-IT--Management

gmmastros

Programmer

timroop

IS-IT--Management

SQLDenis

Programmer

Similar threads

Part and Inventory Search

Sponsor