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

x
?
Solved

vbscript extract csv into tab delimited file

Posted on 2013-01-14
8
Medium Priority
?
1,335 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
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!

 
LVL 16

Accepted Solution

by:
terencino earned 2000 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
 

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

886 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