OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Open xls file same way like csv?
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Igi
Newbie
Newbie


Joined: 24 Feb 2004
Posts: 2
Location: Slovakia

PostPosted: Tue Feb 24, 2004 3:55 am    Post subject: Open xls file same way like csv? Reply with quote

Hi!
In our company we are using SAP.I want to export data from it.If i saves a document to excel format with extension .xls OO open it in writer like text document.When i rename it to csv it is working fine because the output from SAP is a single text and as separator is used TAB.I don't want to rename the file all the time.Is there any possibility to open the file without rename it(with xls extension)?

P.S:Sorry for my english!
Back to top
View user's profile Send private message
Scottxb
Guest





PostPosted: Tue Feb 24, 2004 5:02 am    Post subject: This is a bug in SAP. Reply with quote

I don't know the answer to your question, but this is definetly a bug in SAP.
Your file is in fact a csv file and should be named as a csv file.
SAP should export this file with a csv extension, that way, it would work properly in excell and in OO.
Back to top
v1_pandya
Newbie
Newbie


Joined: 22 Feb 2004
Posts: 1
Location: Ahmedabad - India

PostPosted: Fri Mar 18, 2005 2:31 am    Post subject: BUG or Featureless ? Reply with quote

You can try by using INSERT --> Sheet from File --> Select the xls file exported by the SAP (report).

In this case your user will not required to rename the file & then open the file.

We are also facing the same problem & because of this problem users are not ready to use the OpenOffice.

Same file gets open in excel without any rename or using insert data option.

Lets hope some one get the solution.

cheer.

Virendra
Back to top
View user's profile Send private message
bobharvey
Super User
Super User


Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire

PostPosted: Fri Mar 18, 2005 3:15 am    Post subject: Re: BUG or Featureless ? Reply with quote

v1_pandya wrote:
Same file gets open in excel without any rename or using insert data option.


Thats because Excel and Calc handle .csv files differently. They are, after all, different programmes.
Back to top
View user's profile Send private message
ms777
Super User
Super User


Joined: 07 Feb 2004
Posts: 1355

PostPosted: Sun Mar 20, 2005 5:05 am    Post subject: Reply with quote

Hi,

this is a macro I am using for overriding the calc import filter mechanisms. It forces OO to open a file as CSV, independent of file name / extension.
Should be rather self explanatory - please ask when problems arise

Code:
Sub ForceCSVOpen

'Use this line if you always want to load the same file at the same location
'sUrl    = "file:///C:/WINDOWS/Desktop/test2.csv"   

'Alternatively, use this line if you want to browse for the file
sUrl    = BrowseForFile("Browse for File to be imported as CSV", "any file", "C:\windows\desktop", "*.*")

'use this line if you always want to use the same specifications for the CSV import
'for details, check http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.htm#1+2+2+3+2+Filter+Options+for+the+CSV+Filter
'sFilter = "9,,ANSI,1," ' Tab (Chr(9)) delimiter, no text delimiter, ANSI coding

' Alternatively, use this line if you want to be asked for the options every time
sFilter = GetFilterOptionsFromCSVImportDialog(sUrl)

if sFilter <>"" then
 o = loadCSVfromURL(sUrl, sFilter, false)
 endif
End Sub

'----- Basic laymen should not modify below this line

function loadCSVfromURL(sUrl as String, sFilter as String, bHidden as Boolean) as Object
Dim aProps(2) As New com.sun.star.beans.PropertyValue
aProps(0).Name  = "FilterName"
aProps(0).Value = "Text - txt - csv (StarCalc)"
aProps(1).Name  = "FilterOptions"
aProps(1).Value = sFilter   
aProps(2).Name  = "Hidden"
aProps(2).Value = bHidden
loadCSVfromURL  = StarDesktop.loadComponentFromURL(sUrl, "_blank", 0, aProps())
end function


'----------
'   Lets the user select a file with a given filter
'   call like sFile = BrowseForFile("Pick the file downloaded from Postbank", "Postbank download file", "C:\Windows\Desktop","PB*.csv")
'   returns "" when not successful

function BrowseForFile(sTitle as String, sFilterName as String, sStartDirectory as String, sFilter as String) as String
dim ars
o=CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
o.setTitle(sTitle)
o.appendFilter(  sFilterName & " (" & sFilter & ")", sFilter)
o.setMultiSelectionMode(false)
o.setDisplayDirectory(ConvertToUrl(sStartDirectory))
 
if o.execute() = com.sun.star.ui.dialogs.ExecutableDialogResults.OK then
  ars = o.getFiles()
  BrowseForFile = ars(UBound(ars))
  else
  BrowseForFile = ""
  endif
end function


function GetFilterOptionsFromCSVImportDialog(sUrl) as String
GetFilterOptionsFromCSVImportDialog = ""

oSFA = createUNOService ("com.sun.star.ucb.SimpleFileAccess")
oInputStream = oSFA.openFileRead(sUrl)

Dim aProps(3) as new com.sun.star.beans.PropertyValue
aProps(0).Name = "FilterOptions"
aProps(0).Value = ""
aProps(1).Name = "URL"
aProps(1).Value = sUrl
aProps(2).Name = "FilterName"
aProps(2).Value = "Text - txt - csv (StarCalc)"
aProps(3).Name = "InputStream"
aProps(3).Value = oInputStream

o = createUnoService("com.sun.star.ui.dialogs.FilterOptionsDialog")
o.setPropertyValues(aProps())

if o.execute() = com.sun.star.ui.dialogs.ExecutableDialogResults.OK then
  aPropOut = o.getPropertyValues()
  GetFilterOptionsFromCSVImportDialog = aPropOut(0).Value
  endif
oInputStream.closeInput()
end function



Added June 21, 2009: Due to some changes in OO, starting from OO 3.0 (or maybe earlier), you have to replace
Code:
o = createUnoService("com.sun.star.ui.dialogs.FilterOptionsDialog")

by
Code:
o = createUnoService("com.sun.star.comp.Calc.FilterOptionsDialog")


Last edited by ms777 on Sun Jun 21, 2009 6:43 am; edited 1 time in total
Back to top
View user's profile Send private message
silver12
General User
General User


Joined: 19 Mar 2005
Posts: 5
Location: Indiana

PostPosted: Sun Mar 20, 2005 6:54 am    Post subject: ForceCSVOpen Reply with quote

Works great - thanks!

How would you filter the following? Ignoring the 1st token - "Fri" = 9? so that only the last six tokens are imported? And how would you specify so the three spaces before the last token are treated as one space?

Fri 18-Mar-2005 10627.8300 10653.6900 10557.0400 10629.6700 696213312
Back to top
View user's profile Send private message
ms777
Super User
Super User


Joined: 07 Feb 2004
Posts: 1355

PostPosted: Sun Mar 20, 2005 10:10 am    Post subject: Reply with quote

for the filter options, have a look into http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.htm#1+2+2+3+2+Filter+Options+for+the+CSV+Filter

alternatively, I would recommend to insert a msgbox after the sFilter setting:
Code:
sFilter = GetFilterOptionsFromCSVImportDialog(sUrl)
msgbox "FilterOptions: " & sFilter

Then you can use the OO CSV filter dialog to adjust all options, and just have to write down the FilterOption string generated
Back to top
View user's profile Send private message
eriksmith200
General User
General User


Joined: 17 Aug 2006
Posts: 6

PostPosted: Thu Aug 17, 2006 3:06 am    Post subject: Reply with quote

the macro doesn't work in linux, does anyone know what to change to make it work?

maybe someone should start a poll to show the devs how bad we want to be able to open a file with the app of our choice, also from outside of OO? i'm used to being able to customise everything under linux so when a program makes decisions for me which i cannot change it really annoys me...
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Thu Aug 17, 2006 3:21 am    Post subject: Q Reply with quote

Quote:
the macro doesn't work in linux

Where is it failing and what message are you getting? The string C:\windows\desktop is one obvious change that's needed.

FAQ #001: How do I open a tab-delimited file in OpenOffice.org Spreadsheet? What if I have a different type of delimiter?
_________________
search forum by month
Back to top
View user's profile Send private message
eriksmith200
General User
General User


Joined: 17 Aug 2006
Posts: 6

PostPosted: Thu Aug 17, 2006 3:39 am    Post subject: Re: Q Reply with quote

noranthon wrote:
Quote:
the macro doesn't work in linux

Where is it failing and what message are you getting? The string C:\windows\desktop is one obvious change that's needed.

FAQ #001: How do I open a tab-delimited file in OpenOffice.org Spreadsheet? What if I have a different type of delimiter?


the error message i get is:
Quote:

BASIC runtime error.
An exception occurred
Type: com.sun.star.uno.RuntimeException
Message: the external file picker does not run


i think there might be a problem with my JVM.
when i go to options > java i see the 'Free Software Foundation 1.4.2' jre listed. when trying to add a new one when i browse to my java sun binaries i cannot select a file anywhere...

about the FAQ you mention: i'm aware of this option but i open so many tab delimited txt files that changing file extensions or looking through endless dropdown boxes just isn't an option

[edit]managed to add 'Sun Microsystems Inc. 1.5.0_06' by adding the class path folder but i still get the same errormessage[/edit]
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Thu Aug 17, 2006 4:16 am    Post subject: Reply with quote

It seems to run on my system but I have only one non-csv text file to try it on. Have you altered the path in the following line:
Code:
sUrl    = BrowseForFile("Browse for File to be imported as CSV", "any file", "C:\windows\desktop", "*.*")

Instead of C:\windows\desktop, you will need something like /home/erik/<etc - path to the directory where your Calc files are saved>

If you've done that, perhaps ms777 will shed some light on why you are getting that error.

I've just remembered that I meant to comment on your suggestion of a poll. I believe it will cut no ice. I doubt that any developer would see it. The procedure for instigating such a change is to file an issue. My guess is that at least one relevant issue has long since been filed and the only effective poll is for users to vote for the issue.
_________________
search forum by month
Back to top
View user's profile Send private message
eriksmith200
General User
General User


Joined: 17 Aug 2006
Posts: 6

PostPosted: Thu Aug 17, 2006 4:31 am    Post subject: Reply with quote

noranthon wrote:
It seems to run on my system but I have only one non-csv text file to try it on. Have you altered the path in the following line:
Code:
sUrl    = BrowseForFile("Browse for File to be imported as CSV", "any file", "C:\windows\desktop", "*.*")

Instead of C:\windows\desktop, you will need something like /home/erik/<etc - path to the directory where your Calc files are saved>

If you've done that, perhaps ms777 will shed some light on why you are getting that error.

I've just remembered that I meant to comment on your suggestion of a poll. I believe it will cut no ice. I doubt that any developer would see it. The procedure for instigating such a change is to file an issue. My guess is that at least one relevant issue has long since been filed and the only effective poll is for users to vote for the issue.


already changed the path with no effect.
of:
o=CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
o.setTitle(sTitle)
the second line fails.

i will try n find the issue and see if i can vote...
Back to top
View user's profile Send private message
eriksmith200
General User
General User


Joined: 17 Aug 2006
Posts: 6

PostPosted: Thu Aug 17, 2006 4:42 am    Post subject: Reply with quote

eriksmith200 wrote:

i will try n find the issue and see if i can vote...


doing a very brief search i found a bunch of issues which all refer to each other and are closed as duplicates:
http://www.openoffice.org/issues/show_bug.cgi?id=42777 is closed because duplicate of:
http://www.openoffice.org/issues/show_bug.cgi?id=42064 which is closed because of duplicate of 42777 Rolling Eyes

49492, 65076 and 11802 are closed duplicates of
http://www.openoffice.org/issues/show_bug.cgi?id=50358
which is closed because you can rename to csv or use dropdown blablabla which is the whole issue.....
Back to top
View user's profile Send private message
eriksmith200
General User
General User


Joined: 17 Aug 2006
Posts: 6

PostPosted: Thu Aug 17, 2006 4:57 am    Post subject: Reply with quote

found an issue here:
http://www.openoffice.org/issues/show_bug.cgi?id=23078
so vote!

there's also a closed issue here:
http://www.openoffice.org/issues/show_bug.cgi?id=42804
works from the command line but not from UI, but i guess i'll just make a simple shell script for now....
Back to top
View user's profile Send private message
ms777
Super User
Super User


Joined: 07 Feb 2004
Posts: 1355

PostPosted: Thu Aug 17, 2006 10:22 am    Post subject: Reply with quote

eriksmith200 wrote:

...already changed the path with no effect.
of:
o=CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
o.setTitle(sTitle)
the second line fails.

i will try n find the issue and see if i can vote...


Did you try change th settings for the open file dialog (OO vs. System, in Tools/Options/Openoffice.org/General/OpenSave dialogs) ? The failure in setTitle indicates that there is some general problem with the file open dialog ...
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group