Solved

export data in a csv format

Posted on 2008-06-25
7
486 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
Comment Utility
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
Comment Utility
Do you just want to change from colon (:) delimited to (,) delimited:
0
 
LVL 8

Author Comment

by:newbie27
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Assisted Solution

by:Jared Luker
Jared Luker earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks hileo
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now