Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
idthiru
Starting Member
6 Posts |
 Posted - 2006-09-16 : 03:16:18
|
| Hello, Please solve my problem even though i had not posted in appropriate place.(Really have no idea in which forum shall i post this). For a couple of weeks i am struggling with an excel import and export application to sqlserver as i have to do it through an external application and not through the Export Import Utility in sql server.My most recent problem is: The understanding of field values by sql server and excel. 1. I need to import into a table whose fields are varchar type. 2. I have to use as datasource- an Excel sheet(.xls file). 3. Some fields in excel contain numbers (Length up to 10 or more digits). 4. For this I first format the cells'(in Excel file) datatype into "Text". 5. Even then after importing into a table(say tblTemp) whose schema/structure, is CREATE TABLE [tblTemp] ( [rowId] int IDENTITY(1,1), [Account Number] varchar(30) not null , [Mobile Number] varchar(30) null, [Name] varchar(100) null ) , and to import into this table im using select into opendatasource query with datasource as the excel file(Data Source=ExcelFile.xls), the table fields show exponential values(may be floatdatatype). Shall any one suggest me how shall i get values from excel sheet with datatype varchar and import into the above said table as varchar values (with out any exponential types). |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-16 : 04:35:23
|
The DTS import tool just looks at the first few rows of the XLS file, and decides from that what the data type is.There are registry settings which can be used to force DTS to look at all the rows in the XLS before it decides what the datatype is. (Sorry, haven't got the reference to hand but it should be easy to find in Google)I presume DTS doesn't look at the target table's columns' datatype either :-(I **hate** importing from XLS, it's fraught with problems. Unfortunately our clients seem to think its a useful tool, they overlook the amount of effort that we have to do to capitalise, remove stray punctuation, retrospectively apply best-guess, and so on to their inaccurately entered data - all the things that our Front End application has been carefully honed to include over the years in fact  Good Luck</rant>Kristen |
 |
|
|
idthiru
Starting Member
6 Posts |
Posted - 2006-09-16 : 06:44:44
|
| hello Kristen, did you mean those inbuilt import/export utility in sql server by saying DTS Import Tool. but i am doing both import and export through an asp.net web application. also please tell me whether is it (atleast)good to import Excel spread sheets having rows/records up to max 60,000 or even more(some times lakhs), through a web application. even then if we need to do it using applications other than DTS Import Export tools in SQL Server, why shouldn't i go for a standalone windows application? hope i explained to get your understanding,thanks for replying. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-17 : 02:21:50
|
| "did you mean those inbuilt import/export utility in sql server by saying DTS Import Tool"Yes, I did, but I'm not sure what part of the process only examines the first few rows of the XLS ... it might be the ODBC/OLE DB/whatever Driver - in which case you'll have the same problem using ASP.NET!I don't think the size of the XLS sheet matters - if you are using it as a means of providing data for import 1 row or 60,000 doesn't make much different.But you would be better off, IMHO, using some other transport method that can carry the datatype - XML perhaps, or even CSV with the importing tool "knowing" the datatype of the columns being imported.Kristen |
|
|
|
|
|
|
|
|
Excel sheet vs database datatype conflict