Solved

Importing an Excel table with mixed data type colums to a SQL Table

Posted on 2006-07-12
22
474 Views
Last Modified: 2008-01-09
I have an MS Excel sheet with 1300 odd lines and a couple of dozen of columns that has been in use for years by general users (so a lot and in a mess), and for the time being, needs to continue to be used. I need the data imported across to a SQL table but not all the data is getting imported.

To cut out all the Excel column headings and notes etc in the spread sheet, I have set the print area for the bulk data I need. Most of these columns then become just one data type (Numbers/smallInt or Text/VarChar). But I do have some columns with a mix of the two. As the numbers will not be used in a mathematical sense, I have tried changing the format of these Excel columns into TEXT, then setting the destination SQL Table Column as a VarChar to no avail. The Source column with a majority of Text transfers across, and if you then write a number in the column (although Excel puts a small green error symbol in the top left corner of the cell saying that the number is stored as Text, do you want to convert to a number...) this value also transfers across.

Have a Source column of numbers (formatted to Text) then type in some text, SQL during the import always knows that it is a Double column so ignores the text values, even if the destination table is expecting only VarChar data types.

Please, please, any ideas on how to get around this problem. I have spent so many hours trying to identify what the problem is, and now how to get around it.

Much thanks in advance.
0
Comment
Question by:IvanHowarth
  • 10
  • 10
  • +1
22 Comments
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
Try executing this in the Query Analyzer

SELECT *
INTO NEWTABLE
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="C:\EXCELWORKBOOK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...Sheet1$

Imran
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
Change the workbook name & path and the sheet name in above SELECT INTO statement.

Imran
0
 

Author Comment

by:IvanHowarth
Comment Utility
After copying and pasting, changing the items as requested, I got the following error message...

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:  The provider did not give any information about the error.].
0
 

Author Comment

by:IvanHowarth
Comment Utility
I don't know if this is of any help: It is SQL Server 2000 running on MS Server 2003 platform
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
What is the path of you XLS file on your machine and the name of worksheet you want to import?

Imran
0
 

Author Comment

by:IvanHowarth
Comment Utility
My sheet name is: Test

Have I followed your suggestion correctly by writting the last line:

 'Data Source="C:\CorrectPathToFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...Test$
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
The error may be due the incorrect file path/name or the incorrect sheet name.

Imran
0
 

Author Comment

by:IvanHowarth
Comment Utility
Data Source="D:\DATA\Users\graphicsdept\Temp\Test1.xls
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
try executing this and see if it produce some results:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
 'Data Source="C:\CorrectPathToFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...Test

Imran
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
sorry I missed the $ sign:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
 'Data Source="C:\CorrectPathToFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...Test$

And make sure that your execl file is not opened while you are runngin the above query.

Imran
0
 

Author Comment

by:IvanHowarth
Comment Utility
The original worked - just realised that the source sheet was open on another computer... errrrrr!!  Just checking the data transfer as the destination columns are in alphabetical order so out of sync to the source. Bear with me....
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Expert Comment

by:imrancs
Comment Utility
Although these should be in the same order, but, you can set the column names [the first row] in the excel sheet and use them in the query if you want to get them specific order like:

SELECT Col1, Col2, Col3,......
INTO NEWTABLE
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="C:\EXCELWORKBOOK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...Sheet1$


Imran
0
 

Author Comment

by:IvanHowarth
Comment Utility
Same problem still exists -

Source columns seen by SQL as 'Double' data types only import numbers. Any alpha or alphanumeric characters in that column are then being ignored leaving Null values.

And visa-a-versa, source columns identified as 'nvarChar' data types import everything except numbers. Again, numbers become Null vales

:-(



0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
1- Edit the data types of the columns of table created above
2- Remove the data from the table

INSERT INTO NEWTABLE (Col1, Col2, Col3,......)
SELECT Col1, Col2, Col3,......
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="C:\EXCELWORKBOOK.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...Sheet1$


Imran
0
 

Author Comment

by:IvanHowarth
Comment Utility
The source column with mostly numbers but the odd alphanumeric value (in this case a cell containing '4VAR' and others), formatted in Excell as numbers and SQL smallint, throws this error message:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '4VAR' to a column of data type smallint.

Formatted to Text/nvarchar respectively runs, but no numbers.

0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
If there is a column with the numbers and in some cases it has alphanumeric values then change the datatype of that column to NVARCHAR in SQL. Once the data is imported successfully then you can correct the data & change the datatype accordingly.

I would recommend:
1. make all the columns to NVARCHAR type in SQL
2. Import the data
3. Correct the data
4. Change the datatypes what they should be.

Imran
0
 

Author Comment

by:IvanHowarth
Comment Utility
Given that this import will have to be repeated, thousands of data corrections required and the excel has to remain the master data source for the time being, I think it's best bite the bullet and make all the changes once on the excel sheet to enable seamless import into SQL.

Following your advice the way I'm following it, as a lot of data is being imported as Null values, correcting the data will need to be repeated every time I re-import as you can't have more than one data type in a single column.

Nevertheless, I thank you for persevering with me and for your help.
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
You are welcome!

Yes, you are right, it's better to correct the data in excel workbook. In this way you can cleanly import the data with INSERT INTO & SELECT statements.

Imran
0
 
LVL 19

Expert Comment

by:folderol
Comment Utility
I think all you need do is include code like

convert( nvarchar(10),Col3 ) as Col3

in your select list.

I use DTS, and to include transformations go to Design Package, Transform Data Task Properties, click on the Build Query button.
The other thing that has worked is to just force an alpha value into the 2nd row of the worksheet, assuming column headings are in row 1.  SQL 2000 grabs its datatypes from checking the contents the top x rows, and not necessarily the Excel assigned format, so this has worked for me in the past.

Tom
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
Comment Utility
I hate to be a link poster but I think your answer is here:

http://www.sqldts.com/default.aspx?254

The key is the IMEX=1 property - it makes mixed data types work.

Get it working in DTS first, and if you still want to use OPENROWSET, just use the provider string that you're using in DTS.



Also you may want to use a named range instead of the print area.

1. Select the data you want then go to the 'Name Box' (top left - it normally has the name of a cell in it)
2. Type something into the name box and hit enter.
3. You've just created a named range.


This does two things:

1. Allows you to select the name range in Excel and it will identify your data set
2. Allows you to reference it like a table in a select statement
0
 

Author Comment

by:IvanHowarth
Comment Utility
From my last comment, I started the pain staking task of overwriting numbers in the Excel sheet formatted to text. Then accidentally discovered the ridiculously simple solution:

  - Select All in the Excel sheet, then Copy and Paste into Windows 'Notepad'
  - Select All of the Excel sheet again and format to Text
  - Copy and Paste back from Notepad to the Excel Sheet

Now, all numbers are seen as Text/Varchar and everything successfully imports across into the SQL Table regardless of mixed columns.

A 1 min job that took me about 15 hrs to achieve... the joys of software development :-)

Thanks everyone for your comments!
0
 

Author Comment

by:IvanHowarth
Comment Utility
nmcdermaid

I'll award you the points as the one of the two solutions given is the rekey option, effectively what I did. However, I had 70,000+ cells to contend with hence me  eager to find a quicker solution to which I hope to have found. Although not tried, I assume that the second solution also works. I still think mine is the best though now :)

Thanks again!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

11 Experts available now in Live!

Get 1:1 Help Now