Solved

vbscript to convert all files in a folder

Posted on 2013-02-05
7
683 Views
Last Modified: 2013-02-07
I have a script (thanks to terencino) that will take a given csv file and convert to a tab delimited file with specified formatting. The problem is I may not know the name of the file. Files will be loaded into the folder at random daily. A batch process will run this script every 30 minutes. To completely automate this I need 2 things:
1. Convert all csv files in the folder.
2. Then move the original to another location so it doesn't get converted again.
(I think I have #2 as you can see in my code where I have movefile)
Thank you

Option Explicit
Dim objFSO, objImport, objExport
Dim a, b, c, d, e, f, i
Dim strImport, strExport, strRename
Set objFSO = CreateObject("Scripting.FileSystemObject")
strImport = "C:\Users\jlepley\Documents\Main Folder\PicsFiles\Script\PICS166.csv"
strExport = "C:\Users\jlepley\Documents\Main Folder\PicsFiles\Script\PICS00166 2013_02_04.txt"

Set objImport = objFSO.GetFile(strImport)
Set objImport = objFSO.OpenTextFile(objImport, 1)
Set objExport = objFSO.CreateTextFile(strExport)
i = 0
Do Until objImport.AtEndOfStream
	i = i + 1
		a = Split(objImport.ReadLine,",") 'converts components of next line of CSV file to array
	If i= 1 Then ' builds header line with field names separated by Tab
		f = Join(Split("StoreCode|DataDate|UPCCode|SKUCode|UseUPC|TranQuant|TransPrice|DAXTranType|POID|PackSize|UseStoreEDICode|SourceStamp|DaleteByScope|AdditinalData1|AdditinalData19|AdditinalData3|AdditinalData4|AdditinalData5|AdditinalData6|AdditinalData7|AdditinalData8|AdditinalData9|AdditinalData10|DAX__BuyerID|DAX__ProductID|DAX__Date|DAX__DataType|DAX__ProductGroupID|DAX__TranQuantity|DAX__TranPrice|DAX__POID","|"),vbTab)
	Else 'process next line as data
		b = FormatString00(a(12),3,"0") 'StoreCode formatted to 000
		c = FormatDate00 'DataDate formatted as yyyy-mm-dd string
		d = FormatString00(a(0),13,"0") 'UPCCode formatted to 0000000000000
		e = FormatString00(a(7),6,"0") 'SKUCode formatted to 000000
		f = Join(Array(b,c,d,e,"1",a(10),"","RealInventory","","EA","","-1"),vbTab) 'rejoin string in required format of tab-separated values
	End if
	objExport.WriteLine f

Loop
objImport.Close
objExport.Close
objFSO.MoveFile "C:\Users\jlepley\Documents\Main Folder\PicsFiles\Script\PICS166.csv", "C:\Users\jlepley\Documents\Main Folder\PicsFiles\Script\loc\PICS166.csv"


Function FormatString00(f_string,f_length,f_character)
	'This adds leading characters to a string to the required length
	'usage: Format00("123",6,"0") = "000123" or Format00("ABC",7,"X") = "XXXXABC"
	FormatString00 = Right(String(f_length,f_character) & f_string, f_length)
End Function

Function FormatDate00
	' Formats the current system date as yyyy-mm-dd
	FormatDate00 = DatePart("yyyy",Date) & "-" & Right("0" & DatePart("m",Date),2) & "-" & Right("0" & DatePart("d",Date),2)
End Function

Open in new window

MyScript2.vbs
0
Comment
Question by:jacobJL
  • 4
  • 3
7 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 38857247
Hi there, I think this should work.

Regards,

Rob.

Option Explicit
Dim objFSO, objImport, objExport
Dim a, b, c, d, e, f, i
Dim strImport, strExport, strRename, strDate, objFile, strExportFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
strDate = Year(Date) & "_" & Right("0" & Month(Date), 2) & "_" & Right("0" & Day(Date), 2)
' Specify folders only
strImport = "C:\Users\jlepley\Documents\Main Folder\PicsFiles\Script\"
strExport = "C:\Users\jlepley\Documents\Main Folder\PicsFiles\Script\"

If Right(strExport, 1) = "\" Then strExport = Left(strExport, Len(strExport) - 1)
For Each objFile In objFSO.GetFolder(strImport).Files
	If Right(LCase(objFile.Name), 4) = ".csv" Then
		Set objImport = objFSO.OpenTextFile(objFile.Path, 1, False)
		strExportFile = Left(objFile.Name, Len(objFile.Name) - 4) & " " & strDate & ".txt"
		Set objExport = objFSO.CreateTextFile(strExportFile, True)
		i = 0
		Do Until objImport.AtEndOfStream
			i = i + 1
				a = Split(objImport.ReadLine,",") 'converts components of next line of CSV file to array
			If i= 1 Then ' builds header line with field names separated by Tab
				f = Join(Split("StoreCode|DataDate|UPCCode|SKUCode|UseUPC|TranQuant|TransPrice|DAXTranType|POID|PackSize|UseStoreEDICode|SourceStamp|DaleteByScope|AdditinalData1|AdditinalData19|AdditinalData3|AdditinalData4|AdditinalData5|AdditinalData6|AdditinalData7|AdditinalData8|AdditinalData9|AdditinalData10|DAX__BuyerID|DAX__ProductID|DAX__Date|DAX__DataType|DAX__ProductGroupID|DAX__TranQuantity|DAX__TranPrice|DAX__POID","|"),vbTab)
			Else 'process next line as data
				b = FormatString00(a(12),3,"0") 'StoreCode formatted to 000
				c = FormatDate00 'DataDate formatted as yyyy-mm-dd string
				d = FormatString00(a(0),13,"0") 'UPCCode formatted to 0000000000000
				e = FormatString00(a(7),6,"0") 'SKUCode formatted to 000000
				f = Join(Array(b,c,d,e,"1",a(10),"","RealInventory","","EA","","-1"),vbTab) 'rejoin string in required format of tab-separated values
			End if
			objExport.WriteLine f
		
		Loop
		objImport.Close
		objExport.Close
		objFSO.MoveFile objFile.Path, strExport & "\loc\" & objFile.Name
	End If
Next

Function FormatString00(f_string,f_length,f_character)
	'This adds leading characters to a string to the required length
	'usage: Format00("123",6,"0") = "000123" or Format00("ABC",7,"X") = "XXXXABC"
	FormatString00 = Right(String(f_length,f_character) & f_string, f_length)
End Function

Function FormatDate00
	' Formats the current system date as yyyy-mm-dd
	FormatDate00 = DatePart("yyyy",Date) & "-" & Right("0" & DatePart("m",Date),2) & "-" & Right("0" & DatePart("d",Date),2)
End Function

Open in new window

0
 

Author Comment

by:jacobJL
ID: 38857683
Thank you for the fast response. However when I run your code it does move the original csv files to the loc folder but it doesn't convert the files into txt files as it did before. If you don't mind could you explain your changes as well. Thank you.
0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 500 total points
ID: 38857793
Try changing this line:
            strExportFile = Left(objFile.Name, Len(objFile.Name) - 4) & " " & strDate & ".txt"

to this
            strExportFile = strExport & "\" & Left(objFile.Name, Len(objFile.Name) - 4) & " " & strDate & ".txt"

As for the changes, I added a date format to get the date for today
strDate = Year(Date) & "_" & Right("0" & Month(Date), 2) & "_" & Right("0" & Day(Date), 2)

I changed strImport and strExport to folder paths only so that the folders can be enumerated for files.

I added a loop for all files in strImport, and a check to only work against .csv files:
For Each objFile In objFSO.GetFolder(strImport).Files
      If Right(LCase(objFile.Name), 4) = ".csv" Then

It then generates the export file name based on the current .csv file from the loop
            strExportFile = strExport & "\" & Left(objFile.Name, Len(objFile.Name) - 4) & " " & strDate & ".txt"

and that's it, in a nutshell.

If you need any part of that explained, let me know.

Regards,

Rob.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:jacobJL
ID: 38859849
Rob,
Greatly appreciated. Works great! I have one more question. When moving the original files to the new location:
objFSO.MoveFile objFile.Path, strExport & "\loc\" & objFile.Name
      
How do i make it overwrite if the file already exist.
I tried
objFSO.MoveFile objFile.Path, strExport & "\loc\" & objFile.Name, OverwriteExisting

But I don't think this is the right syntax.
0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 500 total points
ID: 38861800
No, this doesn't work because the MoveFile method doesn't support overwrite.  You can use either of the following two options

1)
'Delete the destination if it exists before moving
If objFSO.FileExists(strExport & "\loc\" & objFile.Name) = True Then objFSO.DeleteFile(strExport & "\loc\" & objFile.Name, True)
objFSO.MoveFile objFile.Path, strExport & "\loc\" & objFile.Name

Open in new window


2)
' Use CopyFile with the overwrite parameter, then DeleteFile on the original
objFSO.CopyFile objFile.Path, strExport & "\loc\" & objFile.Name, True
objFSO.DeleteFile objFile.Path, True

Open in new window


Regards,

Rob.
0
 

Author Closing Comment

by:jacobJL
ID: 38865340
Rob thank you so much, works fantastic! I got an error when i tried putting when I tried to delete the destination if exist (can't use parentheses when calling a sub) but I just used the other one which worked great. So thank you so much for the help. Not sure what I'd do without the experts.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 38865868
Oh sorry.  I had too many parenthesis in the first one, it should have been
'Delete the destination if it exists before moving
If objFSO.FileExists(strExport & "\loc\" & objFile.Name) = True Then objFSO.DeleteFile strExport & "\loc\" & objFile.Name, True
objFSO.MoveFile objFile.Path, strExport & "\loc\" & objFile.Name 

Open in new window


Either way works though.

Thanks for the grade.

Rob.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question