[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

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

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
IvanHowarth
Asked:
IvanHowarth
  • 10
  • 10
  • +1
1 Solution
 
imrancsCommented:
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
 
imrancsCommented:
Change the workbook name & path and the sheet name in above SELECT INTO statement.

Imran
0
 
IvanHowarthAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
IvanHowarthAuthor Commented:
I don't know if this is of any help: It is SQL Server 2000 running on MS Server 2003 platform
0
 
imrancsCommented:
What is the path of you XLS file on your machine and the name of worksheet you want to import?

Imran
0
 
IvanHowarthAuthor Commented:
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
 
imrancsCommented:
The error may be due the incorrect file path/name or the incorrect sheet name.

Imran
0
 
IvanHowarthAuthor Commented:
Data Source="D:\DATA\Users\graphicsdept\Temp\Test1.xls
0
 
imrancsCommented:
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
 
imrancsCommented:
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
 
IvanHowarthAuthor Commented:
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
 
imrancsCommented:
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
 
IvanHowarthAuthor Commented:
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
 
imrancsCommented:
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
 
IvanHowarthAuthor Commented:
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
 
imrancsCommented:
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
 
IvanHowarthAuthor Commented:
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
 
imrancsCommented:
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
 
folderolCommented:
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
 
nmcdermaidCommented:
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
 
IvanHowarthAuthor Commented:
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
 
IvanHowarthAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 10
  • 10
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now