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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

export data in a csv format

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
newbie27
Asked:
newbie27
  • 4
  • 2
2 Solutions
 
newbie27Author Commented:
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
 
Jared LukerCommented:
Do you just want to change from colon (:) delimited to (,) delimited:
0
 
newbie27Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jared LukerCommented:
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
 
newbie27Author Commented:
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
 
hieloCommented:
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
 
newbie27Author Commented:
thanks hileo
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now