July 21, 2005 at 3:01 pm
Hi friends,
I want to transfer table from sql server 2000 to Excel.
Structure of table 'authors' is
FieldName datatype
--------------------------
au_idid
au_lnamevarchar
au_fnamevarchar
phonechar
addressvarchar
cityvarchar
statechar
zipchar
contractbit
I created DTS Package to transfer data.
Data stored in excel sheet for char or varchar datatype incorrectly.
e.g. Value of Field au_lname in sql server = John
Value of Field au_lname in Excel Sheet = 'John
i.e. all such datatypes values preceded by inverted comma.
can any body why is this happening ?
where am I wrong ?
how to solve such errors ?
July 21, 2005 at 10:10 pm
what verson of excel are you using ?! I just tested this with 3 different tables having varchar data types and couldn't recreate what you're seeing!
have you tried deleting the excel file and rerunning the dts package ?!
**ASCII stupid question, get a stupid ANSI !!!**
July 22, 2005 at 3:04 am
When you want to enter numbers in excel to be treated as a string you start by putting a ' in the beginning.
Whilst you do not see this when you look at the cell in excel, it is there.
That's what someone has done.
Overtype a couple of the values and you will see the ' drop off.
July 29, 2005 at 11:56 pm
here're some detailed accounts (unearthed from googling) of identical user griefs:
1) If you let DTS create the table in excel, then apparently it converts a numeric
field into varchar - in this case it converts an int to a varchar and inserts a ' in front of the text columns (as Jonathan said in his reply) - I know you have varchar columns that have this problem but just edit the DTS statements and see what datatypes are being set - splly. IF DTS IS CREATING THE TABLE.
2) Here's someone from another forum with an identical problem but different cause:
"What i did notice though is that in the excel file (any field) but for example
a "minutes" field would export without the ' until it hit a record where the minutes
field was empty, then every record after that one would have the ' infront of it."
3) and this from yet another forum:
"the apostrophe means that Excel thinks it is a text field. It is a total swine trying to get numeric data out of SQL in Excel as the Excel driver just doesn't seem to recognise that you want it out as numeric.
One way round this is to create the Sheet in the Excel file from within DTS, where you can specify the datatypes you want for each column.
DTS thinks of the sheet as a table. Use an Exec SQL Task, pointed at the Excel connection and use a variation of the CREATE TABLE syntax -
CREATE TABLE `TestTableOne` (
`Col1` Long ,
`Col2` Long )
Note the quotes are not the standard single quotes, but the silly angled ones. To save building this by hand use the "Create New" button of the Destination tab of the DataPump and copy the code into your Exec SQL Task. As I've defined the columns as Long it won't put the daft apostrophe in front when you get to Excel. "
sorry I can't provide any of the URLs as I was copying and pasting into notepad as I went along but I'm sure you'll get the same hits with google. Hopefully one of these suggestions will resolve your problem.
Good luck and let us know how it goes!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply