On File open - how to stop conversion of postcodes to numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DragonRider from Downunder - 18 Jun 2007 12:18 GMT

I have a CSV file I download regularly which on open within Excel converts
all numeric fields to numbers - when I do not want it to do so.

e.g. Postcodes can be "00037" appear as 37 which is not a valid US postcode
for printing to labels etc. As postcodes in Australia for example are only 4
digits I can not just replace the missing leading zeros by string
manipulation - as I do not know how many there should be. perhaps some
countries have 6 numbers in the postcode? I know UK have mix of numbers and
letters so they are OK as they appear as text.

When I open CSV file using Notepad the field contains "00037" so it is all
there in the Raw state of the file.

Surely there must be a way to open (without using complex open text file or
stream and complex record structures) a CSV file and control the bahaviour to
forbid it changing the field contents (ie. opening it as straight text or
ascii equivalent)?

Reply to this Message

David Biddulph - 18 Jun 2007 13:07 GMT

Yes. Rename the file from .csv to .txt. When you open the txt from within
Excel, the text import wizard will allow you to select the format of each
individual column, so you can select text for your postcodes.

Unfortunately importing from a file named .csv bypasses the wizard, so you
get Excel's default options.

Signature

David Biddulph

>I have a CSV file I download regularly which on open within Excel converts
> all numeric fields to numbers - when I do not want it to do so.
[quoted text clipped - 18 lines]
> forbid it changing the field contents (ie. opening it as straight text or
> ascii equivalent)?

Reply to this Message

DragonRider from Downunder - 18 Jun 2007 14:04 GMT

Hi David,

Realy appreciate the reply but not really the answer I need...

Have already gone that route, but the file then needs too much manual
fiddling and being quite long becomes more of a hassle. As I need to do this
twice per week I was almost tempted to write a quick pascal file to just add
a space before every entry in the postcode field before the first number just
so excel could not convert it, but I figured there surely must be a simple
solution I was just not aware of....

I could write an automated macro to run the file wizard but I would forever
be handling all the exceptions this throws up and I am wanting to do less
rather than more work...

There must be a way to alter the default behaviour (perhaps dump to an
Access file first and then to excel)? Not sure yet if access tries to do the
same dumb conversion. Will attempt this tomorrow.

Trying a few other options too - if I find an easy solution will post here
for benfit of other members. Again thanks to those who have taken the time
to read this post.

Lex :-)

> Yes. Rename the file from .csv to .txt. When you open the txt from within
> Excel, the text import wizard will allow you to select the format of each
[quoted text clipped - 24 lines]
> > forbid it changing the field contents (ie. opening it as straight text or
> > ascii equivalent)?

Reply to this Message

bj - 18 Jun 2007 15:58 GMT

If the zips are always in the same location in the downloaded file
try downloading once as .TXT making sure the Zips are text then
down load the .CSV file and replace zips with the zip from the earlier
download.

not real friendly but not too bad.
(I have had to do worse)

> Hi David,
>
[quoted text clipped - 50 lines]
> > > forbid it changing the field contents (ie. opening it as straight text or
> > > ascii equivalent)?

Reply to this Message

DragonRider from Downunder - 19 Jun 2007 07:54 GMT

Hi BJ,

That works, and a good 'outside the square' solution too.

I tried using a ";" delimited file to see if it would make any difference...

The problem I encountered is CSV import goes badly wrong if any of the
fields contain more then a certain amount of text in a given field and has to
be manually adjusted in Notepad first. After that, it is simply a matter of
running the macrocode
Workbooks.OpenText Filename := "E:\Data\Parcels.txt", _
Origin := 437, StartRow := 1, DataType := xlDelimited, TextQualifier

:= _

xlDoubleQuote, ConsecutiveDelimiter := TRUE, Tab := FALSE, Semicolon

:= TRUE, _

Comma := FALSE, Space := FALSE, Other := FALSE, FieldInfo :=
Array(Array(1,1), _

Array(2,1),Array(3,1),Array(4,1),Array(5,1),Array(6,1),Array(7,1),Array(8,1),
_
Array(9,1),Array(10,1)
to convert the 10 fields to valid fields including the Postcode...

Still have to be careful when copying this field to assign the .Text and not
the .Value of the field to prevent losing the leading zeros and if moving to
a new worksheet to define the range being 'moved to' to being the correct
'numberformat type' before assigning e.g.
Cells(lCurrentLine, Col_Postcode).NumberFormat = "@" . Otherwise Excel
stubbornly is determined to lose those leading zeros...I even string added a
space to the front of the Value to try to stop this - with no luck! A
definite failing of Excel to anticipate correctly the programmers wishes.

In the end, going back to basics - I was able to change the download file to
be of type XLS from the source program - so when opened, Excel did not try to
change the content of the fields, therefore making a cleaner simpler open
file without having to resort to the text version (above). It seems that
many of the conversion issues are with the way Excel interprets .CSV files
and once it gets an XLS, these are no longer problems... has taught me an
import lesson about .CVS limitations when using Excel.

Thank you both for your assistance, this problem is now solved. Being a
first time user of the forum I am unsure how I mark it closed? Do I need to
do anything to let users know there is no need to address this issue further?

Thanks,

Lex.

PS: Note there is no spell checker before posting?

> If the zips are always in the same location in the downloaded file
> try downloading once as .TXT making sure the Zips are text then
[quoted text clipped - 58 lines]
> > > > forbid it changing the field contents (ie. opening it as straight text or
> > > > ascii equivalent)?

Reply to this Message