Solved

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

Posted on 2006-07-12
22
486 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
ID: 17089039
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
ID: 17089043
Change the workbook name & path and the sheet name in above SELECT INTO statement.

Imran
0
 

Author Comment

by:IvanHowarth
ID: 17089092
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:IvanHowarth
ID: 17089102
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
ID: 17089123
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
ID: 17089125
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
ID: 17089128
The error may be due the incorrect file path/name or the incorrect sheet name.

Imran
0
 

Author Comment

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

Expert Comment

by:imrancs
ID: 17089143
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
ID: 17089172
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
ID: 17089188
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
 
LVL 10

Expert Comment

by:imrancs
ID: 17089240
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
ID: 17089322
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
ID: 17089552
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
ID: 17089976
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
ID: 17090104
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
ID: 17090484
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
ID: 17091029
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
ID: 17095122
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
ID: 17097163
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
ID: 17109991
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
ID: 17110044
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

756 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