[newbie] Need a script to creat a .csv file

ASP Free Forums Sponsor:

April 27th, 2009, 01:34 PM

jcranestoltz

Registered User

Join Date: Apr 2009

Posts: 5

Time spent in forums: 1 h 7 m 28 sec
Reputation Power: 0

[newbie] Need a script to creat a .csv file

I'm using an OCR software to output a PDF and TXT file from the same source file. The PDF and TXT files will have the same filename and will be stored in the same folder directory. I need a script that will look at this folder directory, find the PDF and TXT files with the same filename, and create a CSV file that has the following information

Index1,Document.PDF,Document.TXT

This is needed to associate the 2 files with each other so they can be imported into our Document Mangement System using their proprietary software.

I can clarify if this sounds a little confusing, and I would appreciate anyone that could point me in the right direction.

Thanks,
Jason

April 28th, 2009, 01:27 AM

Nilpo

Shaolin Scripter!™

ASP Free Intermediate (1500 - 1999 posts)

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,905

Folding Points: 214558 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 1 Week 2 Days 11 h 16 m 7 sec
Reputation Power: 969

Although CSV files (much like XML files) are actually simple database formats, they are also nothing more than formatted text files. From a scripting perspective, this presents two way of creating/editing them.

The first approach is to edit them as simple text files using the FileSystemObject. You can simply write properly formatted strings to a flat file and save it when finished. This is the preferred option for creating new CSVs from scratch or simply appending new data.

The second option is to connect to the file as a database using ADODB. This provides a lot more power and flexibility but results is overly complicated code in some cases. This is the preferred method of editing existing files.

My question to you becomes, which one would you like to see?

__________________

Don't like me? Click it.

Scripting problems? Windows questions? Ask the Windows Guru!

Stay up to date with all of my latest content. Follow me on Twitter!

Help us help you! Post your exact error message with these easy tips!

April 28th, 2009, 06:59 AM

jcranestoltz

Registered User

Join Date: Apr 2009

Posts: 5

Time spent in forums: 1 h 7 m 28 sec
Reputation Power: 0

Thanks for the quick reply....

I would like to keep the code simple and I would only be creating new CSV files, not modifying existing ones. So the first method would be perfect.

Thanks,
Jason

April 28th, 2009, 01:06 PM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,905

Time spent in forums: 1 Week 2 Days 11 h 16 m 7 sec
Reputation Power: 969

Very good. Give this a try:

vb Code:

Original - vb Code

strFileName = "myfile.csv"
strDirectory = "C:\somedirectory\"
 
' Create CSV file
Const ForWriting = 2
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objCSV = objFso.OpenTextFile(strFileName, ForWriting, True)
 
' Iterate through PDF files in directory
Set objFolder = objFso.GetFolder(strDirectory)
Set colFiles = objFolder.Files
For Each objFile In colFiles
    If InStr(objFile.Name, ".pdf") > 0 Then
        AddEntry objFile.Path, objFile.Name
    End If
Next
 
objCSV.Close
 
Sub AddEntry(strPath, strPdf)
    strTxt = Replace(strPdf, ".pdf", ".txt")
    objCSV.WriteLine Chr(32) & strPath & strPdf & Chr(32) & "," & Chr(32) & strPath & strTxt & Chr(32)
End Sub

Notice that I'm surrounding each value in the CSV file with Chr(32) which is the ASCII character for double quotes.

April 29th, 2009, 10:43 AM

jcranestoltz

Registered User

Join Date: Apr 2009

Posts: 5

Time spent in forums: 1 h 7 m 28 sec
Reputation Power: 0

I tested the script this morning and its pretty close to what I need. I created the following directory and placed 2 PDF files (TEST1.pdf & TEST2.pdf) and 2 TXT files (TEST1.txt & TEST2.txt) within it. I then specified this directory in the script you supplied.

C:\OCRTEST

After running the script, this is how the CSV file appeared when opened in Notepad:

C:\OCRTEST\TEST1.pdfTEST1.pdf , C:\OCRTEST\TEST1.pdfTEST1.txt
C:\OCRTEST\TEST2.pdfTEST2.pdf , C:\OCRTEST\TEST2.pdfTEST2.txt

Is there any way to have it listed as below?

Index1,C:\OCRTEST\TEST1.PDF,C:\OCRTEST\TEST1.TXT
Index2,C:\OCRTEST\TEST2.PDF,C:\OCRTEST\TEST2.TXT

Thanks again for all your help. This has been extremely helpful so far!

May 6th, 2009, 08:44 AM

jcranestoltz

Registered User

Join Date: Apr 2009

Posts: 5

Time spent in forums: 1 h 7 m 28 sec
Reputation Power: 0

Anybody?

May 7th, 2009, 03:57 AM

sync_or_swim

Moderator

Join Date: Mar 2006

Location: South Wales

Posts: 3,632

Time spent in forums: 2 Months 3 Days 15 h 14 m 45 sec
Reputation Power: 1909

If looks as if the variable strPath contains the path and filename, so you might not need to add the filename again. You should be able to add a counter and pass this value to the function, try something like this, not tested:

Code:

strFileName = "myfile.csv"
strDirectory = "C:\somedirectory\" 
' Create CSV file
Const ForWriting = 2
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objCSV = objFso.OpenTextFile(strFileName, ForWriting, True) 

' Iterate through PDF files in directory
Set objFolder = objFso.GetFolder(strDirectory)
Set colFiles = objFolder.Files
Dim tempCounter
tempCounter = 1
For Each objFile In colFiles    
	If InStr(objFile.Name, ".pdf") > 0 Then        
		AddEntry objFile.Path, objFile.Name, tempCounter    
	End If
	tempCounter = tempCounter + 1
Next 
objCSV.Close 

Sub AddEntry(strPath, strPdf, intCount)    
	objCSV.WriteLine Chr(32) & "Index" & CStr(intCount) & "," & UCase(strPath) & Chr(32) & "," & Chr(32) & UCase(Left(strPath, Len(strPath)-3)) & "TXT" & Chr(32)
End Sub

May 7th, 2009, 04:01 AM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,905

Time spent in forums: 1 Week 2 Days 11 h 16 m 7 sec
Reputation Power: 969

I'm sorry, that's my mistake. Change all of the Chr(32)'s to Chr(34). That will fix it.

May 7th, 2009, 04:15 AM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,905

Time spent in forums: 1 Week 2 Days 11 h 16 m 7 sec
Reputation Power: 969

Sorry again, I completely overlooked the file name problem. Ignore my last post. Here's the complete corrected code that fixes both issues.

vb Code:

Original - vb Code

strFileName = "myfile.csv"
strDirectory = "C:\somedirectory\"
 
' Create CSV file
Const ForWriting = 2
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objCSV = objFso.OpenTextFile(strFileName, ForWriting, True)
 
' Iterate through PDF files in directory
Set objFolder = objFso.GetFolder(strDirectory)
Set colFiles = objFolder.Files
intCount = 1
For Each objFile In colFiles
    If InStr(objFile.Name, ".pdf") > 0 Then
        AddEntry objFile.Path, intCount
        intCount = intCount + 1
    End If
Next
 
objCSV.Close
 
Sub AddEntry(strPdf, intCount)
    strTxt = Replace(strPdf, ".pdf", ".txt")
    objCSV.Write Chr(34) & "Index" & intCount & Chr(34) & ","
    objCSV.WriteLine Chr(34) & strPdf & Chr(34) & "," & Chr(34) & strTxt & Chr(34)
End Sub

Comments on this post

micky agrees!

#10

May 8th, 2009, 09:14 AM

jcranestoltz

Registered User

Join Date: Apr 2009

Posts: 5

Time spent in forums: 1 h 7 m 28 sec
Reputation Power: 0

The script works perfectly. This will really help us save time. Thanks again for all your help Nilpo!

#11

May 8th, 2009, 12:41 PM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,905

Time spent in forums: 1 Week 2 Days 11 h 16 m 7 sec
Reputation Power: 969

Excellent. And you are very welcome.

Viewing: ASP Free Forums > System Administration > Windows Scripting > [newbie] Need a script to creat a .csv file

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Create the Optimal Architecture for your Critical Applications
Warburton's the largest independently owned bakery in the UK faced a number of difficult challenges in providing the most robust yet efficient IT infrastructure for their organization's success. IBM's services combined with their xSeries servers created the perfect platform for their SAP environment with sufficient flexibility, and did so in very time effective fashion.

Five Best Practices for Deploying a Successful Service-Oriented Architecture
This white paper describes the benefits you can expect with SOA, and how IBM can help take your business there.

Gartner Magic Quadrant for Application Delivery Controllers
Gartner summarizes its view on Application Delivery Controllers, evaluates strengths and weaknesses of solutions, and provides Magic Quadrant reporting for a quick comparison across all vendors. Learn from Gartner how you can benefit from an all-in-one device like Citrix NetScaler that delivers the highest levels of availability, performance and security.

Knowledge is Power
What you don't know can hurt you, and is likely costing you money and increasing your security risks during an era of scarce resources. This white paper proposes six key strategies that enterprise security managers can use to improve their network defense posture.

Rationalizing the Multi-Tool Environment
The rationalized multi-tool approach is flexible, scalable and cost effective. It provides the necessary input to the IT service management business processes. It preserves prior investments in monitoring tools, empowers technologists to select the best tools with which to do their jobs, and enhances effective response to incidents.

More Free IT White Papers!