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

Loading csv files into different sheets

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
Bombadillo
Newbie
Newbie


Joined: 03 Jan 2005
Posts: 4

PostPosted: Mon Jan 03, 2005 5:34 pm    Post subject: Loading csv files into different sheets Reply with quote

Hi, I've started learning basic and I'm trying to write a macro that loads different csv files into different sheets of the same spreadsheet. The problem is that I don't know how to reproduce the "Insert Sheet->From File" behaviour. The only function I found is loadComponentFromURL, but it's not what I need (at least I think...). Can anyone help me, please?

P.S.: As csv files are generated by a C++ program I wrote, how difficult would it be to use OpenOffice API C++ Binding to create a spreadsheet directly from that program?
Back to top
View user's profile Send private message
uros
Super User
Super User


Joined: 22 May 2003
Posts: 601
Location: Slovenia

PostPosted: Tue Jan 04, 2005 3:02 am    Post subject: Reply with quote

Hi Bombadillo!

Try this solution to import two (or more) csv files in one Calc dokument:
http://www.oooforum.org/forum/viewtopic.php?t=13861&start=0&postdays=0&postorder=asc&highlight=csv

Calc document is XML document, I asume you can create it directly with C++. Sorry, I don't know anything about C++.

Uros
Back to top
View user's profile Send private message
Bombadillo
Newbie
Newbie


Joined: 03 Jan 2005
Posts: 4

PostPosted: Tue Jan 04, 2005 5:48 am    Post subject: Thanks Reply with quote

Thank you very much uros Smile. It works perfectly (and it's easier than I thought)!
Back to top
View user's profile Send private message
Danad
OOo Advocate
OOo Advocate


Joined: 22 Feb 2004
Posts: 293
Location: Brasil

PostPosted: Tue Jan 04, 2005 8:05 pm    Post subject: Reply with quote

Another way ( XSheetLinkable ) that works for me:
Code:

Sub Ascii_2_CalcSheets
   oDoc = thisComponent
   ' 2nd sheet
   oPlan = oDoc.getSheets().getByIndex(1)
   ' set arguments
   sURL = "C:\Documents and Settings\danad\My Documents\some_asc_file.txt"
   sURL = ConvertToURL ( sURL )
   sOrigem = ""
   sFiltro = "Text - txt - csv (StarCalc)"
   sOpc = "59,,0,1,1/2/2/1/3/2"
   nModo = com.sun.star.sheet.SheetLinkMode.NORMAL
   ' link file
   oPlan.link(sURL, sOrigem, sFiltro, sOpc, nModo)
   ' reset link
   oPlan.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
   ' 3rd sheet
   oPlan = oDoc.getSheets().getByIndex(2)
   ' same arguments, except last column
   sOpc = "59,,0,1,1/2/2/1/3/9"
   oPlan.link(sURL, sOrigem, sFiltro, sOpc, nModo)
   ' reset link
   oPlan.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
End Sub


HTH
Back to top
View user's profile Send private message
uros
Super User
Super User


Joined: 22 May 2003
Posts: 601
Location: Slovenia

PostPosted: Tue Jan 04, 2005 10:47 pm    Post subject: Reply with quote

Hi Danad!

Great job, much better then my way! Very Happy Very Happy

Uros
Back to top
View user's profile Send private message
Bombadillo
Newbie
Newbie


Joined: 03 Jan 2005
Posts: 4

PostPosted: Wed Jan 05, 2005 8:12 am    Post subject: Reply with quote

I've tried Danad's solution and its quite faster (I have to load nearly a hundred of csv files), thank you very much Very Happy Now a final question: as some sheets contain data from different files, is there a way to link external data into a sheet starting from a given position? I've tried to look at documentation but I haven't found anything, so for the moment I simply create a temporary sheet and then copy data from it (like in uros' code).

Bye!
Back to top
View user's profile Send private message
Danad
OOo Advocate
OOo Advocate


Joined: 22 Feb 2004
Posts: 293
Location: Brasil

PostPosted: Wed Jan 05, 2005 6:00 pm    Post subject: Reply with quote

Uros,
Thanks for the kind words.

Bombadillo,

Look for ( dev's guide ):
- method insertAtPosition of interface XAreaLinks
- property AreaLinks
- struct CellAddress

Code is almost the same as previous sample.


Last edited by Danad on Thu Jan 06, 2005 5:21 pm; edited 1 time in total
Back to top
View user's profile Send private message
Bombadillo
Newbie
Newbie


Joined: 03 Jan 2005
Posts: 4

PostPosted: Thu Jan 06, 2005 7:13 am    Post subject: Reply with quote

Thank you very much Danad Smile
Back to top
View user's profile Send private message
rufus
General User
General User


Joined: 18 May 2007
Posts: 20
Location: Bangkok, Thailand

PostPosted: Tue May 22, 2007 5:15 am    Post subject: Reply with quote

Danad,
Could you explain more detail about inserting data at current position. I found the insertAtPosition method in Developers Guide, but i did not understand how to use it.
Thank you.
Back to top
View user's profile Send private message
Danad
OOo Advocate
OOo Advocate


Joined: 22 Feb 2004
Posts: 293
Location: Brasil

PostPosted: Tue May 22, 2007 5:14 pm    Post subject: Reply with quote

There's an old snip here:

http://www.oooforum.org/forum/viewtopic.phtml?t=23067&highlight=insertatposition

HTH
Back to top
View user's profile Send private message
rufus
General User
General User


Joined: 18 May 2007
Posts: 20
Location: Bangkok, Thailand

PostPosted: Tue May 22, 2007 9:49 pm    Post subject: Reply with quote

Danad,
I tried your code, but it works not correctly for me. I need to import CSV-file into Calc in "DOS/OS2 866 Cyrr" codepage at 6th row.

When i use this code:

sub ImportCSVIntoSheet
oDoc = thisComponent
' 1st sheet
oPlan = oDoc.getSheets().getByIndex(0)
' set arguments
sURL = "file:///C:/Zakaz/Zakaz_oo/zakaz.csv"
'sURL = ConvertToURL ( sURL )
sOrigem = ""
sFiltro = "Text - txt - csv (StarCalc)"
sOpc = "44,34,30,1,"
nModo = com.sun.star.sheet.SheetLinkMode.NORMAL
' link file
oPlan.link(sURL, sOrigem, sFiltro, sOpc, nModo)
' reset link
oPlan.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
end sub

it imports in correct codepage ("44,34,30,1," parameters are responsible for this), but at 1st row. But when i try this:

Sub ex_Area_Link
Dim oDoc As Object
Dim oPlan As Object
Dim oLinks As Object
Dim aPos As New com.sun.star.table.CellAddress
oDoc = ThisComponent
oPlan = oDoc.getSheets().getByIndex(0)
sURL = "file:///C:/Zakaz/Zakaz_oo/zakaz.csv"
sFonte = "Planilha2.A1:E5"
sFiltro = "Text - txt - csv (StarCalc)"
sOpcoes = "44,34,30,1,"
aPos.Sheet = 0
aPos.Column = 0
aPos.Row = 5
oLinks = oDoc.AreaLinks
oLinks.insertAtPosition(aPos, sURL, sFonte, sFiltro, sOpcoes)
oLinks.removeByIndex(oLinks.Count - 1)
End Sub

it imports at 6th row, BUT in wrong codepage, like this:

22040560942  ᪨  36
22040560946  ᪨  40
22040560948  ᪨  42
22040561940  ᪨   34
22040561942  ᪨   36

How can i fix it?
Back to top
View user's profile Send private message
Danad
OOo Advocate
OOo Advocate


Joined: 22 Feb 2004
Posts: 293
Location: Brasil

PostPosted: Wed May 23, 2007 7:16 am    Post subject: Reply with quote

As you can see here (section 9.2.2):
http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.xhtml

the third token (30 in your case) refers to character set.

The only thing (AFAIK) documented is that a value 0 => to the System char set.

HTH
Back to top
View user's profile Send private message
rivella
Newbie
Newbie


Joined: 01 Feb 2011
Posts: 3

PostPosted: Sat Feb 05, 2011 7:37 am    Post subject: Reply with quote

Danad wrote:
As you can see here (section 9.2.2):
http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.xhtml

the third token (30 in your case) refers to character set.

The only thing (AFAIK) documented is that a value 0 => to the System char set.

HTH

The character set values are documented here:
http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options
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 Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
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