EXCEL Spreadsheet CELLS have data but load as NULL

I

ITContractor

Greetings,

I've been trying to load the EXCEL Spreadsheet:
http://tonto.eia.doe.gov/dnav/pet/pet_pri_gnd_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.OLEDB.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
return NULL values although the Spreadsheet obviously contains data
in
those cells.

Any ideas are appreciated.
 
S

Sam Wilson

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 said:
Greetings,

I've been trying to load the EXCEL Spreadsheet:
http://tonto.eia.doe.gov/dnav/pet/pet_pri_gnd_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.OLEDB.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
return NULL values although the Spreadsheet obviously contains data
in
those cells.

Any ideas are appreciated.
Click to expand...
 
S

Sam Wilson

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

I should read things before I reply.

Sam Wilson said:
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 said:
Greetings,

I've been trying to load the EXCEL Spreadsheet:
http://tonto.eia.doe.gov/dnav/pet/pet_pri_gnd_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.OLEDB.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
Click to expand...
return NULL values although the Spreadsheet obviously contains data
in
those cells.

Any ideas are appreciated.
Click to expand...
Click to expand...
 
I

ITContractor

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 said:
Or just add IMEX=1 after HDR=Yes in your code...

I should read things before I reply.

Sam Wilson said:
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 said:
Greetings,

I've been trying to load the EXCEL Spreadsheet:
http://tonto.eia.doe.gov/dnav/pet/pet_pri_gnd_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.OLEDB.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.
Click to expand...
Click to expand...
Click to expand...
 
S

Sam Wilson

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 said:
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 said:
Or just add IMEX=1 after HDR=Yes in your code...

I should read things before I reply.

Sam Wilson said:
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"

:

Greetings,

I've been trying to load the EXCEL Spreadsheet:
http://tonto.eia.doe.gov/dnav/pet/pet_pri_gnd_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.OLEDB.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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel working with recording macro in excel 1
Excel 2003 vs 2007 4
how to program excel formatting options 3
Reading data from Excel Worksheet in vb 3
Excel Access 2
Excel Import to Access null issue 3
reading excel file 6
Send Mail in Excel Spreadsheet with fields in spreadsheet 3

Top