January 23, 2011 at 6:06 pm
Hi
Recently i pulled some data out of a sql table into excel. The table has a column that is a varchar and stores dates as a string in this format d/mm/yyy eg '01/04/2004'. These values are inserted from a datetime column.
Other rows have dates in the format d/mm/yyyy eg '1/04/1994', these dates are strings that have been inserted into the varchar column.
In excel all the rows that were converted from datetime to varchar display as dates and the others as text?
I'm not sure whther this behaviour should be expected.
My level of understanding had me thinking that the conversion to varchar would render all values as string when exported from this table.
Does the varchar data type somehow allow data to be stored in its original format?
Cheers
January 23, 2011 at 6:50 pm
Sooo...let me see if I have this right: you have one table, that has a varchar column that stores "date" information. The values come from two inputs, one also a varchar and the other a datetime. When you export this table to excel the resultant column has both varchar and datetime formats?
If I have it right, then there's a range of areas where the effect may kick in. Some of these are:
a) the varchar input saves another character that causes excel to display as text - check for additional chars, including non-displaying chars;
b) the export routine itself is altering the data. How/why depends on the routine;
c) excel is implicitly converting as the default format is "General". It might be possible to force the conversion to something explicit (again, depends on your chosen export routine.)
Try this as a quick test: generate a query that returns both types of rows, that you know will result in the condition in excel. Run said query in SSMS, text output, tab delimited. a) verify that the text output itself already looks correct. If so, select and cut the data (for practical reasons don't grab too much) and then within Excel, using a blank sheet, format all rows/columns to Text format. Paste your data. b) Does the data display as text?
Hopefully something in the above will point you in the right direction. Something else to keep in mind is that export/import routines may determine datatypes based on a sample of the first n records, so if a subsequent value doesn't match the determined datatype it may display differently or even cause the routine to fail.
S.
January 23, 2011 at 7:05 pm
Hi Fal
Thanks for the advice.
I exported to a workbook with all cells set to text and all the dates displayed as text.
I also exported the the results to csv and they displayed as dates and 'general'.
It appears, as you mentioned, that excel can read something from the data and do the conversion.
I don't think it's reading the top n rows and making a judgement from this as the row datatype in the export match exactly with the source data type before being converted to varchar. (hope that makes sense)
How would i test for non visible characters?
January 23, 2011 at 8:42 pm
Are you loading the CSV back into excel? Or are you viewing through notepad/wordpad? If you haven't put the CSV through, say, Notepad yet then do that and identify a known culprit and check the value. As notepad is text-based any oddities may become apparent. Compare also a good and bad example to see if you can spot any differences. Be aware that an "odd" character may display as a space.
I do think you should be able to spot your problem in the CSV.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply