adjusting columns

[csb72]

Hello everyone,

You now the tasks that I have will probably be easy for some of you well seasoned with MS. Some of you may already have such examples just collecting dust on your hard drive. I don't and wish I did.

I first want to say that I am super impressed with what I have read and seen with this program. It is my hope that I can get it to work with the routine events and programs that I am working with on a daily basis.

So in order to figure out whether or not this program would work for me, I started looking for examples in the forum. Unfortunately I was unable to find any specific reference to the specific challenge that I am working with.

You see I am working on moving columns in a .csv file into a different order so that I can upload/import the entire file automagically (using MS) into a specific autoresponder program that I am using (more specific details below). So any help I can get on the following would be greatly appreciated:


Now for the detailed version:

The reason I downloaded the trial for MS is so that I can manipulate some .csv files. You see I manage an autoresponder system that requires me to preform the same upload/import tasks (of business contacts) every day and I would love to be able to automate this.

-----------------------------
TASK 1:
I must first login to the site that has the new members who are asking for information every day and download the .csv file that contains those members information into folder1. I usually label the file with the companies name and add the date that I downloaded the file for example cem 09172007 or cem_09172007 (the last one being the preferred method). I then must open that file in a spreadsheet program like openoffice.org calc (or like most of the forum users prefer excel) and select the specific columns that need to be moved and move them to the correct order to upload them into the autoresponder program.
-----------------------------

You see most of the examples on the forum cover row based examples and as you can see I must manipulate the columns.

-----------------------------
TASK 2:
After the columns are arranged in the correct order I then save the file in folder2 with a slightly modified file name for example cem ml 09172007 or cem_ml_09172007 (the last one being the preferred method).

I then open the autoresponder program and proceed to import the finished and already in correct formatted file that is in folder b. Once all members are in, the autoresponder system the takes over.
-----------------------------

The tasks are not difficult to do each day, they are just so time consuming.

I suppose one of the biggest challenges that I am going to have is to setup MS to:

1. search in folder1 for that days specific downloaded file so that it can be opened and adjusted.
2. search and adjust the columns of that days specific downloaded file, from within a spreedsheet program, into the specific order needed for the autoresponder system and save it into folder2.

the rest will probably not be as difficult.

So as you guy's can see. I could really use some help because I really don't know were to start. Any assistance would be greatly appreciated.

Thank you in advance,
Carl

p.s. I can attach some basic files if necessary. You can probably imagine though that the files consist of fname, lname, email. Some times the files have other fields too like home_phone, mobile_phone, address, city, state, zip, and several other fields usually no more than 25 or 30 fields in total. I would imagine that once I get started with a few fields shown as examples that adding others won't be to difficult.

Why .csv files? Well because all the programs I work with (including the autoresponder system) use .csv files and by being able to manipulate these files I will be able to get all system automated and working together.

[Marcus Tettmar]

One wonders why you'd need to reorder the columns in a CSV file. If the file has headers this shouldn't be necessary. So I guess it has no headers, or the target app doesn't look at the headers and expects a specific format.

No matter. Here's a bit of code which you can use to reorder the columns in a CSV file on the fly. No need to manipulate a spreadsheet program - this does it programmatically.

Code: Select all

//Set up the input and output files
Let>infile=%SCRIPT_DIR%\example.csv
Let>newfile=%SCRIPT_DIR%\example_reordered.csv

//delete the output file if it already exists
IfFileExists>newfile
  DeleteFile>newfile
Endif

//comma is the CSV delimiter
Let>comma=,

//read the CSV file into memory
ReadFile>infile,FileData

//split the file into an array of lines
Separate>FileData,CRLF,Lines

//loop through each line
Let>k=0
Repeat>k
  Let>k=k+1

  //split each line into array of fields (columns)
  Let>ThisLine=Lines_%k%
  Separate>ThisLine,comma,Fields

  //we now have Fields_1, Fields_2 and Fields_3 .. 
  //Output them to new file in new order .. add more fields depending on your CSV file ...
  WriteLn>newfile,r,%Fields_2%%comma%%Fields_3%%comma%%Fields_1%

Until>k=Lines_Count

//lets open the new file in notepad to see the results
Run>Notepad.exe %newfile%

The example above works on this example CSV file:

red,large,round
blue,small,square
yellow,medium,round
green,large,square

Simply modify the WriteLn line in the above script code to output the number of fields and define the order of output. Mine just outputs 3 fields in column order 3, 1, 2. You can do as you wish.

To run the above example, copy the code to a new script and save the example CSV to example.csv in the same folder as the script.

[Me_again]

Although it may work in a particular application that simple method of parsing a .csv isn't very reliable because it doesn't have any way to handle quoted text which can include commas

[Marcus Tettmar]

Nothing is insurmountable. Just a bit trickier:

Code: Select all

Let>line=1,"with, comma",5,"sally",6,"green, yellow"
Let>columns=6
Let>delim=,
GoSub>GetFields

MessageModal>Field_1
MessageModal>Field_2
MessageModal>Field_3
MessageModal>Field_4
MessageModal>Field_5
MessageModal>Field_6

SRT>GetFields

Let>px=1
Length>line,len_line
Let>ThisField=
Let>Fields=0
Repeat>px
  MidStr>line,px,1,pchar
  If>pchar="
     //start of string value
	 MidStr>line,{%px%+1},len_line,rest
	 Pos>",rest,1,pend
	 MidStr>rest,1,{%pend%-1},ThisField
	 Let>ThisField="%ThisField%"
	 Let>px={%px%+%pend%}
  Else
    If>pchar=delim
       //new field
  	   Let>Fields=Fields+1
  	   Let>Field_%Fields%=ThisField
	   Let>ThisField=
	Else
	  Let>ThisField=%ThisField%%pchar%
    Endif
  Endif
  Let>px=px+1
Until>px>len_line

Let>Fields=Fields+1
Let>Field_%Fields%=ThisField

END>GetFields

[Me_again]

Very nice, elegantly coded, and you beat me to it

[csb72]

Thank you guys for helping me with this. Especially mtettmar for the 2 examples of code. Also please forgive me for these messages being so long. I just want to make sure that you have every detail known so that you don't start helping and find out later that a big part was missing.

So, I attempted to implement the first (top) script and found that:

Results from running the example you gave me:

infile:
--------------------------------
email,fname,lname
[email protected],tom,thumb
[email protected],guy,pearce
[email protected],d,j
--------------------------------

newfile:
--------------------------------
fname,lname,email
tom,thumb,[email protected]
guy,pearce,[email protected]
d,j,[email protected]
d,j,Lines_5
--------------------------------

This script left an extra line at the end (d,j,Lines_5). ? So I played around with it and could not figure out how to stop this extra line.

also, a few other items of interest:

The delete existing file part of the script is clever but it leaves me no way to have a backup of the files that will be downloaded.

This is also the same with the infile (example.csv), every time I download the file it will replace the existing example.csv file. I must be able to backup these files and identify them one from the other (namely from day to day). It is also preferred that the file name has each day that it was downloaded in the file name (for example cem_09212007).

So when I setup the script to download the daily file, how can I get the script to name the file with the date in it (for example cem_09212007) (this is detailed below in section 2)?

This script also requires manual discovery of columns layout. I probably didn't give enough detail in my earlier posting. Please forgive me for that. I have attempted to revise my description below for this challenge in section 3 to include such detail.

Now on to the second script ...

The second script worked great, unfortunately it popped up message dialog boxes with the listed items in the script instead of pulling from a .csv file? I also attempted to play around with it to get the desired out come but I must admit I am not as proficient as you are at writing code.

Would you consider adjusting the second script to pull from a .csv file as well as the other items detailed below in the revised description?

So, here is the order of items/tasks that I must complete (revised to include more detail and column headers):

------------------------------
KEY:
= I will be taking care of this one.
[H] = Help; Hopefully you will help me with this one.
[?] = Don't know what to do about this one.
[X] = Completed.
[R] = Replaced once the scripts work.
[N] = No action needed.

Items/Tasks:
1. login to the site that has the new members who are asking for information daily.

2. download the .csv file that contains those members information into folder1. I usually label the file with the companies name and add the date to the end (for example cem_09212007; the 09212007 being the date part that must change on a daily basis).

[H?] 2a. how can I get the script to name the file with the date in it (for example cem_09212007)?

[R] 3a. open that file in a spreadsheet program like openoffice.org calc (or like most of the forum users prefer excel). This is what I am currently doing unfortunately.

OR

[H?] 3b. use MS and run the script that you guys are helping me with to:

[H?] 3b1. the script should search in folder1 for that days specific downloaded file (for example cem_09212007) and opens this file (I guess in memory) for adjustment.

[H?] 3b2. the script then searches each header of the columns and moves them around until each column is in the specified order set in the script (for example email,lname,fname,mname,home_number,mobile_number,home_address,home_city,
home_state,home_zip,date_time_stamp,ipaddress,any_other_fields_that_not _covered_earlier_or_left_over).

[H?] 3b3. after the columns are arranged in the correct order, the script then save the adjusted file in folder2 with a slightly modified file name (for example cem_ml_09212007). Please notice the "ml" in the middle of the last example. This format will make it so that the file will be able to be imported into the autoresponder system.

4. open the autoresponder program.

[H?] 5. import the correctly formatted file from folder2 (for example
cem_ml_09212007).

[H?] 5a. how do I get the script to find that days .csv file (for example cem_ml_09212007)?

[N] 6. autoresponder system takes over and everything starts again on the next day.
------------------------------

If you were looking for something challenging ... I think I have helped you find it.

Thank you and have a fantastic day!
Carl

[Me_again]

There's a good thread here that will give you the tools to do the file renaming.

[Marcus Tettmar]

Hi,

I see the issue with the first script. It was looping too far (trying to do an extra line). I've fixed it and edited my post above.

The second script is what we call here AN EXAMPLE and in order to show what it is doing it displays the results in message boxes. The code sample reorders fields in a list. It is nowhere near complete for your purposes but could be used to reorder a list of fields inside a loop that loops through a file.

>[H?] 2a. how can I get the script to name the file with the
>date in it (for example cem_09212007)?

use the Year, Month and Day functions. See:
http://www.mjtnet.com/blog/2006/01/23/u ... variables/

> [H?] 3b1. the script should search in folder1 for that
>days specific downloaded file (for example cem_09212007)
>and opens this file (I guess in memory) for adjustment.

Use GetFileList. Loop through the list returned and look for today's date in the filename. Or use FileDate.

Your other questions are mostly duplications. The answers are all here.

I don't personally have the time to write your script for you. But we can help you write it yourself. If you would prefer someone to write it for you, we have people who can help and I can put you in touch with freelance coders.

>If you were looking for something challenging ... I think I
>have helped you find it.

What you are asking for is not that challenging for us. Creating Macro Scheduler was a little more challenging

[csb72]

First let me say thank you for the help that you did give. It was much appreciated even though I am still at square one. I suppose that I was asking to much of you with this difficult challenge.

The above "The answers are all here." doesn't lead me to any answers as their is no link/url within the words.

Would you at least adjust the second script to work like the first script (i.e. pull from .csv file, sort columns, and open the results in notepad)?

Then I at least have something that I can work with.

Then I would like to contact one of your freelance coders that you said you would put me in touch with. Since you don't have the time.

Thank you,
Carl

[Marcus Tettmar]

No problem. My code used a subroutine so to merge the two pieces it only required a couple of small changes and a call to the subroutine:

Code: Select all

//Set up the input and output files
Let>infile=%SCRIPT_DIR%\example.csv
Let>newfile=%SCRIPT_DIR%\example_reordered.csv

//delete the output file if it already exists
IfFileExists>newfile
  DeleteFile>newfile
Endif

//comma is the CSV delimiter
Let>comma=,

//read the CSV file into memory
ReadFile>infile,FileData

//split the file into an array of lines
Separate>FileData,CRLF,Lines

//loop through each line
Let>k=0
Repeat>k
  Let>k=k+1

  //split each line into array of fields (columns)
  Let>line=Lines_%k%
  GoSub>GetFields

  //we now have Field_1, Field_2 and Field_3 .. 
  //Output them to new file in new order .. add more fields depending on your CSV file ...
  WriteLn>newfile,r,%Field_2%%comma%%Field_3%%comma%%Field_1%

Until>k=Lines_Count

//lets open the new file in notepad to see the results
Run>Notepad.exe %newfile%
Goto>end

// *** SUBROUTINE COPING WITH QUOTE DELIMITED STRINGS *** //
SRT>GetFields

Let>delim=,
Let>px=1
Length>line,len_line
Let>ThisField=
Let>Fields=0
Repeat>px
  MidStr>line,px,1,pchar
  If>pchar="
     //start of string value
	 MidStr>line,{%px%+1},len_line,rest
	 Pos>",rest,1,pend
	 MidStr>rest,1,{%pend%-1},ThisField
	 Let>ThisField="%ThisField%"
	 Let>px={%px%+%pend%}
  Else
    If>pchar=delim
       //new field
  	   Let>Fields=Fields+1
  	   Let>Field_%Fields%=ThisField
	   Let>ThisField=
	Else
	  Let>ThisField=%ThisField%%pchar%
    Endif
  Endif
  Let>px=px+1
Until>px>len_line

Let>Fields=Fields+1
Let>Field_%Fields%=ThisField

END>GetFields
Label>end

[csb72]

Thank you for the quick reply. Unfortunately I'm getting a funny result.

Here is the code with the small adjustments that I made:

Code: Select all

//Set up the date
Year>y
Month>m
Day>d

//Set up the input and output files
Let>infile=C:\Documents and Settings\captian crunch\My Documents\Macro Scheduler\folder1\cem_%y%%m%%d%.csv
Let>newfile=C:\Documents and Settings\captian crunch\My Documents\Macro Scheduler\folder2\cem_ml_%y%%m%%d%.csv

//delete the output file if it already exists
IfFileExists>newfile
  DeleteFile>newfile
Endif

//comma is the CSV delimiter
Let>comma=,

//read the CSV file into memory
ReadFile>infile,FileData

//split the file into an array of lines
Separate>FileData,CRLF,Lines

//loop through each line
Let>k=0
Repeat>k
  Let>k=k+1

  //split each line into array of fields (columns)
  Let>line=Lines_%k%
  GoSub>GetFields

  //we now have Field_1, Field_2 and Field_3 .. 
  //Output them to new file in new order .. add more fields depending on your CSV file ...
  WriteLn>newfile,r,%Field_2%%comma%%Field_3%%comma%%Fields_4%%comma%%Field_1%

Until>k=Lines_Count

//lets open the new file in notepad to see the results
Run>Notepad.exe %newfile%
Goto>end

// *** SUBROUTINE COPING WITH QUOTE DELIMITED STRINGS *** //
SRT>GetFields

Let>delim=,
Let>px=1
Length>line,len_line
Let>ThisField=
Let>Fields=0
Repeat>px
  MidStr>line,px,1,pchar
  If>pchar="
     //start of string value
	 MidStr>line,{%px%+1},len_line,rest
	 Pos>",rest,1,pend
	 MidStr>rest,1,{%pend%-1},ThisField
	 Let>ThisField="%ThisField%"
	 Let>px={%px%+%pend%}
  Else
    If>pchar=delim
       //new field
  	   Let>Fields=Fields+1
  	   Let>Field_%Fields%=ThisField
	   Let>ThisField=
	Else
	  Let>ThisField=%ThisField%%pchar%
    Endif
  Endif
  Let>px=px+1
Until>px>len_line

Let>Fields=Fields+1
Let>Field_%Fields%=ThisField

END>GetFields
Label>end

As you can see I:

1. added the date to file names (which works great)
2. adjusted the infile and newfile (which is also working great)
3. added %comma%%field_4% to the WriteLn (?)

Here is the infile:

email,sal,fname,lname
[email protected],mr,j,j
[email protected],mr,d,j
[email protected],ms,sally,fields

Here is the newfile(output):

sal,fname,%Fields_4%,email
09r,j,%Fields_4%,[email protected]
09r,21,%Fields_4%,[email protected]
09s,sally,%Fields_4%,[email protected]

any idea why I am getting this?

Thanks,
Carl

[/code]

[Marcus Tettmar]

In this version it should be Field_4 not Fields_4

[csb72]

Isn't it amazing how one letter can make such a mess.

I changed from Fields_4 to Field_4 and get this:

sal,fname,lname,email
09r,j,j,[email protected]
09r,21,j,[email protected]
09s,sally,3,[email protected]

???

Thanks,
Carl

[Marcus Tettmar]

Variable confusion. This one works:

Code: Select all

//Set up the date
Year>gyr
Month>gm
Day>gd

//Set up the input and output files
Let>infile=C:\Documents and Settings\captian crunch\My Documents\Macro Scheduler\folder1\cem_%gyr%%gm%%gd%.csv
Let>newfile=C:\Documents and Settings\captian crunch\My Documents\Macro Scheduler\folder2\cem_ml_%gyr%%gm%%gd%.csv 

//delete the output file if it already exists
IfFileExists>newfile
  DeleteFile>newfile
Endif

//comma is the CSV delimiter
Let>comma=,

//read the CSV file into memory
ReadFile>infile,FileData

//split the file into an array of lines
Separate>FileData,CRLF,Lines

//loop through each line
Let>kount=0
Repeat>kount
  Let>kount=kount+1

  //split each line into array of fields (columns)
  Let>line=Lines_%kount%
  GoSub>GetFields

  //we now have Field_1, Field_2 and Field_3 .. 
  //Output them to new file in new order .. add more fields depending on your CSV file ...
  WriteLn>newfile,wln_r,%Field_2%%comma%%Field_3%%comma%%Field_4%%comma%%Field_1%

Until>kount=Lines_Count

//lets open the new file in notepad to see the results
Run>Notepad.exe %newfile%
Goto>end

// *** SUBROUTINE COPING WITH QUOTE DELIMITED STRINGS *** //
SRT>GetFields

Let>delim=,
Let>px=1
Length>line,len_line
Let>ThisField=
Let>_Fields=0
Repeat>px
  MidStr>line,px,1,pchar
  If>pchar="
     //start of string value
	 MidStr>line,{%px%+1},len_line,rest
	 Pos>",rest,1,pend
	 MidStr>rest,1,{%pend%-1},ThisField
	 Let>ThisField="%ThisField%"
	 Let>px={%px%+%pend%}
  Else
    If>pchar=delim
       //new field
  	   Let>_Fields=_Fields+1
  	   Let>Field_%_Fields%=ThisField
	   Let>ThisField=
	Else
	  Let>ThisField=%ThisField%%pchar%
    Endif
  Endif
  Let>px=px+1
Until>px>len_line

Let>_Fields=_Fields+1
Let>Field_%_Fields%=ThisField

END>GetFields
Label>end

[csb72]

I thought about that but I guess I just didn't look hard enough. I will from now on.

You are definitely a master coder!

The above "The answers are all here." that you typed doesn't lead me to any answers as their is no link/url within the words. Did you have something or somewhere in mind when you typed this?

I would also like to contact one of your freelance coders that you said you could put me in touch with.

Thank you for putting up with me today.

Have a fantastic weekend!
Carl

p.s. thought you might want to know that all but one of the original items/tasks that I listed above are complete. The only one that isn't is section 3b2.

If anyone wants to help me with section 3b2 from above (which is to be added to the already written code; thanks to mtettmar) it would be greatly appreciated. I am willing to pay for it's completion in accordance with my specs.