"IQY" File and Excel Formatting

[YOUNGCODGER]

Hi,

I have a number of "IQY" files which query websites and import the required data into Excel. The problem I have is on some of the cells contain telephone numbers. If these contain "+44" or spaces this is fine but if it is a single number with a leading "0"(s) Excel strips these off. Is there a way of preventing this?

Thanks and regards,

YoungCodger

 

[xlbo]

Probably not - would've thought it will need to be processed in code after the event as I think web queries are embedded so you can't manipulate the data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

If you actually IMPORT the file into an existing worksheet, you can specify the column format as TEXT and the existing leading zeros will be imported.

How are YOU "importing" this file into Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[YOUNGCODGER]

Here is an example of an .iqy file I am running:-

WEB
1

http://www.telephonenumbers.co.uk/Geographic-Codes/i=2

Selection=6,7
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

I have searched for to say "import as text" but with no joy. The file is run from Excel/Data/Import External Data/Import Data, and selecting the .iqy file

Thanks,

YoungCodger

 

[SkipVought]

ahhh.

I missed that it is a WEB query. Sorry.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jges]

Are you importing into an existing Excel file? If so, can you format the column as text before performing the query?

 

[xlbo]

Forgot to mention, you could of course run code afterwards to clean up - phone numbers are always awkward buggers to detect and deal with though

Out of interest, what happens if you change:

Selection=6,7
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

to

Selection=6,7
Formatting=Text
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False


Not really used web queries much so not sure if "Text" is something it would expect - may need to play around a bit to get the syntax right - could also try:

Formatting="0"
if "text" doesn;t work - may force all your data to come out as text...


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[YOUNGCODGER]

Hi Jges,

I usually double click on the .iqy file and it opens a new Excel spreadsheet and imports the data. If I import the data inot an open spreadsheet which has the cells formatted as "text" it imports the numbers as such (left justified) but still strips the leading "0"s.

Thanks,

YoungCodger

 

[SkipVought]

What happens if you do this, to turn OFF, text to columns...
[tt]
Selection=6,7
Formatting=Text
PreFormattedTextToColumns=False
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
[/tt]
Then do a Text to columns on the resultset.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

I made a few different attempts using the query specifications provided.

I tried the PreFormattedTextToColumns=False, which seems to have no effect

When the iqy file is opened, it creates a new workbook and imports the data as a web query. So I tried to format the columns AFTER the query was opened and retrieved, then refreshing. Well, it keeps the formatting given if

Formatting=[b]None[/b]

But it STILL strips the leading zeros!
All of this synchs up with what YoungCodger was saying.

I was able to get the full data through this means:
Open the link

http://www.telephonenumbers.co.uk/Geographic-Codes/i=2

Create a new workbook
Format column A as text
Copy the codes and areas from the website
In Cell A1, Paste Special->Text

I hope that helps.

 

[fisheromacse]

I have no idea if you can do this, but what in other projects not involving a web query, i was able to add a ' (single quote) prior to the number so that upon import excel receives '018 or '009 instead of 18 or 9
it should not affect other values such as +44
this should force Excel to accept it as text and should retain the leading zeros.