From excel to sql server: cells appear as NULL

• elno

  Old Hand

  Points: 368

  More actions

  February 5, 2003 at 12:59 pm

  #151790

  One strange little problem: I execute a package importing data from an Excel file to sqls2k. No manipulation, just importing exact same data.

  However, some cells appear as NULL in sql, even if they do have a value in Excel. I didn't enter the data in Excel, and won't (I just get a file). But is there a way to make sure data is 'real'?

  At first glance, disapearing cells seem to be the same from one execute of the package to the other.

  Any hint appreciated,

  Elno

• Antares686

  SSC Guru

  Points: 125444

  More actions

  February 5, 2003 at 2:28 pm

  #448128

  Check your transformation and the size and datatype associated with those fields. May have a wrong datatype or the length isn't long enough to pick up those pieces.

• elno

  Old Hand

  Points: 368

  More actions

  February 5, 2003 at 2:36 pm

  #448130

  Basically, there's no transformation (data unchanged). In Excel, it's a column with 1 char. (letter or number, but lenght of 1). In sql, it's a nchar 1. Some lines are ok, some aren't...

  Those not ok are -from what I've seen- the same from one test to the other.

  Elno

• philcart

  SSC-Forever

  Points: 47794

  More actions

  February 5, 2003 at 3:05 pm

  #448134

  Are the numbers the values that are missing? If so, check the format of the cell in Excel. If Excel sees it as a number then when you import it into SQL it'll be nulled out if you don't do an explict conversion to char.

  Thanks

  Phill Carter

  --------------------
  Colt 45 - the original point and click interface

• elno

  Old Hand

  Points: 368

  More actions

  February 5, 2003 at 3:28 pm

  #448143

  Thanks for input.

  As far as I can see, NULLs are 'ex-numbers'. However:

  - most of the numbers transfer ok

  - format in excel is 'standard' for all cells (about 50 nulls for 900 numbers ok, in the easiest column to check).

  Since data seems wrong even in the preview, is there a way to convert explicitly (to rule that out, even if data type is not number in Excel - changed it to text, problem remains).

  Thanks

  Elno

• philcart

  SSC-Forever

  Points: 47794

  More actions

  February 5, 2003 at 8:06 pm

  #448161

  You would need to do an ActiveX Script transformation instead of a Copy Column. Delete the transformations you currently have. Select the fields that need conversion and create a new transformation, select ActiveX Script, click Properties and modify the script to convert the Source columns.

  Thanks

  Phill Carter

  --------------------
  Colt 45 - the original point and click interface

• elno

  Old Hand

  Points: 368

  More actions

  February 7, 2003 at 12:42 pm

  #448423

  Thanks for the tip.

  Problem solved, from what I can see, actually the way described by A.Mitchell on

  http://www.sqldts.com/default.aspx?6,222,254,0,1

  Thanks again,

  Elno

• JayDonoghue

  Valued Member

  Points: 72

  More actions

  February 10, 2003 at 9:02 am

  #448541

  I had the same problem importing an Excel file. I got around it using the sqldts.com column you mentioned along with an ActiveX script that saves the file as text first and then does the import.

  Here's the code to save it as a text file

  Function Main()

  Dim oConn' Reference to Connection

  Dim oXLapp' Reference to Excel file

  Dim OldExcelFile' Old Excel file to read

  Dim NewExcelFile' New Excel file to write

  OldExcelFile = "c:\myFile.xls"

  NewExcelFile = "c:\myNEWFile.txt"

  Set oConn = DTSGlobalVariables.Parent.Connections("TextSource")

  oConn.DataSource = NewExcelFile' Set the source connection to be the text file

  Set oXLapp = CreateObject("Excel.Application")

  Set oConn = Nothing

  ' Make the connection to the Excel template

  oXLapp.Workbooks.Open (OldExcelFile)

  ' Verify that Excel was able to be opened

  If oXLapp Is Nothing Then

  Main = DTSTaskExecResult_Failure

  End If

  ' My Excel file had money columns. I force these to text.

  'oXLapp.Workbooks(1).Worksheets(1).Columns("S").NumberFormat = "@"

  'oXLapp.Workbooks(1).Worksheets(1).Columns("T").NumberFormat = "@"

  ' Save the new Excel file

  oXLapp.Workbooks(1).SaveAs NewExcelFile, 21 ' 21 = xlTextMSDOS

  ' Close workbook without saving changes

  oXLapp.Workbooks(1).Close False

  oXLapp.Quit

  Set oXLapp = Nothing

  Main = DTSTaskExecResult_Success

  End Function