?
Solved

SSIS Excel Import losing the number fields

Posted on 2014-07-31
4
Medium Priority
?
181 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

777 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