[AccessD] Import File From UNIX box

It has been my experience that transferring files to and from Unix to 
Windows worlds you almost always have to look at the record terminators in a 
Hex Editor to see what is there verses what should be there. Windows Text 
format should have a CR and a LF character at the end of each record. Unix 
generally has one or the other. Some Windows programs can handle these Unix 
version text files without any problems, but Access seems to be sensitive to 
them. It wants exactly one CR and one LF. Hex 0D and Hex 0A. I have seen the 
FTP process to transfer the files from Unix to Windows add CR/LF's and 
sometimes it would only do that if it had a dot txt file extension. Weird 
stuff. What I have seen is that since there is only the CR (or the LF) The 
Access text file import will see the file as ONE RECORD. One BIG record. 
This will cause it to blow up on big files or just not work right on smaller 
ones. I have used routines similar to the one posted below to "adjust" the 
cr/lf record terminators as needed. The basic logic is to parse fhe file one 
character at a time. If it's not a CR or LF character, you send it to the 
output buffer. If it is a CR or a LF, write the record, adding the CR/LF as 
needed - or NOT because just writing the record to the file may add that for 
you. I haven't done it for a while so I forget exactly if you need to add 
them explicitly or not. I see that this code has that addition commented 
out.  Anyway, here's an example of my routine to straighten out a txt file, 
reading in one file and writing it out to a new file.

Private Sub cmdFixFile_Click()
' On Error GoTo Err_cmdFixFile_Click
    Dim dRec As String
    Dim CharCounter As Long
    Dim RecordsWritten As Double
    Dim dtEndTime As Date
    Dim dtStartTime As Date
    Dim I As Long
    Dim O As Long
    Dim strBL As String
    Dim strImportPath As String
    Dim strOutputPath As String
    Dim strNL As String
    Dim MyChar As String
    Dim MyOutputRec As String
    Dim DisplayCounter As Long
    Dim TotalCharactersRead As Double
    Dim LineFeedSwitch As Boolean
    Dim str0D As String
    Dim str0A As String
    str0D = Chr(13)
    str0A = Chr(10)
    I = FreeFile
    O = FreeFile + 1
    strNL = Chr(13) & Chr(10)

    strImportPath = "D:\aaaa\oracleconv\o200138JewelryOnly.txt"
    strOutputPath = "D:\aaaa\oracleconv\o200138JewelryOnlyCorrected.txt"
    Me!txtMessage = "Commencing Fix Process. "
    Me.Repaint
    dtStartTime = Now()
    Open strImportPath For Input As I
    Open strOutputPath For Output As O
    Do While Not EOF(I)
        MyChar = Input(1, #I)
        If MyChar = Chr(13) Or MyChar = Chr(10) Then
            If LineFeedSwitch = False Then
                MyOutputRec = MyOutputRec ' + strNL
                LineFeedSwitch = True
                Print #O, MyOutputRec
                MyOutputRec = ""
                RecordsWritten = RecordsWritten + 1
                CharCounter = 0
                DisplayCounter = DisplayCounter + 1
            End If
        Else
            MyOutputRec = MyOutputRec + MyChar
            CharCounter = CharCounter + 1
            TotalCharactersRead = TotalCharactersRead + 1
            LineFeedSwitch = False
        End If


'       If CharCounter = 655 Then
'            MyOutputRec = MyOutputRec + strNL
'            Print #O, MyOutputRec
'            MyOutputRec = ""
'            RecordsWritten = RecordsWritten + 1
'            CharCounter = 0
'            DisplayCounter = DisplayCounter + 1
'        End If
        If DisplayCounter = 500 Then
            Me!txtMessage = "Total Characters Read " & TotalCharactersRead & 
strNL & "Total Records Written " & RecordsWritten
            Me.Repaint
            DisplayCounter = 0
        End If
    Loop
    Close #I
    Close #O
    Me!txtMessage = "Total Characters Read " & TotalCharactersRead & strNL & 
"Total Records Written " & RecordsWritten
    Me.Repaint
    MsgBox "All Done - " & RecordsWritten & " Records Written"
Exit_cmdFixFile_Click:
    Exit Sub

Err_cmdFixFile_Click:
    MsgBox Err.Description
    Resume Exit_cmdFixFile_Click
End Sub



Gary Kjos
garykjos at hotmail.com



>From: "Mark A Matte" <markamatte at hotmail.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>Subject: [AccessD] Import File From UNIX box
>Date: Thu, 29 Jul 2004 13:48:01 +0000
>
>Hello All,
>
>I have an A97 db that imports a text file.  The text file was on a Windows 
>box...but now is generated and sent to a UNIX box.  The UNIX version of 
>this text file has a CR or LF character after each record...and access sees 
>this as the end of the file.  Any suggestions in using vba to convert this 
>file(or those characters) back to a Windows format/
>
>Thanks,
>
>Mark A. Matte
>
>_________________________________________________________________
>Overwhelmed by debt? Find out how to ‘Dig Yourself Out of Debt’ from MSN 
>Money. http://special.msn.com/money/0407debt.armx
>
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com