[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