Solved

vbscript to convert all files in a folder

Posted on 2013-02-05
7
666 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:jacobJL
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now