Solved

vbscript to convert all files in a folder

Posted on 2013-02-05
7
690 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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