Pivot Table Problem w/ External Data Source
Posted by Marcie on February 27, 2001 11:53 AM
Due to a rather large file (>65535 rows), I've had to use an external data source (MS Query) to import .csv text data into a pivot table in Excel. When the data is read into Excel and the report is produced, one column is showing inaccurate results. The dollars are off by a bit. The data in the .csv file is correct, but MSQuery seems to be doing something to the data in this one field. If I take a subset of the data and import it into Excel without going through Query, I get correct totals. Can anyone help?
Posted by Marcie on February 27, 2001 2:14 PM
Turns out that MS Query is stripping off the digits after the decimal for this column only. Anyone know why?
Posted by Mark W. on February 27, 2001 2:48 PM
Marcie, you should have a .INI file in the folder
where your data file resides. This file contains
the schema for your file. Paste this schema in
a followup posting.
Posted by Marcie on February 27, 2001 4:26 PM
Mark, Can't seem to find a corresponding .ini file. Is this something that MS Query generates? Maybe this is the problem...
Posted by GregA on February 28, 2001 7:21 AM
Marcie,
Could you open up the csv file in a word processor (notepad, etc) and cut and paste some rows of data so we can see exactly what the source looks like? That might help identify the problem.
Greg
Posted by Marcie on February 28, 2001 8:23 AM
Here is an example of a few lines of data from my file. The first line is the header. The 'Encumbrance' field is where the problem is (3rd from end). The first two records clearly have a decimal place in the .csv file, the third one does not. I've tried rearranging the data, but it still truncates the decimal when I import it via MS Query.
Date,Department,Account,Account Title,Fund,Project,Sub,Object,Data Class,Typeentry,Source,Description,Reference,Appropriation,Expenditure,Encumbrance,Division Code,TransId
31-Jul-00,60,403410,H/OFFICE OF THE DEAN OPERATING ,19900,00D050,3,0,4,EN, ,FM-ADD MIXING BOX AN,EE0185 ,0,0,-946.93,,01D25
31-Jul-00,60,403410,H/OFFICE OF THE DEAN OPERATING ,19900,OOD207,3,0,4,EN, ,OFFICE SOLUTIONS ,P00651906 ,0,0,642.2,,01W25
31-Jul-00,60,403410,H/OFFICE OF THE DEAN OPERATING ,19917, ,1,0,7,11, ,JULY 1 ADJ. BUDGET , ,33500,0,0,,1800
Posted by Mark W. on February 28, 2001 8:34 AM
Didn't you define your data source using Microsoft's
ODBC Data Administrator and Microsoft's Text Driver?
Posted by Marcie on February 28, 2001 8:51 AM
Interesting Discovery
Yup, I used the MS Text driver. I just made a discovery that will shed light on this whole thing. It wasn't clear in my example, but the first line of data in the entire .csv file contains a 0 in the Encumbrance field (for that particular record). If I change this to '0.00' in the .csv, the decimals come over just fine for all subsequent records. This doesn't explain why the other columns' dollar fields can have a '0' in the first record and import with valid decimal places. I guess I will have to mask the zeros ('0') to appear as '0.00' in the .csv file. I think this will solve my problem. I appreciate all the help!!! Thanks a bunch!
