Text Qualifiers and CSV files

Tama

New Member
Joined
Nov 4, 2002
Messages
4
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
L

Legacy 98055

Guest
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

New Member
Joined
Nov 4, 2002
Messages
4
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 :p
This message was edited by Tama on 2002-11-06 14:34
 

garyae

New Member
Joined
Oct 29, 2002
Messages
15
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,
 
L

Legacy 98055

Guest

ADVERTISEMENT

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

New Member
Joined
Nov 4, 2002
Messages
4
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

New Member
Joined
Oct 29, 2002
Messages
15

ADVERTISEMENT

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

New Member
Joined
Nov 4, 2002
Messages
4
Cheers Tom - just tried it out at work and it works a charm.

Thanks again.
Tama
 

Forum statistics

Threads
1,144,290
Messages
5,723,522
Members
422,502
Latest member
barakgahtan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top