First off, completely brand new to all of this. I have been given the task at work to break down a csv file that is a dump of multiple users address books, and sorts them out according to the email addresses. What I'm looking to do is create a macro that would be able to distinguish a change in email address, and make a new file when it notices the change. Example:

A B
Data
Data
Data
Data

Ideally, I would like to create a file with the rows only in one file, followed by in another file, using the same macro if possible. Not sure if this is even something that can be done, but I figured I'd take the shot considering the Excel file I have has over 300 address books and 6000 columns. Thanks in advance for any advice or suggestions!

On Monday, December 16, 2013 11:47:33 PM UTC-5, wrote:
First off, completely brand new to all of this. I have been given the task at work to break down a csv file that is a dump of multiple users address books, and sorts them out according to the email addresses. What I'm looking to do is create a macro that would be able to distinguish a change in email address, and make a new file when it notices the change. Example:



A B

Data

Data

Data

Data



Ideally, I would like to create a file with the rows only in one file, followed by in another file, using the same macro if possible. Not sure if this is even something that can be done, but I figured I'd take the shot considering the Excel file I have has over 300 address books and 6000 columns. Thanks in advance for any advice or suggestions!

One further thing I forgot to add, I would ideally like the name of the file being created be the name of email address from Column A.

Thanks again for anyone who even takes the time to read this.

You need to clarify a few things...

1. Your example demos a change in email *server*, not email address.
This is not a problem but needs to be clarified. (email address
is different than email address )

2. Your explanation suggests there are severall CSV files containing
data, AND that each CSV file is to be opened in Excel to be processed
into new workbooks. I do not recommend doing this as it will be orders
of magnitude faster and more efficient to just read the CSV files into
memory (via normal VB I/O functions) and process them there without
opening them in Excel.

3. <FWIW
In terms of going forward with admin tasks, it doesn't seem very
efficient (to me, at least) to store the different email data in
separate files. Better (IMO) to put them all in one file on separate
sheets. This way, the file can be accessed as a database where each
sheet is a data table. This will allow you to access the data via
normal ADODB without having to open the file in Excel.

If you need separate files:
The data could be stored in separate CSV files (better than using
workbooks, IMO) for individual access based on server name. In this
case, each CSV file serves as an ADODB data table that you can access
without having to open each file. Alternatively, you can also read each
file into memory as previously mentioned.

--
Garry

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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

On Tuesday, December 17, 2013 12:47:47 PM UTC-5, GS wrote:
You need to clarify a few things...



1. Your example demos a change in email *server*, not email address.

This is not a problem but needs to be clarified. (email address

is different than email address )



2. Your explanation suggests there are severall CSV files containing

data, AND that each CSV file is to be opened in Excel to be processed

into new workbooks. I do not recommend doing this as it will be orders

of magnitude faster and more efficient to just read the CSV files into

memory (via normal VB I/O functions) and process them there without

opening them in Excel.



3. <FWIW

In terms of going forward with admin tasks, it doesn't seem very

efficient (to me, at least) to store the different email data in

separate files. Better (IMO) to put them all in one file on separate

sheets. This way, the file can be accessed as a database where each

sheet is a data table. This will allow you to access the data via

normal ADODB without having to open the file in Excel.



If you need separate files:

The data could be stored in separate CSV files (better than using

workbooks, IMO) for individual access based on server name. In this

case, each CSV file serves as an ADODB data table that you can access

without having to open each file. Alternatively, you can also read each

file into memory as previously mentioned.



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion







---

This email is free from viruses and malware because avast! Antivirus protection is active.

http://www.avast.com

I really appreciate the reply. If I wasn't so new to the Excel field, I would have been able to explain this more clearly the first time.

The file I have been given is in essence a dump file. We are switching our mail server, and the file is a dump of all of our users address books. Originally, it contained several columns of information, which I have consolidated down to two colums, the email address, and the information that needs to be added in .abook form. An .abook is simply a glorified text file. The abook file is stored as k .

Now, my problem is, for each address book entry for a particular email address, it uses a new column. Example

address 1
address 2
address 3
address 1
address 2
address 1

I know I can simply copy and paste the address field and create a text file with it, but the issue is the CSV file has over 6000 columns in it. This would be a lengthy procedure. Just was looking for an easier alternative than that.

I really appreciate the reply. If I wasn't so new to the Excel
field, I would have been able to explain this more clearly the first
time.

The file I have been given is in essence a dump file. We are
switching our mail server, and the file is a dump of all of our users
address books. Originally, it contained several columns of
information, which I have consolidated down to two colums, the email
address, and the information that needs to be added in .abook form.
An .abook is simply a glorified text file. The abook file is stored
as k .

Now, my problem is, for each address book entry for a particular
email address, it uses a new column. Example

address 1
address 2
address 3
address 1
address 2
address 1

I know I can simply copy and paste the address field and create a
text file with it, but the issue is the CSV file has over 6000
columns in it. This would be a lengthy procedure. Just was looking
for an easier alternative than that.

Okay.., that helps some. So then the addresses are not email addresses?

I've never heard of an address book 'dump' (or any other kind of data
dump) that stacks data to the right. Normally it would be appended
below existing data. (I doubt your source address books have 6000 data
fields!)

What's needed is to know how the data is laid out in the dump file, and
what data gets parsed into your .abook file. The fact that the .abook
file is a text file is a bonus that makes the task all that much
simpler to perform. Can you provide a sample of the source data dump
file?

--
Garry

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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

On Tuesday, December 17, 2013 10:31:51 PM UTC-5, GS wrote:
I really appreciate the reply. If I wasn't so new to the Excel

field, I would have been able to explain this more clearly the first

time.



The file I have been given is in essence a dump file. We are

switching our mail server, and the file is a dump of all of our users

address books. Originally, it contained several columns of

information, which I have consolidated down to two colums, the email

address, and the information that needs to be added in .abook form.

An .abook is simply a glorified text file. The abook file is stored

as k .



Now, my problem is, for each address book entry for a particular

email address, it uses a new column. Example



address 1

address 2

address 3

address 1

address 2

address 1



I know I can simply copy and paste the address field and create a

text file with it, but the issue is the CSV file has over 6000

columns in it. This would be a lengthy procedure. Just was looking

for an easier alternative than that.



Okay.., that helps some. So then the addresses are not email addresses?



I've never heard of an address book 'dump' (or any other kind of data

dump) that stacks data to the right. Normally it would be appended

below existing data. (I doubt your source address books have 6000 data

fields!)



What's needed is to know how the data is laid out in the dump file, and

what data gets parsed into your .abook file. The fact that the .abook

file is a text file is a bonus that makes the task all that much

simpler to perform. Can you provide a sample of the source data dump

file?



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion







---

This email is free from viruses and malware because avast! Antivirus protection is active.

http://www.avast.com


The reason the second row is only data is because I've already consolidated it to the format that the .abook requires in the file. A sample line looks like this.

A B
Rose|Rose|Last |
|

Each address book entry is an individual column in the workbook.

Really appreciate your advice in this matter

On Tuesday, December 17, 2013 11:49:27 PM UTC-5, wrote:
On Tuesday, December 17, 2013 10:31:51 PM UTC-5, GS wrote:

I really appreciate the reply. If I wasn't so new to the Excel



field, I would have been able to explain this more clearly the first



time.







The file I have been given is in essence a dump file. We are



switching our mail server, and the file is a dump of all of our users



address books. Originally, it contained several columns of



information, which I have consolidated down to two colums, the email



address, and the information that needs to be added in .abook form.



An .abook is simply a glorified text file. The abook file is stored



as k .







Now, my problem is, for each address book entry for a particular



email address, it uses a new column. Example







address 1



address 2



address 3



address 1



address 2



address 1







I know I can simply copy and paste the address field and create a



text file with it, but the issue is the CSV file has over 6000



columns in it. This would be a lengthy procedure. Just was looking



for an easier alternative than that.







Okay.., that helps some. So then the addresses are not email addresses?







I've never heard of an address book 'dump' (or any other kind of data



dump) that stacks data to the right. Normally it would be appended



below existing data. (I doubt your source address books have 6000 data



fields!)







What's needed is to know how the data is laid out in the dump file, and



what data gets parsed into your .abook file. The fact that the .abook



file is a text file is a bonus that makes the task all that much



simpler to perform. Can you provide a sample of the source data dump



file?







--



Garry







Free usenet access at http://www.eternal-september.org



Classic VB Users Regroup!



comp.lang.basic.visual.misc



microsoft.public.vb.general.discussion















---



This email is free from viruses and malware because avast! Antivirus protection is active.



http://www.avast.com





The reason the second row is only data is because I've already consolidated it to the format that the .abook requires in the file. A sample line looks like this.



A B

Rose|Rose|Last |

|



Each address book entry is an individual column in the workbook.



Really appreciate your advice in this matter

Someone gave me this code, but it doesn't seem to be exactly what I need, and I'm too dumb to be able to fine tune it :-)


Public Sub Transfer()
Dim C_ell As Range, P_ath As String
'
'Text files will be saved in the same folder as this macro file.
P_ath = ActiveWorkbook.Path
'
'Sheet1 can be changed to your main data sheet
'Same thing for Sheet2, use the you want but change it in all places in the code
'
Sheets("Sheet1").Select
Range("A1").Select
Range("A1").EntireRow.Copy
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
For Each C_ell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
If C_ell = C_ell.Offset(-1, 0) Then
C_ell.EntireRow.Copy
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
Else
Sheets("Sheet2").Select
Range("A1").EntireRow.Delete
Sheets("Sheet2").Copy
ActiveWorkbook.SaveAs Filename:= _
P_ath & "\" & Range("A1") & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWindow.Close False
ActiveSheet.Cells.ClearContents
Sheets("Sheet1").Select
End If
Next
'this section saves the last email in a text file
Sheets("Sheet2").Select
Range("A1").EntireRow.Delete
Sheets("Sheet2").Copy
ActiveWorkbook.SaveAs Filename:= _
P_ath & "\" & Range("A1") & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWindow.Close False
ActiveSheet.Cells.ClearContents
Sheets("Sheet1").Select

End Sub

I see that! Also doesn't do what I suggest!

I still would like to see a sample of the CSV dump file along with a
sample output file<g. Can you upload to a public webshare and post a
link so I can download and work with 'real' data?

--
Garry

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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com