csv file problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

csv file problem

Author

Topic

Tom85
Starting Member

13 Posts

Posted - 2011-12-12 : 10:55:30

Hello , I've been browsing this forum for a while and seen some impressive skills here so i guess its a best place to ask for advice even with this newbie question...

I created a stored procedure that bulk inserts a csv file into a temp_table and then joins the table with table2 using outer join and selects relevant data from it. Procedure works fine, the problem i have though is following:

excell trimms the column value from 000999 to 999, which as you can guess messes up the outer join since table2 records are not trimmed. (csv file is generated by users, and this has been in place for ages so I dont see any chance of this changing)

Is there any clause that would update the number back to 000999 before/while/after inserting the data? the idea is the number should allways have 6 digits, that means if you go over 1 thousand the number would have only 2 zeros in front of it ( ie: 001000 )

I do realise its a vast explanation for simple problem but its been a pain for too long and I cant seem to find the right clause for it.

Thank you in advance for any help you are willing to provide and for your time you will spend reading this as well.

Have a good day

Regards,
Tom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 11:06:36

you can do something like below


SELECT columns....
FROM yourTable tbl
INNER JOIN (SELECT RIGHT('000000'+CAST(numberfield AS varchar(10)),6) AS numberfield 
            FROM OPENROWSET (Your Excel Connection...)
            )t
ON t.col = tbl.col
....
...
rest of query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Tom85
Starting Member

13 Posts

Posted - 2011-12-13 : 05:23:34

Thank you Visakh, found another way around it ( god bless the Google ) but thanks for your help anyway.

Regards,
Tom

Tom85
Starting Member

13 Posts

Posted - 2011-12-13 : 05:25:27

For those who are interested:

update dbo.mytable set
mycolumn = replicate('0', 6 - len(ltrim(rtrim(mycolumn)))) + mycolumn
where
len(ltrim(rtrim(mycolumn)) < 6

by Lynn Pettis

Regards,
Tom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 06:12:41

quote:
Originally posted by Tom85

For those who are interested:

update dbo.mytable set
mycolumn = replicate('0', 6 - len(ltrim(rtrim(mycolumn)))) + mycolumn
where
len(ltrim(rtrim(mycolumn)) < 6

by Lynn Pettis

Regards,
Tom

you did the update after populating table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Tom85
Starting Member

13 Posts

Posted - 2011-12-13 : 06:25:11

Yes I did, I just hooked it up to mentioned stored procedure right after bulk insert into that temp table,
It is probably one step too many then necessary but it works well, I dont work with huge amounts of data and I dont have to concern myself with performance either.

Regards,
Tom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources