Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

vbscript to convert all files in a folder

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
jacobJL
Asked:
jacobJL
  • 4
  • 3
3 Solutions
 
RobSampsonCommented:
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
 
jacobJLAuthor Commented:
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
 
RobSampsonCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jacobJLAuthor Commented:
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
 
RobSampsonCommented:
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
 
jacobJLAuthor Commented:
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
 
RobSampsonCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now