Help with Data Types in MS Query

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
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!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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

Active Member
Joined
Aug 4, 2008
Messages
353
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

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,878
Messages
5,574,781
Members
412,617
Latest member
mlharris
Top