Hi all,
I face a small problem in DTS, hope you can help me to solve this issue.
I designed a DTS package to import excel data ( with 4 column ) into SQL server database,The database design for the database table was
column name type length
ID bigint 8
CountedQty decimal 9
Location nvarchar 8
Other nvarchar 50
The DTS package is work and does not appear any error message. But the data in column 'Location' appeared <Null> after import into the database, this issue happen when the excel data was <numeric> value (string value in this data column can be accepted and appear nicely).
Izzit because nvarchar cannot accept numeric value ?
flreStarter
Excel assigns a native data type to each column as it imports. If your first 8 rows are blank or alpha then it will not be considered numeric. Numeric field values, as opposed to numeric characters in a text field, will import as NULL.
Microsoft Article 189897 and Microsoft article 194124 explain the problem. You can set the number of "guess type" rows to a higher number or, if you want all rows checked, change it to 0. See 189897 for a description of how to do this.
Edit: To get this to work from SQL DTS (as I've just discovered by trying it) you have to edit the extended OLE DB connection properties as well as editing the registry entry for TypeGuessRows.
1) Open up your saved DTS package in the designer.
2) Right click on an empty spot in the designer window and choose "Disconnected Edit".
3) Expand the connection for each Excel connection and under OLE DB Properties choose "Extended Properties".
4) Edit the string "value" and add ;IMEX=1 to the connection properties. It'll look something like "Excel 8.0;HDR=YES;IMEX=1"
5) Save your package and rerun it.
There are other ways if you really don't want to change the registry.
If you really just have numeric data, and no alpha characters at all, in the problem column then just put a zero in the first row of data for the column.
If you have mixed data then this works for me:
1) Create two new columns to the left of the problem column
2) In the first column make a formula to concatenate an empty string to the column value. This forces it to be a text value. For example: if your problem column was B and your first data row was 2 then you'd put this in: =concatenate(B2,"")
3) Paste your formula down the entire new column all the way to the last row. The new column should look identical to the first.
4) Copy the first new column and past it into a new empty column with "paste special" and choose values only.
5) Delete the original column and the column with the concatenate formula in it.
|||
Hi Wysiwyg,
Thank you for you reply,
The 1st method that you provide ( editing OLE database connection ) is work, but those imported data will store as SCIENTIFIC format in database.
For example, '240' (numeric in excel file) will represent '2.40E+02' (scientific value) when the data transferred.
I know '2.40E+02' scientific value is because i copy the data from database, then i paste it into the excel file, then right click --> 'format cells', it represent as 'scientific'.
How can we the right data import data (integer taype) rather than scientific format For method two that u provided was not suit for our scenario, because we cannot modify the excel file, therefore we cannot use 'create new column' soluation.
Regrds.
|||I've never had the problem of imported data showing up as scientific values. What is the datatype you are importing into? Did you format the column as numeric with the decimal places specified? When you copy value you'll want the column formatted correctly.
edit: I was able to reproduce it by importing a mixed column into a varchar field; in my case fax numbers some of which had no dashes.
I was able to get around this by copying the columns, as described in my first post, and pasting the values as a text field. If you are importing into a numeric field then you probably have non-numeric values in the column somewhere.
|||There are a number of causes of unexpected numeric formats in Excel. Check out the KB article on the subject:
214233 Text or number converted to unintended number format in Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;214233
Mike
|||I am attempting to get to this point and import Excel however I cannot seem to get DTS (Import) function to show up on the context menu... Please help
|||we load operating budget twice a year in an excel format to the database and eveyrtime we spend way too much time trying to resolve issues related to format and nulls
even product code being truncated because they start with zeros. I realized that struggling with cached format and format defect while not have control over was a waste of
time.
The perfect solution was to change the format that the the data is uploaded from. instead of using an excel sheet we used coma separated value file.
This is so great because all the problematic excel format got lost when we saved the file to csv. event the data scope that was to big and causing the null we
got ride of.
basically in dts package used a text file (source) and specified properties:
row delimiter (CR LF) no text delim
No comments:
Post a Comment