Solved

export data in a csv format

Posted on 2008-06-25
7
489 Views
Last Modified: 2012-05-05
Hello Experts
I am working with VBScript to actually receive the comma separated record ids, process them to get details from the XML database and finally to epxort or save it into the csv format on the server at some location.
I have managed to process the ids and the data is now ready to be exported, however, the problem is itself with the way the data is.
Please can someone look into this advice me how to save it to csv file.
Many thanks for your help.
Regards
Sam
please see the attached for fValues, that is how I am having the final results, i now have to export this data.

Open in new window

screen.jpg
0
Comment
Question by:newbie27
[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
  • 2
7 Comments
 
LVL 8

Author Comment

by:newbie27
ID: 21867538
fValues = ": separated columns for each row with a VbCrLf(newline)"

hope you understand what I am trying to say you here?

ISBNs = "e2007092018174409,e2007092018105659,e2007091011105134,e2007091011100125,e2007091011051539,e2007091011084338,e2007091011123197,e2007091011113098,e2007091011073233,e2007091011024965,e2007090910300598"
   
			cn = 4
			DBM = "artism"
			SiteName ="artism"
			FieldList = "ref_no,pe_rev_name,co_name,action_flag,co_group,notes"
			
			If RemoteServer = "" Then RemoteServer = "http://213.253.134.6"
			Set DataHash = CreateObject("Scripting.Dictionary")
		
		' Chunk ISBNs into lots of say 50
			parts = split(ISBNs, ",")
			For i = 0 to Ubound(parts)
				if (i + 1) mod cn = 0 Then
					blob = blob & parts(i) & "|"
				Else
					blob = blob & parts(i) & ","
				end if
			Next
						
			If Right(blob, 1) = "," Then blob = Left(blob, Len(blob)-1)
			
			msg "blob=" & blob
		
		' Get the data and store it ready for writing out
			parts = split(blob, "|")
			For each p in parts
				xTxt = QuickSearchText("&SF1=keyword" & "&ST1=" & p & "&PL=" & cn, DBM, FieldList, NodeName, RemoteServer, IncTags)
				'msg "xtxt=" & xtxt
				xParts = Split(xTxt, "|")
				For each xp in xParts
					DataHash(XMLField(xp, "fv_ref_no", false)) = xp
				Next
			Next
		   
		'Now write out the divs in the correct order of course!
			parts = split(ISBNs, ",")
			For each p in parts
				
				mx = DataHash(p)
				'msg "mx=" & mx
				Fields = Split(FieldList, ",")
				 
				For each f in Fields
					fValues = fValues & XMLField(mx, "fv_"&f, False) & ":" 
					
				Next
				
				fValues = fValues & VbCrLf  
		         
			Next
		
			msg fValues
	
			
		End If	

Open in new window

0
 
LVL 17

Expert Comment

by:Jared Luker
ID: 21867640
Do you just want to change from colon (:) delimited to (,) delimited:
0
 
LVL 8

Author Comment

by:newbie27
ID: 21867676
hello jared luker,
thanks for your comment, well i thought of using comma as a separator but then Name field has already got comma in it so I thought I better use something else but comma.

As you see I have some data as a result, I wanted to save this into csv file

The attached function process all the files from the given folder and save it in a outfile

I wanted to use the same script for saving it on the server, can you please help.


thanks
sam
Function ExportFilesListed (FileList)
Dim parts, p, ff, cBlob, folder, files, f
 
	If FileList = "" Then
		' yuk!
		'Set FSO = CreateObject("Scripting.FileSystemObject")
		Set folder = fso.GetFolder(DataFolder)
		Set files = folder.Files
		For each f in files
			'msg(Right(f, Len(f) - InstrRev(f, "\")))	'"
			FileList = FileList & Right(f, Len(f) - InstrRev(f, "\")) & "|"	'"
		Next
		If Right(FileList, 1) = "|" Then FileList = Left(FileList, Len(FileList)-1)
		parts = split(FileList, "|")
	Else
		parts = split(FileList, "|")
	End If
		
	ff = ubound(parts)
	msg("[" & ff+1 & "]" & "file(s) found for processing..")
	if ff = 0 then
		msg(".. [" & parts(0) & "]")
	Else
		msg(" ..[" & parts(0) & "] ..[" & parts(ff) & "]")
	End if
		
	msg("")
	FieldList = split(KeyFields, ",")
	
	' Write Field Names in the First Row
	FieldTitle = Replace(Ucase(KeyFields), ",", Separator) & VbCrLf
	x = WriteFile(OutFile, FieldTitle)
	
	cBlob = ""
	AddCount = 0
	for each p in parts
		AddCount = AddCount + 1
		If AddCount mod ReportStep = 0 Then
			msg("> records processed: [" & Padz(AddCount, 8) & "]")
		End If
		cBlob = ReadFile(DataFolder & p) 
		Set FieldHash = CreateObject("Scripting.Dictionary")
		' Store Value of the fields in a Scripting Dictionary
		for each Field in FieldList
		  	FieldHash(Field) = CleanXML(Trim(XmlField(cBlob , Field , false)))
		Next   
		FieldBlob = ""
		for each Field in FieldList
		   'write out fieldhash to a blob
           FieldBlob = FieldBlob & FieldHash(Field) & Separator
		Next
		
		AppendtoFile OutFile, FieldBlob 
		'clear blob
		cBlob = ""
		
		Set FieldHash = Nothing	   	
		'Set FSO = Nothing	
	Next	
 
End Function

Open in new window

0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 17

Assisted Solution

by:Jared Luker
Jared Luker earned 100 total points
ID: 21867735
You should just be able to use the OpenTextFile method in order to create a text file on the server.  You could also use the Replace function to replace the : to a , and then use WriteLine to the text file.

http://www.devguru.com/Technologies/vbscript/quickref/regexp_replace.html
http://www.devguru.com/Technologies/vbscript/quickref/filesystemobject_opentextfile.html
http://www.devguru.com/Technologies/vbscript/quickref/textstream_writeline.html
0
 
LVL 8

Author Comment

by:newbie27
ID: 21867937
hello again,
thanks for your suggestion, can you please help me in adding columns in the csv file please?
OutFolder="E:\tbp\www\artism\admin\FTP\download"
	OutFileName="media"
	OutExtension="csv"
FieldTitle = Replace(Ucase(FieldList), ",", Separator) & VbCrLf
			x = WriteFile(OutFile, FieldTitle)
		    
 
		'Now write out the divs in the correct order of course!
			parts = split(ISBNs, ",")
			For each p in parts
				
				mx = DataHash(p)
				'msg "mx=" & mx
				Fields = Split(FieldList, ",")
				 
				For each f in Fields
					fValues = fValues & XMLField(mx, "fv_"&f, False) & ":" 
					
				Next
				
				fValues = fValues & VbCrLf  
		         
			Next
		
			AppendtoFile OutFile, fValues 
		    msg fValues`

Open in new window

0
 
LVL 82

Accepted Solution

by:
hielo earned 400 total points
ID: 21891836
If you already have the data in some variable (and it seems like you do), then you just need to write it to some file:
<%
dim fs,fname
set fs=Server.CreateObject("Scripting.FileSystemObject")
set fname=fs.CreateTextFile("c:\test.txt",true)
fname.Write( yourData )
fname.Close
set fname=nothing
set fs=nothing
%>

Open in new window

0
 
LVL 8

Author Closing Comment

by:newbie27
ID: 31470656
thanks hileo
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 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