EXCEL Spreadsheet CELLS have data but load as NULL
 

Greetings,

I've been trying to load the EXCEL Spreadsheet:
http://tonto.eia.doe.gov/dnav/pet/pe...dcus_nus_w.htm
HTTP downloaded using the "Download Series History" link.
into SQL Server 2005 using SSIS.

In two cases:

1. Using an "EXCEL Connection Manager" and an "EXCEL Source" object
AND
2. "Script Task" code:
Dim cnn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
Source=F:\DOE-EIA\SSIS Production Sub-Directories\SSIS
Import\PET_PRI_GND_DCUS_NUS_W.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [1-Weekly Retail
Gasoline and Di$]", cnn)
Dim ds As New DataSet("ExcelFile")
Dim column As Int32
Dim row As Int32
Dim cellData As String

da.Fill(ds)

For row = 221 To 222 'ds.Tables.Item(0).Rows.Count
For column = 0 To 4 '(ds.Tables.Item(0).Columns.Count - 1)
If ds.Tables.Item(0).Rows(row).Item(column) Is DBNull.Value
Then
cellData = "NULL"
Else
cellData =
CType(ds.Tables.Item(0).Rows(row).Item(column), String)
End If
MsgBox(cellData, MsgBoxStyle.Information, "ExcelData")
Next column
Next row

The second, third and fourth colunms (among others) of the Speradsheet
return NULL values although the Spreadsheet obviously contains data
in
those cells.

Any ideas are appreciated.

EXCEL Spreadsheet CELLS have data but load as NULL
 

Are the columns that aren't importing properly a mixture of numbers and
strings?

If you have numbers in a column, even if they're in excel as strings, then
the non-numeric values are skipped by SSIS, it took me ages to find that.

In the design studio, right-click on the connection in connection managers -
go to propertioes and change the connection string to include "IMEX=1"

"ITContractor" wrote:

Greetings,

I've been trying to load the EXCEL Spreadsheet:
http://tonto.eia.doe.gov/dnav/pet/pe...dcus_nus_w.htm
HTTP downloaded using the "Download Series History" link.
into SQL Server 2005 using SSIS.

In two cases:

1. Using an "EXCEL Connection Manager" and an "EXCEL Source" object
AND
2. "Script Task" code:
Dim cnn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
Source=F:\DOE-EIA\SSIS Production Sub-Directories\SSIS
Import\PET_PRI_GND_DCUS_NUS_W.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [1-Weekly Retail
Gasoline and Di$]", cnn)
Dim ds As New DataSet("ExcelFile")
Dim column As Int32
Dim row As Int32
Dim cellData As String

da.Fill(ds)

For row = 221 To 222 'ds.Tables.Item(0).Rows.Count
For column = 0 To 4 '(ds.Tables.Item(0).Columns.Count - 1)
If ds.Tables.Item(0).Rows(row).Item(column) Is DBNull.Value
Then
cellData = "NULL"
Else
cellData =
CType(ds.Tables.Item(0).Rows(row).Item(column), String)
End If
MsgBox(cellData, MsgBoxStyle.Information, "ExcelData")
Next column
Next row

The second, third and fourth colunms (among others) of the Speradsheet
return NULL values although the Spreadsheet obviously contains data
in
those cells.

Any ideas are appreciated.

EXCEL Spreadsheet CELLS have data but load as NULL
 

Or just add IMEX=1 after HDR=Yes in your code...

I should read things before I reply.

"Sam Wilson" wrote:

Are the columns that aren't importing properly a mixture of numbers and
strings?

If you have numbers in a column, even if they're in excel as strings, then
the non-numeric values are skipped by SSIS, it took me ages to find that.

In the design studio, right-click on the connection in connection managers -
go to propertioes and change the connection string to include "IMEX=1"

"ITContractor" wrote:

Greetings,

I've been trying to load the EXCEL Spreadsheet:
http://tonto.eia.doe.gov/dnav/pet/pe...dcus_nus_w.htm
HTTP downloaded using the "Download Series History" link.
into SQL Server 2005 using SSIS.

In two cases:

1. Using an "EXCEL Connection Manager" and an "EXCEL Source" object
AND
2. "Script Task" code:
Dim cnn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
Source=F:\DOE-EIA\SSIS Production Sub-Directories\SSIS
Import\PET_PRI_GND_DCUS_NUS_W.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [1-Weekly Retail
Gasoline and Di$]", cnn)
Dim ds As New DataSet("ExcelFile")
Dim column As Int32
Dim row As Int32
Dim cellData As String

da.Fill(ds)

For row = 221 To 222 'ds.Tables.Item(0).Rows.Count
For column = 0 To 4 '(ds.Tables.Item(0).Columns.Count - 1)
If ds.Tables.Item(0).Rows(row).Item(column) Is DBNull.Value
Then
cellData = "NULL"
Else
cellData =
CType(ds.Tables.Item(0).Rows(row).Item(column), String)
End If
MsgBox(cellData, MsgBoxStyle.Information, "ExcelData")
Next column
Next row

The second, third and fourth colunms (among others) of the Speradsheet
return NULL values although the Spreadsheet obviously contains data
in
those cells.

Any ideas are appreciated.

EXCEL Spreadsheet CELLS have data but load as NULL
 

Hello Sam !

Bingo !

I added IMEX=1 after HDR=Yes and the 'invisible' Data appeared in
the debug statements, whereas they did not before.

Many Thanks,
David

"Sam Wilson" wrote:

Or just add IMEX=1 after HDR=Yes in your code...

I should read things before I reply.

"Sam Wilson" wrote:

Are the columns that aren't importing properly a mixture of numbers and
strings?

If you have numbers in a column, even if they're in excel as strings, then
the non-numeric values are skipped by SSIS, it took me ages to find that.

In the design studio, right-click on the connection in connection managers -
go to propertioes and change the connection string to include "IMEX=1"

"ITContractor" wrote:

Greetings,

I've been trying to load the EXCEL Spreadsheet:
http://tonto.eia.doe.gov/dnav/pet/pe...dcus_nus_w.htm
HTTP downloaded using the "Download Series History" link.
into SQL Server 2005 using SSIS.

In two cases:

1. Using an "EXCEL Connection Manager" and an "EXCEL Source" object
AND
2. "Script Task" code:
Dim cnn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
Source=F:\DOE-EIA\SSIS Production Sub-Directories\SSIS
Import\PET_PRI_GND_DCUS_NUS_W.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [1-Weekly Retail
Gasoline and Di$]", cnn)
Dim ds As New DataSet("ExcelFile")
Dim column As Int32
Dim row As Int32
Dim cellData As String

da.Fill(ds)

For row = 221 To 222 'ds.Tables.Item(0).Rows.Count
For column = 0 To 4 '(ds.Tables.Item(0).Columns.Count - 1)
If ds.Tables.Item(0).Rows(row).Item(column) Is DBNull.Value
Then
cellData = "NULL"
Else
cellData =
CType(ds.Tables.Item(0).Rows(row).Item(column), String)
End If
MsgBox(cellData, MsgBoxStyle.Information, "ExcelData")
Next column
Next row

The second, third and fourth colunms (among others) of the Speradsheet
return NULL values although the Spreadsheet obviously contains data
in
those cells.

Any ideas are appreciated.

EXCEL Spreadsheet CELLS have data but load as NULL
 

Excellent news!

Mark it as the answer if you wouldn't mind - that way if someone searches
these forums witha similar problem they'll see there's a solution!

Sam


"ITContractor" wrote:

Hello Sam !

Bingo !

I added IMEX=1 after HDR=Yes and the 'invisible' Data appeared in
the debug statements, whereas they did not before.

Many Thanks,
David

"Sam Wilson" wrote:

Or just add IMEX=1 after HDR=Yes in your code...

I should read things before I reply.

"Sam Wilson" wrote:

Are the columns that aren't importing properly a mixture of numbers and
strings?

If you have numbers in a column, even if they're in excel as strings, then
the non-numeric values are skipped by SSIS, it took me ages to find that.

In the design studio, right-click on the connection in connection managers -
go to propertioes and change the connection string to include "IMEX=1"

"ITContractor" wrote:

Greetings,

I've been trying to load the EXCEL Spreadsheet:
http://tonto.eia.doe.gov/dnav/pet/pe...dcus_nus_w.htm
HTTP downloaded using the "Download Series History" link.
into SQL Server 2005 using SSIS.

In two cases:

1. Using an "EXCEL Connection Manager" and an "EXCEL Source" object
AND
2. "Script Task" code:
Dim cnn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
Source=F:\DOE-EIA\SSIS Production Sub-Directories\SSIS
Import\PET_PRI_GND_DCUS_NUS_W.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [1-Weekly Retail
Gasoline and Di$]", cnn)
Dim ds As New DataSet("ExcelFile")
Dim column As Int32
Dim row As Int32
Dim cellData As String

da.Fill(ds)

For row = 221 To 222 'ds.Tables.Item(0).Rows.Count
For column = 0 To 4 '(ds.Tables.Item(0).Columns.Count - 1)
If ds.Tables.Item(0).Rows(row).Item(column) Is DBNull.Value
Then
cellData = "NULL"
Else
cellData =
CType(ds.Tables.Item(0).Rows(row).Item(column), String)
End If
MsgBox(cellData, MsgBoxStyle.Information, "ExcelData")
Next column
Next row

The second, third and fourth colunms (among others) of the Speradsheet
return NULL values although the Spreadsheet obviously contains data
in
those cells.

Any ideas are appreciated.