midget1500

anyone know why when this csv file is viewed in excel the values in taxcredits come up as 2 decimal places (when they aren't zero) and PAYE field works as well.

however, import this csv as a data source using MS Query and it truncates the decimal point values from taxcredits field but not PAYE field.

clear? as mud...

this is driving me mad!

<csv data>

TaxCredits,PAYE
0,457.37
0,517.43
0,525.31
0,374.43
0,418.87
0,49.71
0,489.27
0,341.21
0,208
0,222.86
0,281.15
0,486.11
0,225.5
0,954.91
0,342.97
0,463.97
0,520.93
0,235.61
0,622.51
0,568.7
0,414.69
0,420.63
0,484.43
0,341.43
0,262.89
0,272.35
0,65.55
0,1864.93
0,682.51
0,451.63
0,324.72
0,21.55
0,20.67
0,204.81
0,158.39
0,233.85
0,344.73
0,506.87
0,249.47
0,317.23
0,281.81
0,235.83
0,491.03
0,261.35
0,369.59
0,176.65
0,181.93
0,96.13
0,393.35
0,256.52
0,60.05
0,201.29
0,137.93
0,0
0,49.05
0,50.37
0,240.89
0,299.19
0,310.19
0,480.69
0,171.15
0,660.11
0,736.11
0,345.83
0,573.31
0,375.97
0,592.24
0,426.35
0,486.19
0,847.31
0,511.31
0,639.31
0,377.51
0,642.51
0,510.17
0,525.57
0,369.59
0,425.71
0,496.53
0,268.84
0,138.37
252.96,15.5
143.22,88.65
0,76.97
256.37,0
0,1330.11
0,500.49
0,504.45
0,478.25
0,476.51
0,430.09
0,472.77
0,522.27
0,416.67
0,533.05
0,469.47
0,421.27
0,287.97
0,373.33
0,396.65
0,167.63
0,416.01
0,429.41
0,413.81
0,413.81
0,416.45
0,338.79
0,446.37
0,413.81
0,177.97
0,226.59
0,360.57
0,367.39
0,368.25
0,431.39
0,331.09
0,307.77
0,289.29
0,359.03
0,291.05
0,292.59
0,287.31
0,369.13
0,489.05
0,412.93
0,427.89
0,414.69
0,428.11
0,480.89
0,265.31
0,347.57
0,319.63
0,366.07
0,266.19
0,399.27
0,377.05
0,266.85
0,198.63
0,264.87
0,291.05
0,303.15
0,395.12
0,335.93
0,302.93
0,380.81
0,303.15
0,346.93
0,378.84
0,419.75
0,337.89
0,371.13
0,922.91
0,695.41
0,459.31
0,614.01
0,1069.31
0,366.51
0,845.09
0,443.73
0,392.47
0,520.91
0,293.03
0,409.85
0,545.31
0,587.61
0,499.17
0,575.08
0,422.59
0,365.41
0,454.74
0,604.11
0,412.93
0,439.11
0,77.88
0,298.75
0,154.44
0,906.91
0,302.25
0,478.93
0,221.53
0,217.57
0,422.61
0,409.86
0,438.45
0,472.11
0,219.33
0,487.95
0,197.11
0,538.55
0,285.99
0,236.05
0,342.91
0,504.87
0,1290.51
0,463.53
0,383.45
0,548.67
0,343.85
0,482.67
0,265.97
0,238.01
0,213.15
0,386.31
0,448.99
0,276.09
0,206.33
0,522.05
0,380.57
0,198.43
0,308.65
0,446.59
0,295.45
0,357.25
0,354.85
0,157.08
0,388.73
0,253.41
0,487.95
0,510.91
0,319.87
0,289.29
0,344.05
0,275.85
0,335.49
0,269.25
0,373.11
0,232.97
0,425.7
0,313.05
0,234.95
0,370.91
0,283.57
0,58.73
0,367.61
0,105.37
0,49.05
0,588.49
0,294.57
0,723.58
0,1219.29
0,394.11
0,60.5
0,347.37
0,67.76
0,12.54
0,93.28
0,71.71
0,0
0,469.89
0,88.44
0,122.2
0,13.42
0,283.36
0,88.41
0,459.14
0,301.59
0,27.72
0,102.52
0,588.05
0,252.75
0,216.47
0,380.15
0,375.09
0,405.45
0,454.73
0,460.43
0,382.35
0,382.58
0,266.2
0,428.53
0,366.51
0,-124.4
0,55.44
251.88,122.76
0,193.16
0,38.28
0,54.12
0,210.3
0,492.8
0,251.43
0,234.08
0,378.62
0,76.97

bioforger

iTrader: (1)

Muddy indeed, not quite sure what u want, but if u dont want the decimal to 2 places showing on the taxcredit field, you need to turn off fixed decimal places in the Tools/options/edit tab

Dracoro

Just tried it myself and it imports as you see it. No truncation or adding or taking away decimal points etc. i.e. excel is importing it properly. you can afterwards format the fields as say 9.99 etc.

Can you elaborate on the problem.

midget1500

i do want all the decimal places showing. if i view this file (when saved as csv) in excel it shows the taxcredit values with the 2 decimal places, fine.

then add this csv file as an ODBC datasource, go into excel, data, new database query and then select this csv file, and it for some bloody reason it truncates the decimal places off the taxcredits column values, but alas the PAYE values are fine?

weird!!!

Dracoro

It looks like an issue with MS Query rather than Excel. I'm guessing that it looks at the first data row and assumes that the format for all the fields (i.e if the 1st data row had 1.34 then it would assume a floating point number, if it was 23 then it would assume its just and integer).

Do you have to do it using the database query tool? Just open the file and use text to columns? Or open the file, format the fields as 2 decimal points then re-run the database tool.

ajm

Seems to work in Excel 2000

Just to clarify we are doing the same thing, here is what I did:-

• Ran ODBCad32.exe
• Added a User DSN using Microsoft Text Driver
• Ran Excel -> Data -> Get External Data
• Pick the DSN created in ODBCad32.exe above
• Choose both columns
• Return Data to Excel

What version of MDAC are you running? (click HERE to check)

midget1500

dracoro - i thought that too, and changed the first value of the 2nd column to 0 and it didn't "break" that column?!?

unfortunately it "needs" to be done this way, the data posted here is an extract from a much larger file, just that 1 column causing the problem...

running mdac 2.5, as far as i know this problem exists on excel97 but not sure of OS used.

ajm

Could be worth upgrading to the latest MDAC 2.8 maybe?

midget1500

sorry, i have 2.8 installed.

does this work for you using a ms query database query as well as just importing external data?

ajm

Yes, sorry I missed a step above

Ran Excel -> Data -> Get External Data -> New Database Query

Bit of a long shot, normally this shouldn't be an issue, but is the text file generated from a Unix system? If so it may have New Line characters that are giving Windows grief.... Try pasting the lines you posted above into notepad and saving as a new .txt file

midget1500

thanks ajm - tried already. this comes from Sage line 50, tried on many machines, pulled the data apart. i'm at a total loss. think it must be an ms query bug...

cheers
steven

ajm

If you havn't tried already how about going into ODBCad32.exe, select the DSN that points to the text file, click Configure, Options, Define Format, Select the File, Press the Guess button then select each of the columns and check they are defined as floating point (press modify to save selection).

midget1500

ajm - that's the ticket!!! have a virtual pint

the problem is that it's set to scan the first 25 rows by default, which in this case are all 0 therefore assumed to be int. if i increase the number of rows or explicitly define the type then this fixes the problem.

well, that really was a strange one to come across!

ajm

That was a new one for me too, you learn a new thing every day! Glad to be of help!