0

I have a csv that gets imported into SQL server using BIDS\SSIS. One of the columns is called callerid which is just a phone number. The corresponding sql db column is numeric. Occasionally the callerid field is filled in as 'UNKNOWN' causing my import to break.

Is there a way to identify when this column is non-numeric, and if so, just set it to zero?

d90
  • 767
  • 2
  • 10
  • 28
  • What do you mean by using visual studio to import csv? Did you write a program that imports the data? – PiotrWolkowski Dec 15 '14 at 19:13
  • It's Visual Studio Business Intelligence Development Studio, or BIDS – d90 Dec 15 '14 at 19:14
  • possible duplicate of [How to validate a CSV file before importing into the database using SSIS?](http://stackoverflow.com/questions/6464601/how-to-validate-a-csv-file-before-importing-into-the-database-using-ssis) – AHiggins Dec 15 '14 at 19:24

1 Answers1

0

I would processed the file externally to replace incorrect value and then continue parsing it the way you do it when it has only correct entries.

If you run this command in Windows PowerShell it will replace all '"unknown"`

[io.file]::readalltext("C:\MyFilePath\MyFile.csv").replace("UNKNOWN","0")

This way you do not risk there is a non-numerical value in the phone number column.

PiotrWolkowski
  • 8,408
  • 6
  • 48
  • 68