import text file with custom delimiter

[matmaxgeds]

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

[Wandering]

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.

[matmaxgeds]

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

[Sliderule]

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".

[matmaxgeds]

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

[matmaxgeds]

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

[Sliderule]

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

[matmaxgeds]

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

[Sliderule]

Matt:

You said:

[matmaxgeds]

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