There’s a few threads here close to what I’m looking for but I’d like to learn a little more about the options. I’m exporting relatively small amounts of data to a delimited text file that is then opened in Excel. I chose to build a tab delimited format and that does not work on Windows. I build this field by field and line by line. Loop through the fields and put a tab between them with a carriage return at the end of each record. Then write the file. Works perfect on Mac. Not at all on Windows.
I don’t know is switching to CSV would be a solution or would I have to do something with ReplaceLineEndings and use Target# to switch between formats for each platform? Thanks for any suggestions.
Just from what you’ve said so far it’s hard to tell what isn’t working for you but here are a couple of suggestions. Since it works on a Mac I assume the file is being created properly.
First, make sure you are writing the file to a place you actually have permissions to write to.
Second, since it is a text file you are trying to write, have you created it as a TextOutputFile?
Third, don’t append the carriage return to the end and use WriteLine() instead of Write(). WriteLine will automatically add the EndOfLine for you for the platform you are compiling on.
Of course, there are inelegant solutions. There are better ways to handle it if you need cross-platform solutions.
I don’t have Excel for PC on my Win7 virtual machine. When I looked at the exported file in Notepad it was all one line. Not so in Wordpad though. And I did find a copy of Excel and it did import fine. So…no problem. Thanks for checking in on this.
That’s what we all probably suspected: an EndOfLine problem.
But without hearing how the problem manifests itself it is all just guess work.
Excel is “smart” enough [though too smart for its own good in many other cases] to recognise Windows and Mac line endings (which are different -> see the documentation).
As the other persons that gaves you an answer (above), I have a bunch of questions:
I build this field by field and line by line. Loop through the fields and put a tab between them with a carriage return at the end of each record. Then write the file.
How do you wrote that “carriage return” ?
Chr(13) ? Bad idea pal, use instead EndOfLine.
field by field ? Yes, but what did you put in these fields ?
Oh sorry: from where these fields comes ? A ListBox ?
Also: do you know that Excell (years ago, but I suspect it si still the case) does not like to get some characters in cells ?
So, in your next try, store directly an already build list of sentences whit a tab and only use letters (a-z, A-Z) and nothing else.
Works perfect on Mac. Not at all on Windows.
Norman would say that the code behind Excell is very different :-
Your sentence is like “it worked once, I do not changed my code and now it does not works anymore”…
At last, I suppose you are using Yosemite / Windows 8.1 in their respective native hardware. And, do excessive tests on Windows because as you stated in your text, it works fine on one OS and can fail on another for whatever reason (one of them is to forgot to make code in a #If Target statement (this happens to me as far as … yesterday (to discover that loss).
I am doing the same and have no issues so far.
If you are storing your “CSV” into a string before you write it into a file, maybe try the following (s = the string containing the “CSV” text):
I cannot confirm any issue with producing a tab delimited file, either on Windows or OSX.
For instance, I’m writing out the contents of a listbox to a tab-delimited textfile, using TextOutputStream.Create and TextOutputStream.WriteLine - it works on Windows as well.
[code]Sub imAccountsTextWrite()
'******************************************************************
’ Last change: 2014-02-26
’ Author : Oliver Osswald
’ Purpose: Write the accounts to a tab delimited textfile
'******************************************************************
Dim lb As imCashListbox = lbAccounts
If lb.ListCount > 0 Then
Dim fName As String = im.GetNewFileName(SpecialFolder.Documents,"accounts",".txt")
Dim dlg As New SaveAsDialog
Dim ExportFile As FolderItem
dlg.InitialDirectory=SpecialFolder.Documents
dlg.promptText=imStrings.Enter_a_new_filename // Enter a new filename
dlg.SuggestedFileName=fName
dlg.Title=imStrings.Save_accounts_to_textfil
ExportFile = dlg.ShowModal
// If there is no such folderitem, stop here
If ExportFile Is Nil Then Return
// Check if file exists and delete it if ever
If dlg.result <> Nil Then
If dlg.result.Exists Then
dlg.result.Delete
End If
End If
// Create a temp file
Dim f As FolderItem = GetTemporaryFolderItem
// Write to the tempfile
If f <> Nil Then
If f.Exists Then
Try
Dim t As TextOutputStream = TextOutputStream.Create(f)
Try
t.WriteLine("// Cashbook Pro accounts list. See www.cashbook.pro for details.")
t.WriteLine("// Tabulator delimited list with 3 columns :")
t.WriteLine("// column 1 : Account Number")
t.WriteLine("// column 2 : Account Name")
t.WriteLine("// column 3 : Account Type (0=Expense, 1=Income)")
Dim rLine As String
// Write contents of lbAccounts to textfile, tab delimited fields
For i As Integer = 0 To lb.ListCount -1
rLine = ""
For j As Integer = 0 To lb.ColumnCount -1
If j = 2 Then
rLine = rLine + If(coRowTag(lb.RowTag(i)).IsIncome,"1","0")
Else
rLine = rLine + lb.Cell(i,j) + Chr(9)
End If
Next
'rLine = Left(rLine,Len(rLine)-1)
t.WriteLine(rLine)
Next
Finally
t.Close
t = Nil
End Try
Catch e As IOException
Return
End Try
// Copy the tempfile to the selected export folder
Try
f.CopyFileTo(ExportFile)
Catch e As IOException
Return
End Try
// Delete the temp file
If f.Exists Then
f.Delete
End If
End If // f.Exists
End If // f <> Nil
