Re: Tool for exporting data in CVS or Excel from Cache
Rosti
One of the simplest ways I find to provide the user with a CSV file for
their manipulation and eventual import is to create the file and display
a link to it on a web page for their viewing and downloading.
I create a 'export' CSP page that simply displays a link (via a standard
<a href="somepath/file.txt"> file.txt </a> hyperlink).
During the construction of this page, I insert code similar to the
following Product File export ..
<script language="CACHE" runat="SERVER">
set lCr = $char(13)_$char(10)
set lTab = $char(09)
// creating the column headings
set lOutText =
"ItemID"_lTab_"Description"_lTab_"Style"_lTab_"Category"_lTab_"Colour"_lTab_"DisplayOrder"_lTab_"Price"_lTab_"Shipping"_lTab_"LongDescription"_lTab_"StartDate"_lTab_"EndDate"_lTab_"NewArrival"_lTab_"ActiveYN"_lCr
// Prepare to Write out the File
set stream=##class(%FileCharacterStream).%New()
set stream.Filename="C:/CacheSys/csp/pumper/Products.txt"
do stream.Rewind()
do stream.Write(lOutText)
</script>
<script language="SQL" name="query" P1=''>
select
ID,ItemID,Description,Style,Category,Sizes,SizesQTY,Colour,Price,Shipping,SpecialStockNote,StartDate,EndDate,ActiveYN,DisplayOrder
from SQLUser.PUMPER.Product order by ID
</script>
<csp:while counter="row" condition="query.Next()">
<script language="CACHE" runat="SERVER">
set Product = ##class(PUMPER.Product).%OpenId(query.GetData(1))
if Product '="" {
set lItemID = Product.ItemID
set lDescription = Product.Description
set lStyle = Product.Style
set lCategory = Product.Category
set lSizes = Product.Sizes
set lSizesQTY = Product.SizesQTY
set lColour = Product.Colour
set lPrice = Product.Price
set lShipping = Product.Shipping
set lSpecialStockNote = Product.SpecialStockNote
set lStartDate = Product.StartDate
set lEndDate = Product.EndDate
set lNewArrival = Product.NewArrival
set lActiveYN = Product.ActiveYN
set lDisplayOrder = Product.DisplayOrder
set lOutText =
lItemID_lTab_lDescription_lTab_lStyle_lTab_lCategory_lTab_lColour_lTab_lDisplayOrder_lTab_lPrice_lTab_lShipping_lTab_lSpecialStockNote_lTab_lStartDate_lTab_lEndDate_lTab_lNewArrival_lTab_lActiveYN_lTab
set lOutText = lOutText_lCr
// Write out the File's line
do stream.Write(lOutText)
}
</script>
</csp:while>
<script language="CACHE" runat="SERVER">
// Finish Writing out the File
do stream.SaveStream()
</script>
At the end of this process, I have the file created on the server, I
know where its located, so all I have to do is create the link, such as:
<tr>
<td align="left">
The exported file is contained in the following: <br />
<a
href="http://www.someweb.com/somepath/Products.txt">Products.txt</a><br><br>
(right-click on the file above and select to SAVE the
file to your local drive, from where you may then open it in MS Excel or
similar..)</td>
</tr>
and as the instructions suggest, you can right-click on it to save to a
local drive or you can left click on it to view the contents in the
browser. The insertion of TABs and CRLFs gives it the correct format for
MS Excel.
As its query driven, as long as you define the query correctly, the data
can be any valid Cache data.
Hope this helps.
Regards,
Rosti.
Tiger wrote:
> HI,
>
> Is there any tools that can exporting Cache data to CVS or Excel?
>
> Thanks
>
> William
>
>
John Murray
or in plain MUMPS app which writes globals? If it's Objects then you've
got a data dictionary to tell you what's where. If it's plain MUMPS
there may not be a dictionary. That doesn't mean you can't generate CSV
output, just that a generic tool might not make a great job of the task
John
Tiger
Tiger
I'm totally new to this kind of "database", Its just my client wants to
export all its CACHE data into CSV or XLS, ask me for help. I found Cache
SQL Manager, and when I try to connect to the one called "USER" (there is
only one connection as far as I can see when I lanuch SQL manager), there is
no tables at all.
Thanks
William
"John Murray" <jo...@georgejames.com> wrote in message
news:4A1233377E85D311821...@yak.gj.georgejames.com...
Sergei Shutov (logist)
Cache data very seldom resides in USER namespace. Try to connect to another
namespace. You may see a list of namespaces (with tables) in Cache Explorer.
As far as I remember, SQL manager had an option "export to CSV".
=Sergei Shutov (logist)
Dimas ltd., Khabarovsk
Tiger пишет:
Tiger
Thanks very much for the input, BTW, My client is using a software called
"MedicsPremier", Yes. I'm now see a list of namespaces in Cache Explorer.
They are %CACHELIB, %SYS, SAMPLES and USER. but only the namespaces SAMPLES
has the tables that I can saw from SQL Manager (and Yes, I can export them
to CSV file by using "Export table...." function), But I don't see the
namespaces used by the actual "MedicsPremier" software, Any ideas?
Thanks again!
William
"Sergei Shutov (logist)" <log...@dimas.ru> wrote in message
news:guichg$c62$1...@news.intersystems.com...
>
> Cache data very seldom resides in USER namespace. Try to connect to
> another namespace. You may see a list of namespaces (with tables) in Cache
> Explorer.
>
> As far as I remember, SQL manager had an option "export to CSV".
>
> =Sergei Shutov (logist)
> Dimas ltd., Khabarovsk
>
Tiger
Futher investigate by using Cache Explorer, I find that under "USER"
namespace, there is a "Globals", there are pretty items (like ^XXXX) under
it, open one of them, I found that the data might be there, but Don't know
how to export them now, Any ideas how to export these "Globals"?
Thanks
William
"Sergei Shutov (logist)" <log...@dimas.ru> wrote in message
news:guichg$c62$1...@news.intersystems.com...
>
> Cache data very seldom resides in USER namespace. Try to connect to
> another namespace. You may see a list of namespaces (with tables) in Cache
> Explorer.
>
> As far as I remember, SQL manager had an option "export to CSV".
>
> =Sergei Shutov (logist)
> Dimas ltd., Khabarovsk
>
Sergei Shutov (logist)
There is no standard way to export global to CSV, because global is a tree and
CSV is a table.
There is an Export option in Cache Explorer. You may export data in any format
except Cache block, which is default. There will be a text file with data, which
you may process.
=Sergei Shutov
Dimas ltd., Khabarovsk