import linux text files into access database

[selahlynch]

I cannot import a linux text file into my access database using the following code...

    Open spspath For Input As #1
    While Not EOF(1)
        Line Input #1, strline

Can I modify my code to handle linux or windows text files?


And a bit of background:
I have a database where I will be asking multiple users to import a number of text files, some from Windows and some from Linux. Up till now I am not able to import the Linux files. I realize I could insist that users convert their files using something like unix2dos command, but I'd like to come up with a more user friendly solution.

Thanks,
Selah

 

[DCrake]

Ingnorance here but what is the difference between a Linx and a Windows txt file?

Can you supply a copy of a linux file?

 

[selahlynch]

The difference is the line endings.
Windows has CR+LF at the end of every line.
Unix only has LF.

CR = 0x0d
LF = 0x0a

 

[DCrake]

I may be able to help you if you send me a small sample linux file for testing.

 

[selahlynch]

Here are two very small files.... thanks.

View attachment sample_unix.txt

View attachment sample_dos.txt

 

[gemma-the-husky]

I am sure DC will give you a fix - but if line input doesnt work, i suspect you will end up having to read in text a char at a time, until you get your line terminator.

ie developing your own replacement "speciallineinput" function.

 

[DCrake]

Ok, here is a sample function that will convert the incoming unix file to a dos file

Public Function UnixToDos(UnixFileName As String, DosFileName As String)

Dim strUnix As String
Dim strDos As String

Open UnixFileName For Input As #1
Open DosFileName For Output As #2
Dim lf As Integer
Dim strLen As Long

Line Input #1, strUnix
'get the length of the string
strLen = Len(strUnix)
    
    For x = 1 To strLen
        'test for line feed character (Chr(10))
        lf = InStr(strUnix, Chr(10))
        'If found
        If lf > 0 Then
            strDos = Left(strUnix, lf - 1) 'extract everything before it
            Print #2, strDos ' write results to the converted unix to dos file
            strUnix = Mid(strUnix, lf + 1) ' left trim the incoming string
        Else
            Exit For ' no more line feeds so exit loop
        End If
    Next
    'Close both files
    Close #1
    Close #2
    

End Function

Place the function in a standard module.
Pass the full path and the file name to the function for both the unix file you want to convert and the converted file name you want to create.

Example:

Call UnixToDos("C:\Temp\Unix.Txt","C:\Temp\Dos.Txt")

This will read the incoming unix.txt file and create a new file call dos.txt

What it does not do is handle multiple rows in the unix file. You will need to wrap a Do until eof around the for loop

 

[selahlynch]

Thanks a lot, I will try that out.

My one worry though is that many of my files are very large (~30Mb) and import time is an issue. I imagine going through the file twice would make importing twice as long and maybe there will be some memory issues if I try to save the entire file in one string variable??

Do you think there is a way to do this without reading the file twice? I thought I might investigate functions that are intended to handle binary files. Hrmm. Any thoughts appreciated.

 

[gemma-the-husky]

it isnt reading twice

the line input is obviously a (supplied) function that reads a character at a time until it reaches an end of line marker, and then returns the line

if you cant use the internal function to do that (because it doesnt recognise yuor line terminator), you will just have to write your own function to do the same thing - its doing the same thing, but you will have to "roll your own" if you will


not sure how long it will take to load 30Mb though


[edited to improve presentation]

 

[DCrake]

As Dave said it is not reading the file twice at this point. It will however have to pen the converted file to import into Access once the new file has been created.

As far as you timings are concerned, how log have you been pondering this issue? how long would it have taken you alone to come up with a solution? Equate this into the solution provided and see if there is much difference. This is known as opportunity costing.

The only way to see what the implications are is to bench test a file with a known number of lines and multiply it out to give you an estimated time to complete time AKA Microsoft Minutes. Running the conversion locally will also boost performance.

 

[selahlynch]

By reading the file twice I meant reading the file once in order to convert it to dos, and then reading the converted file in order to input it to the database.

Anyway I'm gonna give the converting and then reading a try and I'll report back with how it goes. Thanks for the help.

Selah

 

[gemma-the-husky]

i hadnt seen the conversion utility

but no doubt you could adopt the principle involved to directly read the lines into your program in the same way that you could have done with line input.

 

[selahlynch]

So, I ended up trying a different approach. I was worried about reading my entire text file into one string because the text file is very large.

With some help and inspiration from this link:
http://www.excelforum.com/excel-programming/599280-line-input-for-linux-generated-files.html posted by Leith Ross

I wrote a function to read and return a line of a unix or a dos file. I use this function in place of the "Line Input" statement. I hope maybe it will be helpful for someone else in the future.

Public Function getLine_UnixDos(filenum As Integer) ' as string
    'A function to return a line of text from a Unix OR a Dos text file
    'Selah Lynch - Feb 2010
        
    Dim thischar As String
    Dim thisline As String
        
    While Not EOF(1)
        
        thischar = Input(1, #filenum)
        If thischar = vbLf Then
            lastchar = Right(thisline, 1)
            If lastchar = vbCr Then
                linelen = Len(thisline)
                thisline = Left(thisline, linelen - 1)
            End If
            getLine_UnixDos = thisline
            Exit Function
        
        Else
            thisline = thisline & thischar
        End If
    Wend
    getLine_UnixDos = thisline

End Function

 

[DCrake]

It seams to me that you are reading the input one character at a time. This is even more labour intensive than my suggestion.

I can suggest that with my method if you did not want to create the new dos file you could at that point stick it straight into Access as long as you can identify the target field from the sub string. Therefore there is only one read.