?
Solved

export data in a csv format

Posted on 2008-06-25
7
Medium Priority
?
494 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 17

Assisted Solution

by:Jared Luker
Jared Luker earned 400 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 1600 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…

589 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