Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
On File open - how to stop conversion of postcodes to numbers
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)? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
On File open - how to stop conversion of postcodes to numbers
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 :-) "David Biddulph" wrote: 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. -- David Biddulph "DragonRider from Downunder" <DragonRider from wrote in message ... 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)? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
On File open - how to stop conversion of postcodes to numbers
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) "DragonRider from Downunder" wrote: 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 :-) "David Biddulph" wrote: 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. -- David Biddulph "DragonRider from Downunder" <DragonRider from wrote in message ... 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)? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
On File open - how to stop conversion of postcodes to numbers
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? "bj" wrote: 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) "DragonRider from Downunder" wrote: 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 :-) "David Biddulph" wrote: 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. -- David Biddulph "DragonRider from Downunder" <DragonRider from wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop automatic virus scan when I open powerpoint file? | Excel Discussion (Misc queries) | |||
How to stop file open macro prompt after deleting all macros? | Excel Worksheet Functions | |||
when i open a file a new excel window opens. how do stop this | Excel Discussion (Misc queries) | |||
how to stop Reviewing toolbar from opening when I open a file | Excel Discussion (Misc queries) | |||
How do I stop Excel from closing the open file each time I open a. | Setting up and Configuration of Excel |