| View previous topic :: View next topic |
| Author |
Message |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 7649
Location: Germany
|
 Posted: Mon Mar 19, 2007 3:30 pm Post subject: |
 |
|
OK, it is really simple.
Save a template with all the required formattings.
Open one of your csv properly with all settings the import-wizzard has to offer.
Run ShowFilterOptions(). You get a input-box from where you can copy the appropriate filter options string.
In sub openSpecialCSV edit the two constants for FilterOptions and path of the template file.
I have tested this with the following shell-command, which should be adjustable to a Windows link or link to a shell-script.
| Code: |
soffice 'macro:///Calc.csv.openSpecialCSV(home/andreas/umsatz/umsatz-6942346-02102006.csv)'
|
EDIT: the command calls soffice to execute a Basic macro in Library "Calc", module "csv", routine "openSpecialCSV", passing an argument which specifies the csv to be copied into a formatted template.
| Code: |
REM ***** BASIC *****
Sub ShowFilterOptions()
args = thiscomponent.getArgs()
for i = 0 to uBound(args())
if args(i).Name = "FilterOptions" then
inputbox "FilterOptions: ","ShowFilterOptions", args(i).Value
exit for
endif
next
End Sub
Sub openSpecialCSV(optional arg)
'calls getusedRange, getopenCSV
REM this is derived from a manually opened file and Sub ShowFilteroptions:
Const myFilterOptions$ = "59/MRG,34,ANSI,1,1/2/2/4/3/4/4/2/5/2/6/2/7/2/8/2/10/2/11/2"
REM Path to template file:
Const myTemplate$ = "/home/andreas/.openoffice.org2/user/template/formatCSV.ots"
if isMissing(arg) then
Msgbox "Missing File"
exit sub
elseif not FileExists(arg) then
Msgbox arg &" is not a file"
exit sub
else
csvURL = ConvertToURL(arg)
oCSV = getOpenCSV(csvURL, myFilterOptions)
oSource = getUsedRange(oCSV.sheets.getByIndex(0))
lCols = oSource.Columns.getCount() -1
lRows = oSource.Rows.getCount() -1
aData() = oSource.getDataArray()
templateURL = ConvertToURL(myTemplate)
oTemplate = StarDesktop.loadComponentFromURL(templateURL, "_blank",0, Array())
'use first sheet:
oSheet = oTemplate.Sheets.getByindex(0)
'StartColumn, StartRow, EndColumn, EndRow
oTarget = oSheet.getCellRangeByPosition(0, 0, lCols, lRows)
oTarget.setDataArray(aData())
' oCSV.close(False)
endif
End Sub
Function getOpenCSV(sURL$,sFilterOptions$)
Dim aProps(1) 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 = sFilterOptions
getOpenCSV = StarDesktop.loadComponentFromURL(sURL, "_blank",0, aProps())
end Function
Function getUsedRange(oSheet)
Dim oRg
oRg = oSheet.createCursor()
oRg.gotoStartOfUsedArea(False)
oRg.gotoEndOfUsedArea(True)
getUsedRange = oRg
End Function
|
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06 |
|
| Back to top |
|
 |
JJJoseph
Power User
Joined: 25 Sep 2005
Posts: 81
|
| Posted: Thu Mar 22, 2007 11:18 pm Post subject: Importing data |
|
|
"Databases with reports and forms are made for tasks like this one. "
There's more than just one user with this infernal problem! The xls data format (plus the cvs, dbf formats) is a universal standard. Everybody emails/uploads files in these formats. I agree, OO should be able to read/import simple data files (i.e. xls, dbf,cvs) easily with fumbling around. |
|
| Back to top |
|
|
JJJoseph
Power User
Joined: 25 Sep 2005
Posts: 81
|
| Posted: Thu Mar 22, 2007 11:19 pm Post subject: Importing data |
|
|
| Correction: That should be "...without fumbling around." |
|
| Back to top |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3323
|
| Posted: Fri Mar 23, 2007 4:06 am Post subject: |
|
|
Hi, fgad&c. An issue has been filed in relation to tab separated values (.tsv): http://www.openoffice.org/issues/show_bug.cgi?id=23078
Add your votes (max. 2) and comments - it may help to draw attention to the issue again.
It seems to me that OOo developers, particularly the ones on Sun's payroll, occupy a different universe from everyone else. They proffer these silly workarounds as though they are inspired works of art.
_________________
search forum by month |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 7649
Location: Germany
|
| Posted: Fri Mar 23, 2007 4:24 am Post subject: |
|
|
btw: Can Excel 2007 export floating point numbers meanwhile?
http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html#excel
How does Excel import csv into a prepared template with colours, column-widths, names, styles, analysis-sheets, charts, pivots? This is actually what my macro does.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06 |
|
| Back to top |
|
|
tar1827
General User
Joined: 23 Mar 2007
Posts: 18
|
| Posted: Fri Mar 23, 2007 11:26 am Post subject: Re: Importing data |
|
|
| fgadflgkjaglkj wrote: | "Databases with reports and forms are made for tasks like this one. "
There's more than just one user with this infernal problem! The xls data format (plus the cvs, dbf formats) is a universal standard. Everybody emails/uploads files in these formats. I agree, OO should be able to read/import simple data files (i.e. xls, dbf,cvs) easily with fumbling around. |
That's for sure. I find it hard to believe that developers don't allow for the user to know what he/she wants. I just instructed Calc to open a tab-delimited file and it opened in Write! Explain to me how this makes sense...I can understand if the office suite needs to guess at what I want; however, if I use the Calc menu choice to open a file, then it should at least try. When you try to open a text file in Excel it doesn't open it in Word...why? Because you used Excel to open it! If it needs to, it offers up the import dialog, which makes sense, but it doesn't assume that you want to use a word processor with the file, as if you were too stupid to know any better. 
Furthermore, there ought to be a setting somewhere to override this idiotic behavior, but I can't find one. I frequently work with tab-delimited files in Excel. I want to switch to OO, but if I have to rename files to *.csv before Calc will open them, then I may stick with Excel. Unbelievable... |
|
| Back to top |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3323
|
|
| Back to top |
|
|
JJJoseph
Power User
Joined: 25 Sep 2005
Posts: 81
|
| Posted: Sat Apr 28, 2007 10:17 pm Post subject: Calc data formats |
|
|
| Villeroy wrote: | btw: Can Excel 2007 export floating point numbers meanwhile?
How does Excel import csv into a prepared template with colours, column-widths, names, styles, analysis-sheets, charts, pivots? This is actually what my macro does. |
This is an interesting question, but it has nothing to do with the topic being discussed. There is an important place for scripted tasks as you have outlined, but that type of script is of no help for someone sorting data from xls to txt to dbf to tab-delimited - and back again.
BTW, I've tried to figure out how to vote for an issue (in order to capture the attention of the developers), and voting is COMPLICATED! Not many users appear to figure out the voting process. I struggled with it for a while, and it finally told me to"try again in 48 HOURS"! It's not surprising that not a lot of voting gets done. |
|
| Back to top |
|
|
|
|
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
|
