Solved

SSIS Excel Import losing the number fields

Posted on 2014-07-31
4
166 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
  • 2
4 Comments
 
LVL 14

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 65

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now