Solved

vbscript extract csv into tab delimited file

Posted on 2013-01-14
8
1,211 Views
Last Modified: 2013-02-05
Hi experts. I am working on a way to take a csv file like the one below and convert the data into a tab delimited file. Here is the kicker; I need to be able to change the format of the data and move the data from one column to another. See example below.

CSV format
Heading1,Heading2,Heading3,Heading4
Value1-1,Value1-2,Value1-3,1/14/2013
Value2-1,Value2-2,Value2-3,1/14/2013
Value3-1,Value3-2,Value3-3,1/14/2013

Convert to tab delimited and take the information from heading 4 and put in heading 1 and also be able to change the format of each column. So if for instance changing the date format.
Heading1              Heading2      Heading3      Heading4
2013/01/14      Value1-3      Value1-2      Value1-1
2013/01/14      Value2-3      Value2-2      Value2-1
2013/01/14      Value3-3      Value3-2      Value3-1

I am new to vbscript and this is all I have so far and I got this from browsing the web:
Dim oFSO, oDB, oRS, sOldFile, sNewFile, sColumn, sSource

sOldFile = "C:\Users\jlepley\Documents\Main Folder\Script\CSVFILETOIMPORT.csv"
sNewFile = "C:\Users\jlepley\Documents\Main Folder\Script\TabDelimitedOutput.txt\"
sColumn="Heading2"

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oDB = CreateObject("ADODB.Connection")

sSource = oFSO.GetParentFolderName(sOldFile)
sOldFile = oFSO.GetFileName(sOldFile)

oDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sSource _
& ";Extended Properties=""text;HDR=YES;FMT=Delimited"""

Set oRS = oDB.Execute("SELECT * FROM [" & sOldFile & "]")

With oFSO.OpenTextFile(sNewFile, 2, True)
.WriteLine sColumn
Do Until oRS.EOF
.WriteLine oRS.Fields(sColumn)
oRS.MoveNext
Loop
End With
0
Comment
Question by:jacobJL
  • 4
  • 4
8 Comments
 
LVL 16

Expert Comment

by:terencino
ID: 38777017
Hi again Jacob, and thanks for your invitation to look at another of your intriguing challenges! I have used here out old friend FileSystemObject to process the file, in conjunction with VBScript's very useful Join & Split functions to achieve the required output. Please test it and let me know how it goes for you.
...Terry

Option Explicit
Dim objFSO, objImport, objExport
Dim a, b, c, d, i
Dim strImportLine, strImport, strExport
Set objFSO = CreateObject("Scripting.FileSystemObject")
strImport = "C:\Users\jlepley\Documents\Main Folder\Script\CSVFILETOIMPORT.csv"
strExport = "C:\Users\jlepley\Documents\Main Folder\Script\TabDelimitedOutput.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
	strImportLine = objImport.ReadLine
		a = Split(strImportLine,",")
	If i= 1 Then ' process header line
		d = Join(Array(a(3),a(2),a(1),a(0)),vbTab)
	Else
		b = Split(a(3),"/") 'split date into components
		c = Join(Array(b(2),Format00(b(0)),b(1)),"/") 'rejoin date components in required format
		d = Join(Array(c,a(2),a(1),a(0)),vbTab) 'rejoin string in required or as tab-separated values
	End if
	objExport.WriteLine d
Loop
objImport.Close
objExport.Close

Function Format00(f_string)
If Len(f_string)= 1 Then
	Format00 = "0" & f_string
Else
	Format00 = f_string
End If
End Function

Open in new window

0
 

Author Comment

by:jacobJL
ID: 38778398
Terry thank you once again for helping me out on this. Would you be able to put some comments in your code? I am having a hard time figuring out what is going on. I was hoping to get a basic understanding of 3 things:
1. Obviously how to convert from csv to tab delimited
2. How to take the data from say header 4 of the csv and put in header 1 of the txt file. (knowing this I should be able to do this for all columns)
3. How to change the format (whether by date or something else) in each column.

If this is too much to explain I understand.

Thanks you


EDIT:
I am starting to understand a little bit. "a" is the array of data in each row. so the first line would read a(0) as Header1. a(1) as Header2 and so on. Correct? "b" and "c" is how to reformat the date in specified value. "d" is as you stated in the comment that I overlooked, rejoining the string in tab format.

Another Edit:
No need to respond just yet. I am figuring something out (I think). Will get back shortly

Thanks
0
 

Author Comment

by:jacobJL
ID: 38780098
OK. I made some changes to your code to meet the needs of the actual file imported (also attached).Note that the pics file is much larger than that but I trimmed it down.  

So I got the data importing into the right columns however, I need to change the format of a few columns:
Column 1 = StoreCode needs to be "000". in other words if the storecode is 17 it should be 017
Column 2 = DataDate needs to be yyyy-mm-dd. This date is not imported. As you can see I have it hard coded as Now() which is not the proper format.  It would be nice if you could take the date the file was created and insert here in the proper format. If that's too much then just Now() in the proper format should work.
Column 3 = UPCCode needs to be "0000000000000" in other words 1234567 should be 0000001234567 (13 digits)
and lastly Column 4 = SKUCode needs to be "000000" 6 digits

Let me know what you think. Thank you


Option Explicit
Dim objFSO, objImport, objExport
Dim a, b, c, d, i

Dim strImportLine, strImport, strExport
Set objFSO = CreateObject("Scripting.FileSystemObject")
strImport = "C:\Users\jlepley\Documents\Main Folder\Script\PICSfile.csv"
strExport = "C:\Users\jlepley\Documents\Main Folder\Script\TabDelimitedOutput.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
      strImportLine = objImport.ReadLine
            a = Split(strImportLine,",")  
      If i= 1 Then ' process header line
            'These are the headers.
             d = Join(Array("StoreCode", "DataDate", "UPCCode","SKUCode","UseUPC","TranQuant","TransPrice","DAXTranType","POID","PackSize","UseStoreEDICode","SourceStamp"),vbTab)
      Else
            'I commented your code for now as the file imported doesn't actually have a column date.
            'b = Split(a(3),"/") 'split date into components
            'c = Join(Array(b(2),Format00(b(0)),b(1)),"/") 'rejoin date components in required format
            'd = Join(Array(c,a(2),a(1),a(0)),vbTab) 'rejoin string in required or as tab-separated values
            'here is the data that needs to go in each column. Some columns will be blank or static data.
            d = Join(Array(a(12), NOW(), a(0), a(7), 1, a(10),"","RealInventory","","EA","",-1),vbTab) 'rejoin string in required or as tab-separated values
      End if
      objExport.WriteLine d
Loop
objImport.Close
objExport.Close

Function Format00(f_string)
If Len(f_string)= 1 Then
      Format00 = "0" & f_string
Else
      Format00 = f_string
End If
End Function
MyScript.vbs
PICSfile.csv
0
 
LVL 16

Accepted Solution

by:
terencino earned 500 total points
ID: 38780848
Hi Jacob, I've added some more comments into the code to help you decipher it although quite frankly you worked it out yourself very quickly! I also shortened it a bit, removed surplus variables, and revised the custom formatting functions.

By the way what do you use to edit your code? I use Notepad++ which is free, and VBSEdit, which is not. Both are good, and likely there are others out there. They really help with formatting the code to make it more legible, as well as debugging.

Let me know how you go
...Terry

Option Explicit
Dim objFSO, objImport, objExport
Dim a, b, c, d, e, f, i
Dim strImport, strExport
Set objFSO = CreateObject("Scripting.FileSystemObject")
strImport = "C:\Users\jlepley\Documents\Main Folder\Script\PICSfile.csv"
strExport = "C:\Users\jlepley\Documents\Main Folder\Script\TabDelimitedOutput.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","|"),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

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
	' Lifted from http://learningpcs.blogspot.com.au/2011/03/vbscript-format-date-as-yyyy-mm-dd.html 
	FormatDate00 = DatePart("yyyy",Date) & "-" & Right("0" & DatePart("m",Date),2) & "-" & Right("0" & DatePart("d",Date),2)
End Function

Open in new window

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Closing Comment

by:jacobJL
ID: 38781128
Terry you rock! Thank you so much. I learned a lot from this and your previous answer. I am using textpad. I tried the evaluation of vbsedit but that was a pain (the evaluation version that is). Anyways I do have some more things I need to do to this code to make it work EXACTLY how I want that I didn't include but i will try on my own and if i need help will open another question and surely link you to it.

Once again thanks.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38781166
Great you're welcome Jacob. I learned a lot myself, and I'm impressed with the file & text manipulation capability of VBScript. I'll look forward to your next challenge
0
 

Author Comment

by:jacobJL
ID: 38856956
Got another one for ya Terry if you are interested. Same script but now I need to finalize it by completely automating it. Would love for your help on it. Thank you

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28021083.html
0
 
LVL 16

Expert Comment

by:terencino
ID: 38857615
Thanks Jacob for the invitation, looks like Rob has sorted that one for you. You can delete the FormatDate00 function too, since you have what you need with the strDate definition. I'm glad your project has come to a successful conclusion!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

746 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

12 Experts available now in Live!

Get 1:1 Help Now