| View previous topic :: View next topic |
| Author |
Message |
Igi
Newbie
Joined: 24 Feb 2004
Posts: 2
Location: Slovakia
|
 Posted: Tue Feb 24, 2004 3:55 am Post subject: Open xls file same way like csv? |
 |
|
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 |
|
 |
Scottxb
Guest
|
| Posted: Tue Feb 24, 2004 5:02 am Post subject: This is a bug in SAP. |
|
|
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
Joined: 22 Feb 2004
Posts: 1
Location: Ahmedabad - India
|
| Posted: Fri Mar 18, 2005 2:31 am Post subject: BUG or Featureless ? |
|
|
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 |
|
|
bobharvey
Super User
Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire
|
| Posted: Fri Mar 18, 2005 3:15 am Post subject: Re: BUG or Featureless ? |
|
|
| 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 |
|
|
ms777
Super User
Joined: 07 Feb 2004
Posts: 1355
|
| Posted: Sun Mar 20, 2005 5:05 am Post subject: |
|
|
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 |
|
|
silver12
General User
Joined: 19 Mar 2005
Posts: 5
Location: Indiana
|
| Posted: Sun Mar 20, 2005 6:54 am Post subject: ForceCSVOpen |
|
|
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 |
|
|
ms777
Super User
Joined: 07 Feb 2004
Posts: 1355
|
|
| Back to top |
|
|
eriksmith200
General User
Joined: 17 Aug 2006
Posts: 6
|
| Posted: Thu Aug 17, 2006 3:06 am Post subject: |
|
|
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 |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3318
|
|
| Back to top |
|
|
eriksmith200
General User
Joined: 17 Aug 2006
Posts: 6
|
| Posted: Thu Aug 17, 2006 3:39 am Post subject: Re: Q |
|
|
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 |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3318
|
| Posted: Thu Aug 17, 2006 4:16 am Post subject: |
|
|
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 |
|
|
eriksmith200
General User
Joined: 17 Aug 2006
Posts: 6
|
| Posted: Thu Aug 17, 2006 4:31 am Post subject: |
|
|
| 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 |
|
|
eriksmith200
General User
Joined: 17 Aug 2006
Posts: 6
|
|
| Back to top |
|
|
eriksmith200
General User
Joined: 17 Aug 2006
Posts: 6
|
|
| Back to top |
|
|
ms777
Super User
Joined: 07 Feb 2004
Posts: 1355
|
| Posted: Thu Aug 17, 2006 10:22 am Post subject: |
|
|
| 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 |
|
|
|
