Text Qualifiers and CSV files

[Tama]

Hi there I'm trying to set up a way of allowing staff at my library to manage e-resources easily.

For part of this I am using a Excel 2000 file that staff save as CSV. My biggest problem with this is that text feilds are not being saved with text qualifiers ("") so the Javascript I am running on the CSV can not read it properly.

When I've tried to physically add quotations it then saves the file with multiple quotations on either side - ""field entry"" - which is even worse.

So, does anyone know how to make Excel save to CSV with text qualifiers?

Any ideas, comments, etc. much appreciated.

Cheers
Tama

 

[Legacy 98055]

I don't know of any settings you can use on save as???
Irregardless, the problem is easily solved with a bit of code to create the file and save it exactly as needed.
Please post an example of the data on your worksheet(several rows) and then the expected result of saving as text.
Tom

 

[Tama]

In an ideal world - I would like the following Table (example)<table border="1"><tr><td>PURL</td><td>Resource</td><td>URL</td></tr><tr><td>1</td><td>INFOS</td><td>javaserver/infos.html</td></tr><tr><td>2</td><td>Turbine</td><td>jcbr/turbine/index.html</td></tr><tr><td>3</td><td>SCI</td><td>cdrom/sci/index.html</td></tr></table>

Transformed into:<tt>"javaserver/infos.html", "jcbr/turbine/index.html", "cdrom/sci/index.html"</tt>

Hopefully my HTML comes out OK
This message was edited by Tama on 2002-11-06 14:34

 

[garyae]

This might be a little bit off topic however here goes anyhow.

Since I do not know JAVA at all and I am just learning VB I would do one of these things.

If would be very simple to write a perl program that reads in the .csv and outputs another csv with the quotes. However, if you do not know perl and or you do not have perl on your machine that idea is probabaly moot. BTW if you have easy access to a unix box you have access to perl. If you do not get any ideas that you really like I could cook up that perl program pretty easily (like 30 mins or less).

Use a text editor of some sort (that supports macros) and write a simple macro to add the quotes to the .csv I would use emacs or some editor that understands emacs key-strokes. Again you probably do not have access to emacs.

You could also write the macro in word or even excel.

I realize that none of these are ideal if you are using Win-Tel and (sp?) if you have add in the quotes very often.

HTH,

 

[Legacy 98055]

My apologies for the delay. I read this post at work and lost track of it. This is a simple but effective enough procedure. Select your range of strings and run the routine. Will save like the format in your example.
<pre>
Sub OneUglyExport()
Dim FileToSave, c As Range, OneBigOleString As String
FileToSave = Application.GetSaveAsFilename
Open FileToSave For Output As #1
For Each c In Selection
If Len(c.Text) <> 0 Then _
OneBigOleString = OneBigOleString & ", " & Chr(34) & Trim(c.Text) & Chr(34)
Next
Print #1, Mid(OneBigOleString, 3, Len(OneBigOleString))
Close #1
End Sub

</pre>
Tom

 

[Tama]

Thanks Tom!

I'm on annual leave at the moment but will test this out when I get back in the office next week.

Thanks again, much appreciated

Tama

 

[garyae]

I was just reformatting one of my spreadsheets by saving the sheet as a csv and editing it in my prefered text editor for the mac.

I realized that if a cell value contains a comma and you save it as a CSV excel puts in the quotes for you! So I think you could just add a space followed by a comma in that row and make a minor mod your java to just ignore the space comma at the end. This may be the simplest way to go.

Besst of luck,

 

[Tama]

Cheers Tom - just tried it out at work and it works a charm.

Thanks again.
Tama

 

[spearheadjams]

what happens if a row entry is numeric Tom, is the integrity of the matrix preserved?