help with csv file

[elyas]

i have this csv file which it contains a lot of phone numbers, with 0 a
the start and spaces in between, i wanna import them to a new progra
which need everything without spaces, how can i get rid of space
without loosing the 0 at the start of the number and save everything i
csv again

 

[Auric__]

i have this csv file which it contains a lot of phone numbers, with 0 at
the start and spaces in between, i wanna import them to a new program
which need everything without spaces, how can i get rid of spaces
without loosing the 0 at the start of the number and save everything in
csv again?

Post an example of what the lines currently look like, and how you need them
to look.

 

[Chrisso]

i have this csv file which it contains a lot of phone numbers, with 0 at
the start and spaces in between, i wanna import them to a new program
which need everything without spaces, how can i get rid of spaces
without loosing the 0 at the start of the number and save everything in
csv again?

* use the Microsoft Scripting Runtime library/reference
* use the FileSystemObject to open and get a handle on your CSV file
* use the FileSystemObject to get ready to write to a new CSV file
* use the TextStream object to iterate over each line in the CSV file
* walk over each character in the line and add it to a string only if
it is a comma or a digit
* when you get to the end of the line write your string to the new CSV
file using the TextStream object you set up

You will then have your original CSV and the new one with the changes
you wanted.

Chrisso

 

[GS]

* use the Microsoft Scripting Runtime library/reference
* use the FileSystemObject to open and get a handle on your CSV file
* use the FileSystemObject to get ready to write to a new CSV file
* use the TextStream object to iterate over each line in the CSV file
* walk over each character in the line and add it to a string only if
it is a comma or a digit
* when you get to the end of the line write your string to the new CSV
file using the TextStream object you set up

You will then have your original CSV and the new one with the changes
you wanted.

Chrisso

WOW! Do you really use these external libs this way???

It would be tonnes faster and more efficient to use standard VB{A} file
I/O to 'get' the data into an array, modify the text to remove spaces,
then 'put' the array back into the file.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[William B. (Billby)]

WOW! Do you really use these external libs this way???

It would be tonnes faster and more efficient to use standard VB{A} file I/O to 'get' the data into
an array, modify the text to remove spaces, then 'put' the array back into the file.

Depending on how you want to use the phone numbers,
for a 'once only' job it might be easiest to load
the file into a good freeware editor like Notepad++
- from http://notepad-plus-plus.org/
Then load your file and find & replace all 'spaces'
with '' (nothing...) and save the result as a CSV
under a new filename, if needed as such.

Another option is to change the filename from xxxxxx.csv
to xxxxxx.txt and load it into Excel as a 'space' deliminated file.
Then concatenate any resulting split columns into a single column by
using the '&' operator, or conactenate function for every row.
Copy the resultant column to a new sheet as 'paste special.values'
and again saving under a new csv filname, if needed.

Both of those options take longer to describe than to actually do...

Cheers,

William B.

 

[GS]

William B. (Billby) laid this down on his screen :

Depending on how you want to use the phone numbers,
for a 'once only' job it might be easiest to load
the file into a good freeware editor like Notepad++
- from http://notepad-plus-plus.org/
Then load your file and find & replace all 'spaces'
with '' (nothing...) and save the result as a CSV
under a new filename, if needed as such.

Another option is to change the filename from xxxxxx.csv
to xxxxxx.txt and load it into Excel as a 'space' deliminated file.
Then concatenate any resulting split columns into a single column by
using the '&' operator, or conactenate function for every row.
Copy the resultant column to a new sheet as 'paste special.values'
and again saving under a new csv filname, if needed.

Both of those options take longer to describe than to actually do...

Cheers,

William B.

Well, both your suggestions are doable but, IMO, they're both a lot
more work than my suggestion. Not to mention that neither of those
approaches involves VBA (this being a VBA forum & all).

I mean no offense to you or Chrisso. I just have a lot of experience
working with CSV files (and delimited files in general whether the
delimiter is a comma or not), especially in the form of large database
'dumps'. Thus, I know what tools to use for optimum processing. This
task can be done with a mouse click (or two), whereas your idea
involves outside processing via a separate app (yet to be installed,
maybe) AND doing an external data import AFTER the data has been fixed
so it can be imported as wanted. I'm thinking click a menu that opens a
file picker dialog and clicking on the CSV to process. Sounds a lot
simpler approach to me in terms of end user productivity!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[William B. (Billby)]

William B. (Billby) laid this down on his screen :

Well, both your suggestions are doable but, IMO, they're both a lot more work than my suggestion.
Not to mention that neither of those approaches involves VBA (this being a VBA forum & all).

I mean no offense to you or Chrisso. I just have a lot of experience working with CSV files (and
delimited files in general whether the delimiter is a comma or not), especially in the form of large
database 'dumps'. Thus, I know what tools to use for optimum processing. This task can be done with
a mouse click (or two), whereas your idea involves outside processing via a separate app (yet to be
installed, maybe) AND doing an external data import AFTER the data has been fixed so it can be
imported as wanted. I'm thinking click a menu that opens a file picker dialog and clicking on the
CSV to process. Sounds a lot simpler approach to me in terms of end user productivity!

Hi Garry, Thanks for your response.

One of the reasons I browse about a dozen Usenet Excel groups is to learn from experts like
yourself, and I've learnt plenty from your generous and accurate contributions.

If you want to keep this group purely for VBA that's fine with me but after 45 years in various
areas of IT and and around 28 years (can it be that long?) reading Usenet groups and 15 years (from
the Office 97 +20Kg pack days) using Excel (light years better than Multiplan...) for ever
increasing purposes, I believe that the more responses to a query the better.

I may be wrong but the impression I got from elyas's original post is that he is closer to my level
of 'in-expertise' than your level of expertise...

To my mind, for 'one off' problems like his, editing files with a good editor is conceptually
easier and faster for beginners than getting into VBA. BUT of course, for long term benefit and
more complex problems, spending time learning VBA is definitely the way to go and for experts like
yourself, the only one way to go.

Looking forward to learning more from your future contributions,

Best wishes,

William B.

 

[GS]

It happens that William B. (Billby) formulated :

Hi Garry, Thanks for your response.

One of the reasons I browse about a dozen Usenet Excel groups is to learn
from experts like yourself, and I've learnt plenty from your generous and
accurate contributions.

Hi William,

Thanks for your kind words and overall feedback. While I don't consider
myself an 'expert' per se, I'm hopeful that my contributions to the
various NGs is beneficial to others.

If you want to keep this group purely for VBA that's fine with me but after
45 years in various areas of IT and and around 28 years (can it be that
long?) reading Usenet groups and 15 years (from the Office 97 +20Kg pack
days) using Excel (light years better than Multiplan...) for ever increasing
purposes, I believe that the more responses to a query the better.

Alternative suggestions to using VBA are most welcome, and sometimes a
better approach given the macro security issue that tag along with VBA.

Sounds like your experiences are very worthy assets. My experience lies
in a trades background. I'm a journeyman machinist/toolmaker/designer
but (after 38 years) can no longer work at that due to falling victim
to Lou Gehrig's back in 1993. (I have same strain as Stephen Hawking.
He's lived with it for over 40 years) I was urged by my accountant to
make some of my business solutions available to other trades people who
were making a business of their profession. At the time I hadn't used
macros or even considered using VBA. I did make use of 3rd party
'addins', though, and these inspired me to automate my solutions so
people with minimal Excel skills could use them. Since my formal
education was business, majoring in accounting and management, I had
the tools to turn my mechanical engineering interests into a
self-employed business opportunity after I became tired of doing
forensic accounting in the real world. I engaged the services of an
expert 'tax' accountant for doing my tax returns, and so my being here
is the result of his prompts to share my solutions.

I may be wrong but the impression I got from elyas's original post is that he
is closer to my level of 'in-expertise' than your level of expertise...

To my mind, for 'one off' problems like his, editing files with a good editor
is conceptually easier and faster for beginners than getting into VBA. BUT
of course, for long term benefit and more complex problems, spending time
learning VBA is definitely the way to go and for experts like yourself, the
only one way to go.

Typically, people expect 'turn-key' responses to their issues. When VBA
is involved it raises the Q of whether this is a persistent need for
job requirements, OR somebody's school project, OR just for personal
interest. When it becomes a repeating issue I assume the OP has more
than a "one off" interest in using VBA and so I expect some degree of
interest toward 'learning' VBA be demonstrated. That concludes that not
all my replies will contain gratuitous 'turn-key' code. I'm a staunch
supporter of 'self-learning' and so will most likely steer others
toward learning resources. My learning is an ongoing affair. While I'm
most grateful to have been mentored by some of the leading Excel VBA
minds on the planet, all of my growth has been through practical 'trial
& error' implementation of the concepts being presented by the various
sources I draw from. This NG has some highly skilled contributors that
I consider to be 'sage' level compared to me, and their contributions
are very appreciated. I just hope I can reciprocate in kind some
measure of what I've received here!

As you're probably aware, VBA is useful for automating simple (but
repetitive) tasks/utilities to developing full blown application level
solutions. The latter is mostly what I do, but that also includes
implementing many reusable 'utility' components made up of repetitive
procedures. In my case, importing data from delimited 'plain text'
files is fairly common and so I've made a point of making that a
trivial (but consistently reliable) process using built-in VB[A} file
I/O functions.

I hope you continue to share your expertise in the NGs. I'll be the
first to admit that 'learning' and 'change' ARE the only way to move
forward. Your contributions to that process will always be
appreciated...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[William B. (Billby)]

Hi again Garry,

It was interesting to learn about your path to becoming such an Excel-ent VBA guru. Experience
gained as machinist/toolmaker must have helped you to focus on the detail and precision of
programming. In part that could explain the accuracy and clarity of your many helpful posts.

For my part I did some programming in Basic and Fortran in the early 1970s towards a Computer
Science qualification. Computer work then was carried out using teletypes, punched paper tape and
punched cards for Fortran. There were no VDUs. Memory and storage space were severely limited and
students had to book terminal and processing time. I did what was needed for the course but decided
then that programming was not for me. Instead I focused on computer hardware and communications.
Little did I realise that programming would become so easy to do in the years to come...
Especially now that we have 'the ribbon' in Excel 2007 and beyond!

Seriously, during my working life I was associated with keen programmers to consult when needed. Now
of course I can avail myself of the many talents freely given in these Excel groups. But you can see
why as a first choice for 'one off' tasks I always try to find an easier way first...

Stephen Hawking and his battle with Lou Gehrig's disease first came to my notice when I tried to
understand his book "A brief History of Time" in the late 1980s [a lost cause for me.]

I think it is absolutely remarkable that you can make such valuable contributions to so many groups
while at the same time dealing with the debilitating effects of such a serious and progressive
disease. I hope you will be able continue doing so for many years to come, as I'm sure do all those
who benefit from your expert help here.

Many thanks for all your efforts and response,

William B.

 

[GS]

William B. (Billby) formulated the question :

Hi again Garry,

It was interesting to learn about your path to becoming such an Excel-ent VBA
guru. Experience gained as machinist/toolmaker must have helped you to focus
on the detail and precision of programming. In part that could explain the
accuracy and clarity of your many helpful posts.

That certainly applies to programming CNC machines, where the tiniest
mistake has catastrophic consequences. The primary language is Basic
and so transition to VBA (and later to VB6) was fairly easy. CNC
programming is primarily macros and subs and so is similar in many
respects to VBA.

For my part I did some programming in Basic and Fortran in the early 1970s
towards a Computer Science qualification. Computer work then was carried out
using teletypes, punched paper tape and punched cards for Fortran. There were
no VDUs. Memory and storage space were severely limited and students had to
book terminal and processing time. I did what was needed for the course but
decided then that programming was not for me. Instead I focused on computer
hardware and communications. Little did I realise that programming would
become so easy to do in the years to come...
Especially now that we have 'the ribbon' in Excel 2007 and beyond!

That's pretty much how it was for me back in college days (late
60s/early 70s). The first real world job I got did things the same way
(punch cards and paper tape). Back then our idea of a spreadsheet was
to scotch tape pages from a multi-column columnar pad. I was elated
when Lotus123 came along. I switched to Excel in v4 because the college
where I was teaching switched to MS Office.

I was ticked off when the ribbon emerged! Thanks to Ron deBruin's
efforts that's no longer an issue with me. At the time, though, I went
to great lengths to replace all my Excel apps with stand-alone VB6.exe
solutions via fpSpread.ocx! I was able to duplicate everything I was
doing in Excel PLUS create my own UI. Unfortuantely, most clients want
MS Excel solutions still so I just go along with the demand.

Seriously, during my working life I was associated with keen programmers to
consult when needed. Now of course I can avail myself of the many talents
freely given in these Excel groups. But you can see why as a first choice for
'one off' tasks I always try to find an easier way first...

This is always a first approach with me too, but when clients want
productivity solutions I opt to that regardless. I do a lot of
'repairing' of delimited files using TextPad. This is the text editor I
ship with my cnc files manager apps because it supports syntax
highlighting and line numbering. It also supports having multiple files
with the same filename open (as long as they're from different
locations). This is good for working with machine programs that utilize
similar blocks of code.

Stephen Hawking and his battle with Lou Gehrig's disease first came to my
notice when I tried to understand his book "A brief History of Time" in the
late 1980s [a lost cause for me.]

i think he's an amazing guy! It's good that he has the support and
backing of the university going for him. It would be a tough go for him
otherwise, being how far along his condition has progressed. I keep as
active as I can in the ALS community, hoping I can impart some
encouragement and inspiration to those less fortunate than me. (Typical
runtime for ALS is 6 months to 5 years)

I think it is absolutely remarkable that you can make such valuable
contributions to so many groups while at the same time dealing with the
debilitating effects of such a serious and progressive disease. I hope you
will be able continue doing so for many years to come, as I'm sure do all
those who benefit from your expert help here.

It's definitely a 'give-n-take' situation because these NGs contribute
lots to my learning. Again, I don't consider myself an expert but I'm
happy I'm able to give back something of what I do know when I can!!!

Many thanks for all your efforts and response,

I thank you for your kind words and encouragement...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[William B. (Billby)]

Garry, I wasn't going to burden this group with any more 'historic' postings... but just this last
time... Not sure if there is an 'old farts' Historic Windows/Excel group(?) If not perhaps we
should start one

~~~ I was elated when Lotus123 came along. ~~~

Ahhh.. Lotus 123, it's great to reminisce with someone who know the old hardware, OSs and
applications. I started with Multiplan which was marvelous at first and of course led to Excel but
also used Lotus 123. How about Wordstar?, dBbase & Clipper? Corel? I can even remember way back
using a DOS 1.1 (or 1.2?) TI PC only equipped with floppies using a DOS without any sub-directories!
Enough already...

I was ticked off when the ribbon emerged! Thanks to Ron deBruin's efforts that's no longer an issue
with me. At the time, though, I went to great lengths to replace all my Excel apps with stand-alone
VB6.exe solutions via fpSpread.ocx! I was able to duplicate everything I was doing in Excel PLUS
create my own UI. Unfortuantely, most clients want MS Excel solutions still so I just go along with
the demand.

The one saving grace with the 'Ribbon' menu is the ability to create a Quick Access tool bar showing
icons of frequently used items. Mine has 28 icons at present, but I'm already losing track of what
many icons are for

My real beef with Excel 2007 was not the Ribbon, it was the nasty changes made to the charting
wizard which is probably my main Excel activity. A whole lot of cosmetic, flim flam, operational
changes to basically the same core wizard with very little real new charting functionality. Just
enough changes to destroy ten years of fast charting expertise & macros, requiring months of
re-learning, changes and re-work.

I think I've seen Ron deBruin's site, doesn't he mainly use Excel on a MAC? I'll check again,
perhaps there's some help for Excel 2007+ charting?

~~~ using TextPad ~~~ It also supports having multiple files with the same
filename open (as long as they're from different locations) ~~~

Such an obvious facility, don't you wish Excel and other Office applications could do that?

Enough..., bye for now.

William B.