Toad World® Forums

Cross Connect Queries between XLSX docs returns messed up output data


#1

Hi Im doing a number of automated tasked in Toad DP 4.3. The final step simply takes data from my master spreadsheet and then joins the results of other queries to the master data nd saves a new copy. Anyway, the master data when connected and queried returns the right data. However when under a cross- connect query, the data comes out missing data in the columns. Ill post an example below.

This is the direct query from the xlsx doc. and the results:

SELECT Date, CLO, Facility ID
FROM AutoRange_Wireline;

Results::
9901 /T6XP /ABHLMIBH /PNTCMIMN
101 /T6X /DYTNOHIAW01/DYTNOH22
101 /T6XP /DYTNOHIAW01/DYTNOH22
6603 /T4X7P /DRFDILDF /NBRKILNT
103 /T4X7 /DRFDILDF /NBRKILNT

This is the data when trying to run it from a cross-connect query:

SELECT distinct
AutoRange_Wireline.Facility ID
FROM
Prioritization Tracker.AutoRange_Wireline AutoRange_Wireline

RESULTS::

9901 /T6XP /ABHLMIBH /PNTCMIMN
101
101
6603 /T4X7P /DRFDILDF
103

Any Idea how to keep the data the way it should be in the output? I checked and the data is represented as VACHAR(255). Maybe its something in MYSQL if thats what it runs out of?


#2

is 9901 /T6XP /ABHLMIBH /PNTCMIMN in one cell? please run query with Cross-Query Builder not in automation, is data correct? I create two Excel files with sample data, do Cross-Query in Automation and Query Builder, data is correct. We suspect there are special characters, could you send xlsx doc to this Email? cindy.sang@quest.com, if it only happens with automation, tas file would be helpful.

Thanks,
Cindy


#3

Sent file. Thank you for looking.


#4

hi,
I found some special characters(non-ASCII character) in the content of your table. They are invisible in excel, but are visible with my tool (WinHex tool).

e.g. the second row content is: image ,
I copy the content into Winhex tool, and found there’s a special character behind 101, this character doens’t show normally in excel and looks like a whitespace. It’s corresponding hex value is “A0”, while a normal whitespace hex value is “20”, see the picture below

image

Cross-query and single database query are using different mechanism, and cross-query doesn’t support dealing with non-ASCII character , so the issue happens.

solution: since the data is not large, please just retype all the whitespace of your table content