Java and Access

[Sharp]

Hi

I'm trying to convert a file in FASTA format to a text file in TABLE
(delimited by tab) format for MS Access.

EXAMPLE:

from FASTA format:

12345 a description

ATATATATATATATATATAT
ATATATATTATATATATATT

to TABLE format:

12345 ATATATATATATATATATATATATATATTATATATATATT


PROBLEM:

Sometimes the sequence of A's and T's is so long that even in notepad, it
will wordwrap, despite having wordwrap set off. WordPad does a better job
than notepad, and will accept a little more characters per line, but still
in some cases the sequence is too long that it reaches the line maximum and
begins to word wrap. The problem of wrapping is that it becomes difficult to
import the text file into MS Access reliably.


QUESTIONS:

Is there another format I could use that will allow extremely long lines?
I plan to import the sequence as a memo field, which I heard can take many
characters.
I guess another solution is to use JDBC to connect directly to the database
and import it directly to a table, thereby avoiding the need to save it as a
textfile (doesn't work anyway) before I import.

Any advice appreciated.

Cheers
Sharp

 

[Albert D.Kallal]

Hum, don't see why you would want to introduce java in to this mix. (likely
the reason why no one answered this question!!).

Anyway, I would simply write some code in ms-access to grab the data.

A quick google shows that most FASTA data has line breaks (usually 80
chars). Do you want to 'keep' those line breaks in the memo field?

eg:

Cow ATGGCATATCCCAT
CACACGCTAATAAT

Dog

CACACGCTAATAAT
ATGGCATATCCCAT

When you import ">cow", do you wnat to keep the lines breaks in the memo
field at:
ATGGCATATCCCAT
CACACGCTAATAAT

Or, do you want the lines all taken togher, (the above two lines would be
placed on one line)
ATGGCATATCCCAT + CACACGCTAATAAT
we get:

ATGGCATATCCCATCACACGCTAATAAT

It does seem that you want the multiple lines taken into one?

The code do do this would/could look like:

Sub ReadTextFile

Dim strFile As String
Dim intF As Integer
Dim strLineBuf As String
dim strField1 as string
dim strField2 as string
dim rstData as dao.RecordSet

strFile = "c:\my data\MyData.txt"
set rstData = currentdb.OpenRecordSet("myDataTable")

intF = FreeFile()
Open strFile For Input As #intF

WritenewRec = false
Line Input #intF, strLineBuf
Do While EOF(intF) = False

if left(strLineBuf,1) = ">" then
if writeNewRec = True then
rstData.Addnew
rstData!Field1 = strField1
rstData!Field2 = strField2
rstData.update
strField1 = ""
strField2 = ""
' start a new line input
strField1 = mid(strLineBuf,2)
end if
end if

' now process data....
strField2 = ""

Line Input #intF, strLineBuf
do while left(strLineBuf,1) <> ">"
strField2 = strField2 & strLineBuf
Line Input #intF, strLineBuf
loop
loop


rst.Close
set rst = nothing

close

msgbox "done inport"

Now, the above code is just typed as I write this (so, it is rough air
code...but it would be close to what you need).

Do note that the coding language in ms-access is VB6, so. if you got any vb
skills, then the above is quite easy...

 

[Sharp]

Hum, don't see why you would want to introduce java in to this mix. (likely
the reason why no one answered this question!!).

I would like to use JDBC (Java DataBase Conectivity) to connect to MS
Access.
It is relevant, but is obviously not very popular here.

Anyway, I would simply write some code in ms-access to grab the data.

A quick google shows that most FASTA data has line breaks (usually 80
chars). Do you want to 'keep' those line breaks in the memo field?

You gave me an idea! thanks!
Yes, i would like to keep the line breaks.

Thanks for the code, but I don't know VB, and dont have the time to learn
it.
I will stick to Java to simulate the same thing.
Thanks again.

Cheers
Sharp