Solved

SSIS Excel Import losing the number fields

Posted on 2014-07-31
4
177 Views
Last Modified: 2016-02-11
Hello Experts Exchange
I have a Excel 2003 spreadsheet that has a table of data and I'm trying to import in to SQL Server 2012 database, but I am losing the numbers from the spreadsheet.

The cells with the numbers are made up of just a number in the cell and some are formulas.

Has anyone come across this problem before?

Thank you for reading

Regards

SQLSearcher
0
Comment
Question by:SQLSearcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40231214
Hi,

In the Excel the data you can see is integer but default data type while extracting data from Excel will be varchar and while Loading that data to SQL table which has int data type the data might not getting matched and hence it is losing.

Just use derived column / Conversion colum and convert that field to integer.
That will do
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40231499
Can you expand on 'losing the number fields', with perhaps a screen shot or data mockup?

Smoke?  Magic?  Mirrors?  Losing My Religion by REM?  Having a hard time picturing this.

>and some are formulas.
Hmm. Not sure if the formula-calculated value will import..
0
 

Accepted Solution

by:
SQLSearcher earned 0 total points
ID: 40231954
Hello Experts
I have been able to import the data now by copying the table of data from the worksheet, creating a new work sheet and pasting the table at the top of the work sheet.

The table is on row 31 and I tried to move all data from the Excel spreadsheet into a CSV file, but all the number fields are blank, when I look at the preview in the SSIS none of the numbers are displayed and none get exported.

I would like to resolve the issue as I may get the same error again in the future.

Formula-calculated fields do get imported as expected when I copy and paste the data.

Regards

SQLSearcher
0
 

Author Closing Comment

by:SQLSearcher
ID: 40262645
Hello
I was able to import data after moving the data to a new worksheet.

Regards

SQLSearcher
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question