[Babase] Extra quotes in data -- Was: Re: min_max file revised

kfenn kfenn at princeton.edu
Wed Mar 18 12:27:33 EDT 2009


Lacey (and anyone who exports text from Excel for Babase upload):

See the discussion below.  The tab delimited .txt function automatically 
outputs annoying double quotes around the text that contains a comma OR 
double quotes.  Two possible solutions are given at the bottom of this 
email.

Karl O. Pinc wrote:
>
> On 03/18/2009 07:32:08 AM, kfenn wrote:
>> Karl O. Pinc wrote:
>>>
>>> Those extra quotes that excel sometimes puts around
>>> the data are making the comments look ugly. 
>> I know for sure there are quotes in the demog notes.  At times, I 
>> think I've bothered to delete them manually, but I'm pretty sure I 
>> gave up when I had to put in 1200 demog notes from incomplete census 
>> data one update.  I don't know where they come from or how to get rid 
>> of them.  Any suggestions?
>
>  If nothing else
> I'm sure that purchasing Microsoft Visual Basic and
> programming some extension or something would do it.
> But that sort of approach sounds both excessive and
> fragile.
My investigations have turned up two things:
1)  using sp3 might solve the problem (someone mentioned this in an 
online forum.  I'm still running sp2 so I can't confirm this)
2)  Here is a VBA macro that someone wrote.  I tested it on a small file 
with commas and quotes and it seemed to do the job.


Hi yvan,

The
coding <javascript:void(0)> in that other question doesn't quite look 
right, so I made a new macro for you.  To run this, press alt-f11 from 
excel to open your VBA (visual basic <javascript:void(0)> for 
applications) editor.  Go to Insert <javascript:void(0)>, then Module, 
and in the blank <javascript:void(0)> code module, paste the following:


Sub ExportTabDelimited()
 Dim Delim As String, eWS As Worksheet, ExportName As String
 Dim vFF As Long, TempStr() As String, i As Long, j As Long
 Delim = Chr(9)
 Set eWS = ActiveSheet
 ExportName = 
Application.GetSaveAsFilename(InitialFilename:=Left(ActiveWorkbook.Name, _
  Len(ActiveWorkbook.Name) - 4) & ".TXT", _
  FileFilter:="Tab-delimited text files (*.txt),*.txt,All Files (*.*),*.*")
 If UCase(ExportName) = "FALSE" Then Exit Sub
 Application.ScreenUpdating = False
 vFF = FreeFile()
 On Error Resume Next
 Open ExportName For Output As #vFF
 If Err <> 0 Then MsgBox "Cannot save to filename " & ExportName & 
vbCrLf & "The file " & _
  "may already exist and currently be in use. Consider changing the 
filename.": Exit Sub
 On Error GoTo 0
 ReDim TempStr(1 To eWS.UsedRange.Column + eWS.UsedRange.Columns.Count - 1)
 For i = 1 To eWS.UsedRange.Row + eWS.UsedRange.Rows.Count - 1
  For j = 1 To eWS.UsedRange.Column + eWS.UsedRange.Columns.Count - 1
   TempStr(j) = eWS.Cells(i, j).Text
  Next j
  Print #vFF, Join(TempStr, Delim)
 Next i
 Application.ScreenUpdating = True
 Close #vFF
 MsgBox "Saved to " & ExportName
End Sub


Then close the VBA editor.  From excel, go to the sheet you want to 
export, press alt-F8 to open the Macros window, choose 
"ExportTabDelimited", and click run.  It will prompt you for a filename 
to save to, then create the file for you with no quotation marks other 
than those you have in the file.
Let me know if you have any questions!
Matt



>
>
>
> _______________________________________________
> Babase mailing list
> Babase at www.eco.princeton.edu
> http://www.eco.princeton.edu/mailman/listinfo/babase

-- 
Tabby Fenn
Research Assistant

Dept of Ecology and Evolutionary Biology
401 Guyot Hall
Princeton University
Princeton, NJ  08544

609 258-6898 (Ph)
609 258-2712 (Fx)



More information about the Babase mailing list