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

Macro - loading multiple CSV into calc

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


Joined: 18 Mar 2010
Posts: 21

PostPosted: Thu Mar 18, 2010 6:39 am    Post subject: Macro - loading multiple CSV into calc Reply with quote

Is it possible to load (using macro) multiple csv files into calc document, each file into different sheet ?

Thanks in advance.
Back to top
View user's profile Send private message
i_jens
General User
General User


Joined: 20 Mar 2010
Posts: 9

PostPosted: Sat Mar 20, 2010 12:33 pm    Post subject: Reply with quote

Check here: http://www.oooforum.org/forum/viewtopic.phtml?t=15678&highlight=csv - there is a multitude of threads in this forum with this topic.

Anyhow - I modified it to my needs: It will insert the csv given by fileName into a new table. I have "|" as delimiter - but simply replace the ascii code accordingly ("|" = 124)

Code:

Sub ImportCSVIntoTable(tableName as String, fileName as String)

Dim oDoc as Object
Dim oSheet as Object
Dim oPlan as Object

   oDoc = thisComponent
   oSheet = oDoc.createInstance ( "com.sun.star.sheet.Spreadsheet" )
   oDoc.Sheets.insertByName ( tableName, oSheet )
   oPlan = oDoc.Sheets.getByName(tableName)
      
      sURL = ConvertToURL ( fileName )
      sOrigin = ""
      sFilter = "Text - txt - csv (StarCalc)"
      sOpc = "124,,0,1,1/2/2/1/3/2"
      nModo = com.sun.star.sheet.SheetLinkMode.NORMAL
      ' link file
      oPlan.link(sURL, sOrigin, sFilter, sOpc, nModo)
      ' reset link
      oPlan.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
End Sub
Back to top
View user's profile Send private message
auser99
Newbie
Newbie


Joined: 17 Feb 2010
Posts: 1

PostPosted: Sat Mar 20, 2010 8:02 pm    Post subject: thanks Reply with quote

Thanks for the reply.

au

http://www.xprad.org/
Back to top
View user's profile Send private message
jody
General User
General User


Joined: 30 Mar 2010
Posts: 13

PostPosted: Tue Mar 30, 2010 11:35 am    Post subject: Reply with quote

@i_jens:
I would like to use your code - could you explain the content of the string sOpc?
From your comment i assume that the first number is the ascii code of the separator
character. But what about the others - the empty one, the " 0", the "1" and the "1/2/2/1/3/2"?

Thank You
Jody
Back to top
View user's profile Send private message
jan.novak
Newbie
Newbie


Joined: 30 Mar 2010
Posts: 2

PostPosted: Tue Mar 30, 2010 1:36 pm    Post subject: Reply with quote

Here is description of Filter Options for the CSV Filter.

http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options


jody wrote:
@i_jens:
I would like to use your code - could you explain the content of the string sOpc?
From your comment i assume that the first number is the ascii code of the separator
character. But what about the others - the empty one, the " 0", the "1" and the "1/2/2/1/3/2"?

Thank You
Jody
Back to top
View user's profile Send private message
jody
General User
General User


Joined: 30 Mar 2010
Posts: 13

PostPosted: Wed Mar 31, 2010 8:05 am    Post subject: Reply with quote

Thank you for the link!

I have another question though:
From the documentation and the example given in this link, it looks like
i have to know in advance how many fields are to be expected, and
what types they have. Is this correct?

But when i look at OpenOfficeCalc loading a csv file, it only asks about the
character set, the start row, the separator character, and the text delimiter
(and even makes good guesses) and does not need to be told about the
number of colons and their formats. So i should think there should be
the possibility to load a csv file the same way by a "VB" function,
i.e. without knowledge of number and formats of columns.
Or am i completely wrong here?

Thank You
jody
Back to top
View user's profile Send private message
i_jens
General User
General User


Joined: 20 Mar 2010
Posts: 9

PostPosted: Wed Mar 31, 2010 8:15 am    Post subject: Reply with quote

If you do not specify it explictly the macro will do a 'good guess' - so there are no problems if you omit that part (at least that's how it worked for me - no expert here).

sOpc = "ASCII CODE Field Separator, ASCII CODE Text Delimiter, Character Set, Starting Line" is all you need to specify
Back to top
View user's profile Send private message
APU
General User
General User


Joined: 18 Mar 2010
Posts: 21

PostPosted: Wed Apr 07, 2010 1:16 am    Post subject: Reply with quote

I tried a code similar to that of i_jens (thanks again), but loading CSV into existing sheets rather than new ones. It worked fine, except for one problem - loading data destroyed the original cell formatting of the sheets (background color etc), which I wanted to keep.

So I went other way: opening CSV in a hidden temporary document, and then copying the contents to the relevant sheet of the original document ( the code can be seen here: http://www.oooforum.org/forum/viewtopic.phtml?p=368767&highlight=#368767 ). It keeps cell formatting of the original sheet. However, I have a problem with multiline cells, which are displayed as single lines ( also described in more detail in that thread ).

I wonder if there is some other way to keep the original cell formatting from being destroyed ?
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