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!
 

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
 

Forum statistics

Threads
1,081,556
Messages
5,359,557
Members
400,534
Latest member
michaella

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top