Help with Data Types in MS Query

[largeselection]

I have a workbook with a couple of tabs of data in it. I'm using another workbook to query that original workbook so I can treat the tabs as a relational database. The issue that I have is that some data types are not importing correctly.

For example - I have a column "Sales" in the original workbook. It has 5 rows of headers and then the sales values by sales person beneath that. When it pulls into the new workbook in the pivot table it all sums up to 0. I'm assuming this is because it looks at the first value and flags the column as text. Is there any way to change this? As far as I know there's not like a schema file or something I can edit.

Thanks for any ideas!

 

[Firefly2012]

Hi

Not sure this will work and also not sure what version of Excel you are using, but on my current Excel 2007 if once you've created the query, go to Data tab and click on the Connections button which will bring up a list of your connections. Select the relevant one and click on Properties.

Select the Definition tab and in the connection string, add the following on to the end (don't miss the trailing ; )

MaxScanRows=0;

Now refresh the query and see if it helps.

 

[largeselection]

Thanks for the suggestion. I tried it out and noticed something else strange. For some reason it's specific columns which are returning as text. I have several columns for sales which are week-to-date, month-to-date, quarter-to-date, year-to-date. For some reason all of the columns labeled week-to-date are returning as numbers formatted as text. The rest of them are returning as actual numbers. I have no idea why that would happen. I found this out by double clicking on a value in the pivottable it brought up a table of the values being used and I could see that all of the "week-to-date" fields were left justified (text) vs the other fields which were right justified (numbers).

 

[Firefly2012]

Personally, if I were you I'd reformat the source data (ie remove the additional headers so you have one header row on the sheet). It will save a lot of hassle (presuming that this is the source of your issue of course).

 

[p45cal]

Some reading for you:
Using ADO with Excel files - Xtreme Visual Basic Talk
http://www.xtremevbtalk.com/showthread.php?t=217783
BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO)
http://support.microsoft.com/kb/319998

How To Use ADO with Excel Data from Visual Basic or VBA
(This one includes a paragraph headed:'A Caution about Mixed Data Types')
http://support.microsoft.com/kb/257819
Daily Dose of Excel » Blog Archive » ADO Recordset Basics
http://www.dailydoseofexcel.com/archives/2004/12/16/ado-recordset-basics/
SQL Server Helper - Tips and Tricks - Query and Import an Excel File
http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx
Office Space: Using ADO to Query an Excel Spreadsheet
http://technet.microsoft.com/en-us/library/ee692882.aspx
Running SQL against Excel file | Visual Importer ETL News
http://www.dbsoftlab.com/etl-tools/visual-importer-etl-news/running-sql-against-excel-file.html