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

.dat files import

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
BasicGuy
General User
General User


Joined: 30 Jun 2009
Posts: 40

PostPosted: Sat May 26, 2012 5:27 pm    Post subject: .dat files import Reply with quote

There are several posts on how to import .dat files into OO.calc, but none of them is the silver bullet needed.

I have data coming in every night from a datalogger in .dat form. I use a two sheet SS template to process the data. The raw data from the converted .dat file goes to the second sheet. The first sheet is the display page with all the graphs and values derived from the data.

The "link to external data" route WOULD work perfectly IF it did not require a Range name inside the file. The .dat files have no range names in them. Is there any automatic way to make the "range" default to the whole file? This would be extremely useful if it would work without requiring a range name. I could go directly to my calculation template file, link to the external data and have it pop into sheet two and be done.

Now, I have to use a multistep process of
1. converting the file to an oo.calc file, then
2. copy and paste the contents into sheet two of my template, then
3. delete the original file from 1.

On windows, I can declare that .dat files should be opened with OO.calc. Double click on the file and it opens in the oo.calc dialog for text files, click OK and it opens in OO.calc. That gives me step 1. above.

I can't do this on a Mac. There is no OO.calc creator file, only generic OO, which will always open the .dat file in writer. It is too tedious to go this way.

The OPEN command with selection of "CSV(text)" doesn't work. I could not get any of the OPEN file types to open the .dat file.

The "insert sheet from file" works, but again is to tedious for any number of files. While it work in importing .dat into an OO SS, I now have several more steps, which is a killer when there are many files to be processed.

I have seen other posts from people processing .dat files. I bet they have the same problems.

The two solutions that would work are:

1. Have the "link to external data" command load the file [preferred]
2. have a creator name for OO.calc that would open the file [better than the current options]

Any help would be appreciated.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun May 27, 2012 2:23 am    Post subject: Re: .dat files import Reply with quote

Collect the files in a dedicated directory.
Connect a registered Base file to this database of text files.

Only if you really, really need database data in a spreadsheet:
In Calc hit F4 and drag your table into the sheet. The imported database range will be named "Import1" (Data>Define, Data>Select, Data>Refresh)
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Jars28
General User
General User


Joined: 24 May 2012
Posts: 10

PostPosted: Sun May 27, 2012 11:15 pm    Post subject: Reply with quote

I took some code from here:
http://www.oooforum.org/forum/viewtopic.phtml?t=68077

and used it to my bidding. My Revised code for .CSV (kinda changed it up to make more sense for .DAT, but didn't change the filter options:

Code:

Sub importdata

   ' File "Open..." dialog for user to select the DAT file
   oFileDialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")

   With oFileDialog
      .appendFilter("*.DAT", "*.DAT")
      .appendFilter("All files...", "*.*")
      .Title = "Select your .DAT file"
      ' Sets the root directory of the FilePicker dialog
      .setDisplayDirectory(ConvertToURL("Volumes/Materials/"))  'set the default root folder here (the folder where your .DAT files are)   The current path is a MAC shared server file path.
      
   End With

   ' if file was selected and user clicked 'OK'
   If oFileDialog.execute() = com.sun.star.ui.dialogs.ExecutableDialogResults.OK then
       ' sURL = user selected file name
       sUrl = oFileDialog.Files(0)
      ' Define name for new sheet******* make it whatever you want**********
      sSheetName = "DATsheet"
      ' insert new sheet with defined name
      ThisComponent.Sheets.insertNewByName(sSheetName, ThisComponent.Sheets.getCount)
      ' Object for the new sheet     
      oNewSheet = ThisComponent.Sheets.getByName(sSheetName)

      ' Define import filter properties here:
      Dim FileProperties(2) As New com.sun.star.beans.PropertyValue
      FileProperties(0).Name = "FilterName"
      ' Change the filer name to one that works with DAT files
                FileProperties(0).Value ="Text - txt - csv (StarCalc)"
      FileProperties(1).Name = "FilterOptions"
      ' Define filter variables to whatever fits your needs, this is customized a LOT
      '*****last arg standard input=1, ignore=9 1/9/2/9/3/9 first three columns ignored*****
      FileProperties(1).Value ="44,34,0,1,1/1/2/1/3/9/4/9/5/9/6/9/7/1/8/1/9/1/10/9/11/9/12/1/13/1/14/1/15/1/16/9/17/1/18/9/19/9/20/9/21/1/22/1/23/9/24/9/25/9/26/9/27/9/28/9/29/9/30/9/31/9/31/9/32/9/33/9/34/9/35/9/36/9/37/9/38/9/39/9/40/9/41/9/42/9/43/9/44/9/45/9/46/9/47/9/48/9/49/9/50/9/51/9/52/9/53/9/54/9/55/9/56/9/57/9"
      FileProperties(2).Name = "Hidden"
      FileProperties(2).Value = TRUE
      ' Open user defined CSV file
      oCSV = StarDesktop.loadComponentFromURL(sUrl, "_blank", 0, FileProperties)
      ' set oSourceSheet as the first sheet on the import file
      oSourceSheet = oCSV.Sheets(0)

      Dim iiColumns as Long
      Dim iiRows as Long
      ' use function to gather last column to determine rightmost side of array
      iiColumns = iC2C_getLastUsedColumn(oSourceSheet)
      ' use function to gather last row to determine bottom side of array
      iiRows = iC2C_getLastUsedRow(oSourceSheet)
      ' pull out all data as an array
      oSourceArea = oSourceSheet.getCellRangeByPosition(0, 0, iiColumns, iiRows)
      ' set allData as an array containing source data from the CSV file
      allData = oSourceArea.getDataArray
      ' Target area in the same size set
      oEndArea = oNewSheet.getCellRangeByPosition(0, 0, iiColumns, iiRows)
      ' purely write Data array
      oEndArea.setDataArray(allData)
      ' Close CSV file 
      oCSV.close(TRUE)     
   Else
   'in order to avoid programming menu coming up with basic runtime error, quit program instead.
   MsgBox "If you cancel gathering Import Sales Order file, the program goes boom." & CHR$(10) & CHR$(10) & "The program will now close."
   ThisComponent.close(true)
   
   End If

End Sub

'FUNCTIONS****************************************************************
'*************************************************************************
' pure: Sheet as Object
' Out: Number of the last row / column (starting from zero)
Function iC2C_getLastUsedColumn(oSheet as Object) as Integer
Dim oCell As Object
Dim oCursor As Object
Dim aAddress As Variant
oCell = oSheet.GetCellbyPosition( 0, 0 )
oCursor = oSheet.createCursorByRange(oCell)
oCursor.GotoEndOfUsedArea(True)
aAddress = oCursor.RangeAddress
iC2C_getLastUsedColumn = aAddress.EndColumn
End Function

Function iC2C_getLastUsedRow(oSheet as Object) as Integer
Dim oCell As Object
Dim oCursor As Object
Dim aAddress As Variant
oCell = oSheet.GetCellbyPosition( 0, 0 )
oCursor = oSheet.createCursorByRange(oCell)
oCursor.GotoEndOfUsedArea(True)
aAddress = oCursor.RangeAddress
iC2C_GetLastUsedRow = aAddress.EndRow
End Function


You can change the code to work for a .DAT file by researching filter options here:
http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options

Use this macro to grab the correct syntax for a file path for the file dialog. It will show you the file path where the current open file you have open is saved. Make sure it has been saved in the place you want to open your .DAT files from and remove the file name from the path (obviously).
Code:

Sub DisplayURL
InputBox "Copy URL below to use as your file path","Display URL", ThisComponent.GetURL
End Sub


If you add this big ol macro to your user macros in calc, you can add it as a custom toolbar button. It automatically figures out the range of the file you open and creates a new sheet with the name you give it in the code and copies the data from the file to it. I know I am being a bit brief, but my girlfriend wants me to come to bed...
_________________
Jared
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
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