Solved

export data in a csv format

Posted on 2008-06-25
7
488 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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
XML extra information 8 30
VBScript on Html 15 47
Button function on table is in trouble 3 22
adding "ungroup sheets" to existing vbs code 5 31
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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