February 7, 2006 at 8:48 am
I'm having problems importing an Excel XLS into a SQL database using Enterprise Manager. The XLS has about 1000 rows of 3 columns with a header-row. The first rows look like 10,20,100.00 but lower rows contain non-numeric characters such as 10A,20B,200.00.
Importing shows all the field properties as "float" when I click on the Transform button -- don't know why, so I set them to varchar,varchar,decimal. This appears to have no effect, since all non-numeric cells like "10A" are <NULL>. Other cells import just fine.
I tried changing the column properties in the Excel sheet to text,text and numeric, again with no effect.
I tried adding an extra top row X,Y,100.00 but that didn't help either.
Trying to import via Access didn't help. Access threw out all rows containg a non-numeric field.
I eventually saved the Excel sheet as a tab separated text file and imported that. That worked OK.
I am expecting more Excel sheets to arrive so I need a better way of importing them. Naively I assumed that since Excel is only an "x-y" table like a database "row-col" table it should be easy. How wrong I was !
Am I doing something wrong? Any help please.
February 7, 2006 at 10:01 am
This is known issue. If you format the columns in excel and have the data re-entered, it should fix your problem(apparently just changing the format for entered data doesnt work). As I recall there is a disconnected property that may be helpful here also..but I can't remember which one.
What I have done is to import excel using OpenRowSet instead. Then you can make a SQL task..and place the SQL in it. You'd do something like..
Select convert(varchar(12),col1) Col1 ... from OpenRowSet(...)Q.
(I have the exact syntax to use for openrowset in my blog..if you need it)
HTH
Mathew J Kulangara
sqladventures.blogspot.com
February 7, 2006 at 11:41 am
I have had some luck in importing this to Access first and then import the Access table into SQL. It is a pain.
Michelle
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply