Help with Data Types in MS Query

largeselection

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

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.
 
Upvote 0
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).
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top