Solved

Importing Excel file problem

Posted on 2010-08-12
12
367 Views
Last Modified: 2012-05-10
I am importing Excel 2003 file using SSIS into SQLServer 2005.
Some of the cells in Excel file have little green triangles in the lefthand corners - with a warning that it's a number formatted as text or preceded by an apostrophy. Even though a value is a normal number and when I save it as csv I don't see any apostrophies.
My problem is that when I import this file using SSIS this cells are lost and become NULLs.
Is there any way to avoid it? Excel files are being picked up from ftp site and imported automatically, so fixing it in the file itself is not an option. Unless I create a script that does it.
In SQL Destination is a varchar(10)

Thank you!
0
Comment
Question by:larisa1970
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 12

Expert Comment

by:pastorchris
ID: 33428013
To avoid Excel from formatting your data, try working with CSVs as source files from the remote site.
0
 

Author Comment

by:larisa1970
ID: 33431944
Unfortunately I don't have an option of modifying the source file before importing.
Is it possible to change xls file (one of the sheets in it) to CSV file with a script?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33432831
I would try importing the data into a staging table that only has VarChar() columns.  You should be able to set up the import to use a query that tests the numeric values for being numeric and, either accepts them as they are or , if they are numeric, converts them to a varchar.
Once you have your data in the staging table, you can manipulate it much more easily.  You can set up an INSERT query to pull the data from the staging table and convert any numeric column's varchar value to a number as needed.  This will also allow you to handle issues with dates that may be saved as strings and even those "string dates" that are invalid (e.g. '2010-02-29').
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 33450519
Its probably this issue
http://www.sqldts.com/254.aspx
This page is for DTS but it's really about the Excel driver. Basically you need to stick the
IMEX=1
part into the Excel connection string that's defined in the Excel connection manager
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33459945
Yep, normally add in the IMEX=1

But there is a small "gotcha" in so much as excel might ignore the first 8 rows.

It does try to "guess" the content of a column based on the first "n" rows. That is controlled by a registry setting :

 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows".

If you set it to zero then you are say scan all rows. I have mine set to decimal 25 thought the books say between 0 and 16.

Have a look at the example spreadsheet below - the last three columns using the following :

select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=c:\order_worksheet.xls;hdr=yes;imex=1',' select * from [order_worksheet$]') as a
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=c:\order_worksheet.xls;hdr=yes;imex=0',' select * from [order_worksheet$]') as a

order-worksheet.xls
0
 
LVL 12

Expert Comment

by:pastorchris
ID: 33473039
Hi Larisa,
Have you already tried 8080_Diver's solution above?

Let the SSIS package import your excel sheet into a temporaty table which you can drop later.
While the data is in a temporary table, you can analyse it and pick up what exactly you want from an SQL script.

I once had a similar issue but on an SQL 2000 instance so i designed a DTS package which would pick up the source files one by one, into a temporary table (kind of a trans table) and then i would fire a procedure to handle all inconsistencies.

With SSIS, most probably the issue is related to the drivers you have. It's unlikely that your issue is caused by the way the data is stored/appears in the source Excel file.

Regards,
Chris.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 33474353
Hey Chris,

Just importing you still leave it up to Excel to "auto" decide what column it is - regardless of destination - so alphebetic characters in a numeric column show as NULL and can sometimes do the same the other way around...

So, letting it simply import really wont achieve the results - you need to tell the driver how to override the ever present "help" from excel.

And, it doesnt necessarily have to be SSIS - it could just be a script similar to those openrowsets and essentially "roll your own" import and then simply schedule those scripts. Or a stored procedure or whatever... There are choices.

But always a good idea to first import into a "staging table" then do any validation / tidy up in there before committing to "live".



0
 

Author Comment

by:larisa1970
ID: 33476339
Regarding 8080_Diver's solution:
How do I "set up the import to use a query that tests the numeric values for being numeric and, either accepts them as they are or , if they are numeric, converts them to a varchar."
Jet SQL doesn't recognize CONVERT(VARCHAR,MyField). When I try to do that it complains that it is not a valid SQL statement.
For another suggestion of adding "IMEX=1" - where exactly do I add it?
P.S. It is a text column in excel and varchar(255) in a destination table.

Thank you
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33476478
Could you please try the openrowset with the imex=1 to see if it actually solves your problem... If it doesnt, then we have to do something different...

Is there any particular reason why it must be SSIS ? Could we entertain the idea of using T-SQL (even if an execute sql task)
0
 

Author Comment

by:larisa1970
ID: 33476658
I don't think I can use openrowset because we have 64 bit version
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 33495213
Sure you can...

Or are you saying JET isnt available ? Then have you loaded the ACE (2007 dirver replaces jet)  ?  Can download from : http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en&displaylang=en note there is an x64 version...

Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\order_worksheet.xls;HDR=Yes;imex=1', 'SELECT * FROM [order_worksheet$]') as a
0
 

Author Closing Comment

by:larisa1970
ID: 33629494
Thank you, Mark.
That worked.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

27 Experts available now in Live!

Get 1:1 Help Now