Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SSIS Excel Import losing the number fields

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

578 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