How to create script to read multiple files, modify and write to new files

All,

I'm hoping someone can help. I'm working on a project where I have
about 30 csv files that I continuously have to edit, strip off the
header row, save and move to another folder where I upload them to an
FTP server.

I'd like to be able to create a vbscript that would automate the
process by reading each of the csv files in a folder skipping line 1
and writing them back to a file in a new folder.

I've seen bits and pieces of what I need in other posts but I can't
seem to get the whole process working..

Thanks in advance!


......Chris

cmoorhead@mindspring.com

mr_unreliable
Thu Dec 01 09:51:57 CST 2005

Chris, why don't you post what you have thus far,
and maybe we can comment on that.

If you are suggesting that I write the entire script for you,
then it will cost you a truckload of Heineken.

cheers, jw

Chris Moorhead wrote:
> All,
>
> I'm hoping someone can help. I'm working on a project where I have
> about 30 csv files that I continuously have to edit, strip off the
> header row, save and move to another folder where I upload them to an
> FTP server.
>
> I'd like to be able to create a vbscript that would automate the
> process by reading each of the csv files in a folder skipping line 1
> and writing them back to a file in a new folder.
>
> I've seen bits and pieces of what I need in other posts but I can't
> seem to get the whole process working..
>
> Thanks in advance!
>
>
> ......Chris
>
> cmoorhead@mindspring.com
>

\
Thu Dec 01 11:00:24 CST 2005

> I'm hoping someone can help. I'm working on a project where I have
> about 30 csv files that I continuously have to edit, strip off the
> header row, save and move to another folder where I upload them to an
> FTP server.

> I'd like to be able to create a vbscript that would automate the
> process by reading each of the csv files in a folder skipping line 1
> and writing them back to a file in a new folder.

Need more input. Do you want to do all 30+ at once? Are all
30+ CSV files in the same place (computer/drive/folder)? Do all edited files go
to the same target folder? Assuming the answer is "yes" to everything, then this
should work:

'---------------------------------------
source="d:\source"
target="d:\target"

Set fso = CreateObject("Scripting.FileSystemObject")
Set src=fso.getFolder(source)

for each file in src.files
if file.type="Comma Delimited Array" then
Set textStream=file.OpenAsTextStream(1)
textStream.skipLine
data=textStream.readAll
textStream.close
set oFile=fso.CreateTextFile(target & "\" & file.name)
oFile.write data
oFile.close
end if
next
'--------------------------------------------
--
Crash

Chris
Thu Dec 01 13:48:09 CST 2005

Thanks much!

I created the script and ran it but nothing happens.

Here's what it looks like.

'Read csv files minus the header and write to a file
source="C:\Projects\Oracle Conversion Scripts"
target="C:\Projects\Oracle Conversion Scripts\Converted files"

Set fso = CreateObject("Scripting.FileSystemObject")
Set src=fso.getFolder(source)

for each file in src.files
if file.type="Comma Delimited Array" then
Set textStream=file.OpenAsTextStream(1)
textStream.skipLine
data=textStream.readAll
textStream.close
set oFile=fso.CreateTextFile(target & "\" & file.name)
oFile.write data
oFile.close
end if
next


.........Chris


On Thu, 1 Dec 2005 12:00:24 -0500, "\"Crash\" Dummy"
<dvader@deathstar.mil> wrote:

>> I'm hoping someone can help. I'm working on a project where I have
>> about 30 csv files that I continuously have to edit, strip off the
>> header row, save and move to another folder where I upload them to an
>> FTP server.
>
>> I'd like to be able to create a vbscript that would automate the
>> process by reading each of the csv files in a folder skipping line 1
>> and writing them back to a file in a new folder.
>
>Need more input. Do you want to do all 30+ at once? Are all
>30+ CSV files in the same place (computer/drive/folder)? Do all edited files go
>to the same target folder? Assuming the answer is "yes" to everything, then this
>should work:
>
>'---------------------------------------
>source="d:\source"
>target="d:\target"
>
>Set fso = CreateObject("Scripting.FileSystemObject")
>Set src=fso.getFolder(source)
>
>for each file in src.files
> if file.type="Comma Delimited Array" then
> Set textStream=file.OpenAsTextStream(1)
> textStream.skipLine
> data=textStream.readAll
> textStream.close
> set oFile=fso.CreateTextFile(target & "\" & file.name)
> oFile.write data
> oFile.close
> end if
>next
>'--------------------------------------------

\
Thu Dec 01 15:07:55 CST 2005

> I created the script and ran it but nothing happens.

It's a little hard to debug long distance. Do you get any error messages? Does
the target folder exist? The script does not create the folder.

I ran the script with a collection of .csv files that I have, and it worked
fine.
--
Crash

Chris
Thu Dec 01 17:31:19 CST 2005

No errors, target folder exists.....




I'll try on another computer.


....Chris


On Thu, 1 Dec 2005 16:07:55 -0500, "\"Crash\" Dummy"
<dvader@deathstar.mil> wrote:

>> I created the script and ran it but nothing happens.
>
>It's a little hard to debug long distance. Do you get any error messages? Does
>the target folder exist? The script does not create the folder.
>
>I ran the script with a collection of .csv files that I have, and it worked
>fine.

\
Thu Dec 01 18:14:30 CST 2005

> No errors, target folder exists.....

Open "Explorer>Tools>Folder Options>File Types" and see if .csv is a defined
type. If it isn't, you will either have to enter it or modify the script to
identify the extension instead of the file type.

Replace this:
if file.type="Comma Delimited Array" then

With this:
fname=lcase(file.name)
if right(fname,4)=".csv" then
--
Crash

Chris
Fri Dec 02 07:24:28 CST 2005

That did it!

CSV file type is registered as MS Excel but the substitution you
offered made the difference.

Thank you very much for your time, expertise, patience and quick
response!


..........Chris


On Thu, 1 Dec 2005 19:14:30 -0500, "\"Crash\" Dummy"
<dvader@deathstar.mil> wrote:

>> No errors, target folder exists.....
>
>Open "Explorer>Tools>Folder Options>File Types" and see if .csv is a defined
>type. If it isn't, you will either have to enter it or modify the script to
>identify the extension instead of the file type.
>
>Replace this:
>if file.type="Comma Delimited Array" then
>
>With this:
>fname=lcase(file.name)
>if right(fname,4)=".csv" then

\
Fri Dec 02 08:34:08 CST 2005

> Thank you very much for your time, expertise, patience and quick
> response!

You're just lucky I don't have a life!

(You're welcome.)
--
Crash