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?

elyas wrote:

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.

--
I've come to realize tonight, my dear, the end of time is not so far away.
We cannot pray to save our lives.

On Jun 11, 7:51*am, elyas wrote:
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?

--
elyas

* 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

on 6/11/2012, Chrisso supposed :
On Jun 11, 7:51*am, elyas wrote:
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?

--
elyas

* 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

On 12/06/2012 5:28 AM, GS wrote:
on 6/11/2012, Chrisso supposed :
On Jun 11, 7:51 am, elyas wrote:
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?

--
elyas

* 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.


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.

William B. (Billby) laid this down on his screen :
On 12/06/2012 5:28 AM, GS wrote:
on 6/11/2012, Chrisso supposed :
On Jun 11, 7:51 am, elyas wrote:
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?

--
elyas

* 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.


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

On 12/06/2012 12:44 PM, GS wrote:
William B. (Billby) laid this down on his screen :
On 12/06/2012 5:28 AM, GS wrote:
on 6/11/2012, Chrisso supposed :
On Jun 11, 7:51 am, elyas wrote:
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?

--
elyas

* 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.


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!

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.