Pivot tables using an external CSV file is pulling numerical data through as text

[scanavan10]

Good afternoon,

I have to run large reports from a system so I generate the output in csv format. In the past I would run a pivot in excel using SQL to pull in the data from the CSV.

Recently the source system has been upgraded. Since then, if I open the pivot I can see numerical data as numerical but when I run it through the pivot it becomes text thus can't be sumed.

Do you know what I can do in SQL to change the data to numeracy or what is the root cause in the data that I can fix at the source.

Thanks

Stephen

 

[Fazza]

hi, Stephen

This might be relevant: http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

If there are mixed data types, you might need a different approach.

If there is some other problem, using VAL function might help. Such as
SELECT VAL(problem data field) AS [field name], etc, etc

I am unfamiliar with the problem & can't advise further.

HTH. Regards, Fazza

 

[scanavan10]

Thanks,

An issue has been found in the source system so hopefully that will have a resolution.