Attempting to import a csv into SQL Server that is lead by a random number of 0

Question

Basically, I want to skip the random number of zeroes. The solution, I think, would be to create a dummy column, in the format file, that ends on the first nonzero value. However, after scouring the net, I have no idea how to do that.

edit: To clarify each row is preceded by the random number of 0s.

e.g.

000004412900000009982101201021042010
000000935000000009902005199322071993

Can you explain - do you mean the file has a header with a bunch of zeroes, or each number field starts with zeroes, or each row starts with a random number of zeroes? Can you edit your question to include some sample data? — N West, Jul 24 '12 at 14:13
Sorry, but can you clarify some more? How are the two example lines supposed to be interpreted? Your title says CSV but I don't see any commas. — BellevueBob, Jul 24 '12 at 15:07
I guess it's technically just a flat file, that is my fault. Basically, assume I have one column that I want to insert, however each row of that column is led by a random number of zeroes. I need to get rid of those zeroes. — Jerome, Jul 24 '12 at 15:18

score 0 · Answer 1 · edited Jan 21 '13 at 06:28

0

First i'd import it as is to rows in a temporarily created column I'd then create a user defined function or do this set based

That looped and removed the first char whist that char was "0"

TempCol = CASE WHEN LEFT(TempCol,1) = "0" THEN LTRIM(TempCol, LEN(TempCol) -1)
          ESLE TempCol
END

LOOP WHILE @LEN changes

@LEN = SUM(LEN(TempCol))
SELECT @LEN

edited Jan 21 '13 at 06:28

akjoshi

15,374
13
103
121

answered Jul 24 '12 at 14:41

Ian P

1,724
1
10
12

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

I had to do something similar on a MLS import. Here is essentially what I did.

BULK INSERT TmpTable FROM [CSVPATH] 
WITH (FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n')

Then fix the data you need to fix (the leading zeros)

There a few ways you can do this, but I will list two.

UPDATE TmpTable Set TmpCol = CASE WHEN LEFT(TmpCol,1) = "0" THEN LTRIM(TmpCol, LEN(TmpCol) -1) ELSE TmpCol END

2. UPDATE TmpTable Set TmpCol = SUBSTRING(TmpCol, PATINDEX('%[^0]%', TmpCol + '.'), LEN(TmpCol))

edited May 23 '17 at 12:13

Community

1
1

answered Jul 24 '12 at 16:03

Brad Bamford

3,783
3
22
30

Of course, a third option is to update using a Function as @Dmitriy has so elegantly suggested. However, removing the leading zeros inline will have better [performance](http://connect.microsoft.com/SQLServer/feedback/details/524983/user-defined-function-performance-is-unacceptable). – Brad Bamford Jul 24 '12 at 18:53

score 0 · Answer 3 · answered Jul 24 '12 at 17:45

I assume that the first step of the .csv file import process is to get the file into a database. Then the second step is to clean up the data.

To remove zeros after import, you can create a function:

 create function [dbo].[RemoveLeadingZero](@strSrc as varchar(8000))
 returns varchar(8000)
 as
 begin
      declare  @strResult varchar(8000)
      set @strResult = ''
      begin
          set @strResult = Replace(Ltrim(Replace(@strSrc, '0', ' ')), ' ', '0')
      end
      return @strResult
 end
select dbo.RemoveLeadingZero('000004412900000009982101201021042010')

update myTable
set columnA = dbo.RemoveLeadingZero(columnA)

score 0 · Answer 4 · answered Jul 25 '12 at 04:09

Try this.

DECLARE @str varchar(100)='000004412900000009982101201021042010'
DECLARE @str1 varchar(100)='000000935000000009902005199322071993'

    SELECT @str,RIGHT(@str,LEN(@str)-(PATINDEX('%'+'[1-9]'+'%',@str)-1)),@str1,RIGHT(@str1,LEN(@str1)-(PATINDEX('%'+'[1-9]'+'%',@str1)-1))

score 0 · Answer 5 · edited Jan 08 '13 at 06:49

0

ALTER Function [dbo].[fn_CSVToTable] (@CSVList Varchar(5000))
Returns @Table Table (ColumnData Varchar(50))
As
Begin
If right(@CSVList, 1) <> ','
Select @CSVList = @CSVList + ','

Declare @Pos    Smallint,
@OldPos Smallint
Select  @Pos    = 1,
@OldPos = 1

While   @Pos < Len(@CSVList)
Begin
Select  @Pos = CharIndex(',', @CSVList, @OldPos)
Insert into @Table
Select  LTrim(RTrim(SubString(@CSVList, @OldPos, @Pos - @OldPos))) Col001
Select  @OldPos = @Pos + 1
End
Return
End

edited Jan 08 '13 at 06:49

akjoshi

15,374
13
103
121

answered Jan 08 '13 at 06:15

subash

1
1

welcome to SO, it will be great if you can add some details to your answer explaining your approach/code, that will make it much more useful. – akjoshi Jan 08 '13 at 06:51

Attempting to import a csv into SQL Server that is lead by a random number of 0

5 Answers5