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

import text file with custom delimiter

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


Joined: 08 Jul 2008
Posts: 6

PostPosted: Tue Jul 08, 2008 3:47 am    Post subject: import text file with custom delimiter Reply with quote

Hi,

I wonder if anyone can help me. I am trying to import a number of text files into OOoBase and can't work out how to achieve it.

The text files are longer that the row limit in Calc so I cannot just paste the data in.
The delimiter is a "|" ie not a comma, semicolon etc so I cannot import them by connecting to them as a data source.

Any ideas, am I missing something obvious?

Thanks in advance,

Matt Gedes


Last edited by matmaxgeds on Thu Sep 18, 2008 7:31 am; edited 2 times in total
Back to top
View user's profile Send private message
Wandering
OOo Enthusiast
OOo Enthusiast


Joined: 18 Oct 2007
Posts: 190

PostPosted: Tue Jul 08, 2008 5:28 am    Post subject: Reply with quote

Presuming you are using Windows.

First, clean up the file using the freeware CSVed program. It is a joy for this kind of work is very fast, and takes almost no space. Just open the file in it, select the comma seperator, and save it. Presto! Then you'll have a file in standard csv format.

Put the file in it's own directory.
Open Base and create a new database. Name it as you chose and create it in the same directory as the csv file. Choose the file type as TEXT.(Important) .

Then when Base creates the file it will automatically link to any and all csv files in the same folder it was created in. Note these are links, not imports. The data remains in the csv file. That's as close as you can get with Base. It is pretty quick, and quite easy once you get the hang of it.

Good luck.
Back to top
View user's profile Send private message
matmaxgeds
General User
General User


Joined: 08 Jul 2008
Posts: 6

PostPosted: Tue Jul 08, 2008 6:32 am    Post subject: Reply with quote

Hi,

Thanks for your super quick response,
Sadly I am not using Windows and I can't get CSVed to work under Wine/Linux
In Windows I can just import text files into Access with a custom delimiter which is the functionality I am trying to achieve with opensource software only.

However, this has prompted me to start reading man pages and I think I should be able to do the replace on the command line eg with: tr 'original delimiter' 'new delimiter' <originalfile >newfile

However, I need to ask if this is the only way of doing it?

I am reluctant to go down the path of changing the delimiter as some of the data already has commas' not as a delimiters but as part of strings in the data.

As far as I can tell OOoBase only accepts "," ":" ";" tab or space as delimiters, all of which are currently in use in my data.

The tip about Base linking to files in the same folder should be useful though.

Thanks again,

Matt
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Jul 08, 2008 8:12 am    Post subject: Reply with quote

Matt:

While not 'fun' reading . . . please check out HSQL documentation on Chapter 6. Text Tables. . .

http://www.hsqldb.org/doc/guide/ch06.html

Specifically . . . you can inform HSQL what the delimiter character is . . . including the "pipe".

HSQL Text File documentation . . . http://www.hsqldb.org/doc/guide/ch06.html#N10F7F wrote:
The default field separator is a comma (,). A different field separator can be specified within the SET TABLE SOURCE statement. For example, to change the field separator for the table mytable to a vertical bar, place the following in the SET TABLE SOURCE statement, for example:

SET TABLE mytable SOURCE "myfile;fs=|"

Since HSQLDB treats CHAR's, VARCHARs, and LONGVARCHARs the same, the ability to assign different separators to the latter two is provided. When a different separator is assigned to a VARCHAR or LONGVARCHAR field, it will terminate any CSV field of that type. For example, if the first field is CHAR, and the second field LONGVARCHAR, and the separator fs has been defined as the pipe (|) and vs as the period (.) then the data in the CSV file for a row will look like:

The SET TABLE command may be issued / executed in Base . . .
  1. From the Menu: Tools -> SQL... -> in the Command to execute box . . . enter the statement as you need it . . . AND . . . as in the example above . . . mytable should be wrapped in double quotes . . . " . . . AND . . . the text table name MUST be entered "exactly" as you have defined it in your database . . . that is . . . CASE - UPPER / lower must be the same. A table "matt" is not the same as "Matt" nor "MATT".
  2. Press the Execute button
  3. Result should say . . . Command successfully executed.
  4. Press the Close button

I hope this helps you to define your TEXT table with a Pipe ( or other ) delimiter. Very Happy

Please be sure to let me / us know if this helps.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
matmaxgeds
General User
General User


Joined: 08 Jul 2008
Posts: 6

PostPosted: Wed Jul 09, 2008 8:11 am    Post subject: Reply with quote

Thanks a lot,

That looks like what I need,
When I get a chance I will do the reading and give it a go as queries etc are not my strong point to say the least.
I'll mark it as solved for now

Thanks again,

Matt
Back to top
View user's profile Send private message
matmaxgeds
General User
General User


Joined: 08 Jul 2008
Posts: 6

PostPosted: Thu Sep 18, 2008 7:30 am    Post subject: reopening Reply with quote

Hi,

Apologies for opening this up again. I need some more help. I tried the suggestion above as follows.

I have a database and a text file with the data in the same folder. The text file is called crsaa06.txt. The seperator is a pipe (|).
I made a new database.
I am not sure how to 'define a table' so I just saved a blank table as 'Table1'

from the MENU > Tools > SQL > command box I entered:

SET TABLE "Table1" SOURCE "crsaa06.txt;fs=|"

In reply I recieve:

1: Table not found in statement [SET TABLE "Table1" SOURCE "crsaa06.txt;fs=|"]

I suspect this is to do with me not being able to define a table properly, either that or not knowing how to correctly specify the locations of files.

Thanks in advance again!

Matt Geddes
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Sep 18, 2008 8:31 am    Post subject: Reply with quote

Matt:

My apologies, since your reply comes over 2 months . . . I will ( or will not ) reply to your request in a like period.

After all, this must be something important to you ( over 2 months for a reply ) so . . . I will be sure ( or not ) to reply accordingly.

Thanks again in advance.

Sliderule
Back to top
View user's profile Send private message
matmaxgeds
General User
General User


Joined: 08 Jul 2008
Posts: 6

PostPosted: Thu Sep 18, 2008 8:38 am    Post subject: Reply with quote

Hi Sliderule,

Not sure if I have offended you? If I have then I'm sorry for that. I got moved to another project for the last two months (or rather the same project but with a different dataset) and having just finished that, am now back to working on this more troublesome one.

Matt Geddes
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Sep 18, 2008 9:15 am    Post subject: Reply with quote

Matt:

You said:
Matt wrote:
Not sure if I have offended you?

I have not idea why you said that, I am not offended, nor, did I say that.

I too, have moved onto another project . . . helping folks . . . where folks follow through to resolve questions and answers.

I hope that helps.

Sliderule
Back to top
View user's profile Send private message
matmaxgeds
General User
General User


Joined: 08 Jul 2008
Posts: 6

PostPosted: Thu Sep 18, 2008 9:21 am    Post subject: Reply with quote

Hi,

Thanks for clearing that up and for your help so far. Hopefully someone else might see this thread and be able to take it further.

Matt
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 Base 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