Reading Text file in Access

Hello All,
I'm reading in code a text file with Input #1, TextLine
but if there is a comma "," in the file the system will handle it as the end of file character and ignore the rest of the file.
How can i ignore the commas?
Who is Participating?
Dale FyeConnect With a Mentor Commented:
try using Line Input, assuming W1 is defined as a string variable.

    Line Input #1, W1

I assume, by the way your code is written, that the text file only contains a single line of data with tilda (~) where your record breaks should be.
I think we're missing something.

Standard text file records end with CrLf, not a comma.  Commas do cause a problem in .csv files since the "c" in .csv = COMMA and therefore, commas have a structural meaning.  In a .csv file, any field that contains a comma MUST be enclosed in double quotes.
frimyAuthor Commented:
This is a .Txt file the data dosnt have any quotes i.e. *BEALL, S DEPT STORE*
I want to ignore the commas or replace it with space.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

That is different from your original statement.  Once you read the input record into a variable, then use the Replace() function to replace "," with "".  the "" is a ZLS (Zero length string).  I doubt that you actually want to embed extra spaces in the string but if you do, then instead of "", use " ".  I'm not sure if you can see visually the difference but "" = two double quotes with no character between them and " " is two double quotes with a space between them.
Dale FyeCommented:
reading a line of text using the Input #1 synatx should not terminate lines at commas; it should read an entire line at a time.  What are you going to do with the line of text after you have replaced the commas?

Can you post the code you are actually using to read the file?
frimyAuthor Commented:
the following is my code,
the problem is that it stops reading by the first comma (it reads it as EOF)

Open wFile For Input As #1
    Input #1, w1
    If Left(w1, 3) <> "ISA" Then  'check for customer receivr commID
       DoCmd.Beep: MsgBox wFile & ", WRONG 940 DOCUMENT!", vbCritical: GoTo EdiCrt940_exit
    End If
    If InStr(w1, "~") > 0 Then
       w1 = Replace(w1, ",", " ")
       w1 = Replace(w1, "~", vbCrLf)
       Close #1
       Open wFile For Output As #1
       Print #1, w1
       Close #1
    End If
frimyAuthor Commented:
your assumptions are correct and also to use the Line Input.
I was thinking about it but you get the credit.
Dale FyeCommented:
good luck with your project,

But I need to ask again, what are you going to do with this file once you have replaced all of the commas and added CRLF characters?

When you remove the commas and add the CRLF, you are basically parsing the string into multiple single field records.  Is that really what you want to do?
frimyAuthor Commented:
This is how the file looks, before and after.
once I remove the commas and add the CRLF, I open the file again and I use the line input to loop thru every line separately.
Thanks again

ISA*00*          *00*          *12*PROMLA         *01*364430512      *171222*1312*U*00401*000000113*0*T*>~GS*OW*PROMLA*364430512*20171222*1312*113*X*004010~ST*940*0001~W05*N*64453*0681481~N1*ST*BEALLS DEPT STORE #050*92*050~N3*1806 38TH AVENUE EAST~N4*BRADENTON*FL*34208*US~N1*SF*PROMPT WAREHOUSING LA*91*PMTLA~N3*15912 VALLEY VIEW AVENUE~N4*LA MIRADA*CA*90638*US~N1*Z7*BEALLS DEPT STORE #050*92*050~N3*1806 38TH AVENUE EAST~N4*BRADENTON*FL*34208*US~N1*BY*BEALLS DEPT STORE #050*92*050~N3*1806 38TH AVENUE

ISA*00*          *00*          *12*PROMLA         *01*364430512      *171222*1312*U*00401*000000113*0*T*>
N1*Z7*BEALLS DEPT STORE #050*92*050
Dale FyeCommented:
OK, that makes sense for an EDI file format.  Just wanted to be sure.
I'm not saying that you should use the same technique that I used but It turns out that you may not need all the data and so you might want to pick and choose.  This is how I imported the details for an 835 and assembled what I needed into a table record.  I had an additional challenge since my input file was created on Unix and so rather than having CrLf as record separators, the whole file needed to be read as a blob and then parsed into individual records.
Public Function Import835(frm As Form) As Boolean
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim td835 As DAO.TableDef
Dim rs835 As DAO.Recordset
Dim str As String
Dim FileNum As Long
Dim RecCount As Long
Dim ary1 As Variant
Dim aryColumns As Variant
Dim aryPCode As Variant
Dim sTranCode As String
Dim sRecBlob As Variant
Dim i As Long
Dim i2 As Long
Dim strFullFileName As String
Dim saveEMS As Variant
Dim saveSendID As Variant
Dim saveReceiveID As Variant

Dim HoldReceivedID As Variant
Dim HoldTransmittalNum As Variant
Dim HoldRecNum As Variant
Dim HoldErrGroup As Variant
Dim HoldErrCode As Variant
Dim HoldEMS As Variant
Dim HoldFromDT As Variant
Dim HoldToDT As Variant
Dim HoldpCode As Variant
Dim HoldUnits As Variant
Dim HoldBilledAmt As Variant
Dim HoldPaidAmt As Variant
Dim HoldCoPay As Variant
Dim HoldRec As Boolean
Dim HoldInvNum As Variant
Dim FoxProFlg As Integer

Dim TranCount As Long
Dim LineCount As Long

    On Error GoTo Err_Proc

    Set db = CurrentDb
    Set td = db.TableDefs("tbl835BlobImport")
    Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    Set td835 = db.TableDefs("tbl835Raw")
    Set rs835 = td835.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    'Open FileNum For Input As #1
    strFullFileName = frm.txtFileName
    FileNum = FreeFile
    Close FileNum
    Open strFullFileName For Input As FileNum
    HoldRec = False
    TranCount = 0
    LineCount = 0
    Do Until EOF(1)                         'import to tbl835BlobImport
        Line Input #FileNum, str            'because records are not correctly delimited, entire file is read as a blob
        ary1 = Split(str, "~")              'split blob into records
        For i = 0 To UBound(ary1)           'for each record in blob
            sRecBlob = ary1(i)
            LineCount = LineCount + 1
            If LineCount Mod 500 = 0 Then
                frm.txtTranCount = LineCount
            End If
            If sRecBlob & "" = "" Then
                sTranCode = Left(sRecBlob, InStr(sRecBlob, "*") - 1)
                aryColumns = Split(sRecBlob, "*")
            'add record to  table
                        ''rs!ImportBatchID = frm!txtImportBatchID
                        If sTranCode = "NM1" Then
                            If aryColumns(1) = "QC" Then
                                saveEMS = aryColumns(9)
                            End If
                        End If
                        If sTranCode = "CLP" Then
                            saveSendID = aryColumns(1)
                            saveReceiveID = aryColumns(7)
                            rs!SendID = saveSendID
                            rs!ReceiveID = saveReceiveID
                        End If
                        rs!TranCode = sTranCode
                        'can't populate EMS on CLP because we don't get the correct EMS until after writing the CLP record.  Update later with query.
                        If sTranCode = "CLP" Or sTranCode = "PLB" Or sTranCode = "SE" Or sTranCode = "GE" Or sTranCode = "IEA" Then  'don't populate EMS for these codes
                            rs!ems = saveEMS
                            rs!SendID = saveSendID
                            rs!ReceiveID = saveReceiveID
                        End If
                        For i2 = 1 To UBound(aryColumns)
                            rs.Fields(i2 + 4) = aryColumns(i2)
                        Next i2
                        rs!ImportBatchID = frm!cboImportBatchID
                    RecCount = RecCount + 1
                    ''''' save 835 rec
                    Select Case sTranCode
                        Case "CLP"
                            If HoldRec = True Then
                                GoSub Write835
                            End If
                            GoSub Clear835
                            HoldRec = True
                            HoldReceivedID = aryColumns(7)
                            HoldInvNum = aryColumns(1)
                            If Mid(aryColumns(1), 5, 1) = 0 Or Mid(aryColumns(1), 5, 1) >= 5 Then 'Access
                                HoldTransmittalNum = Left(aryColumns(1), 6)
                                HoldRecNum = Mid(aryColumns(1), 7)
                                HoldTransmittalNum = Left(aryColumns(1), 5)         'FoxPro
                                HoldRecNum = Mid(aryColumns(1), 6)
                            End If
                            HoldBilledAmt = aryColumns(3)
                            HoldPaidAmt = aryColumns(4)
                            HoldCoPay = IIf(aryColumns(5) & "" = "", Null, aryColumns(5))
                        Case "NM1"
                            If aryColumns(1) = "QC" Then
                                HoldEMS = aryColumns(9)
                            End If
                        Case "DTM"
                            Select Case aryColumns(1)
                                Case "232"
                                    HoldFromDT = CDate(Left(aryColumns(2), 4) & "/" & Mid(aryColumns(2), 5, 2) & "/" & Right(aryColumns(2), 2))
                                Case "233"
                                    HoldToDT = CDate(Left(aryColumns(2), 4) & "/" & Mid(aryColumns(2), 5, 2) & "/" & Right(aryColumns(2), 2))
                            End Select
                        Case "SVC"
                            aryPCode = Split(aryColumns(1), ":")        'this field has multiple parts separated by colons.  The second one is pcode
                            HoldpCode = aryPCode(1)
                            HoldUnits = aryColumns(5)
                        Case "CAS"
                            HoldErrGroup = aryColumns(1)
                            HoldErrCode = aryColumns(2)
                    End Select
                End If
        Next i
    If HoldRec = True Then
        GoSub Write835          'write last record
    End If
    Set qd = db.QueryDefs!q835UpdateCLPwithEMS  'populate EMS on CLP records
    qd.Execute (dbSeeChanges)
    ''MsgBox "Import Complete", vbOKOnly
    'close files and release
    Set rs = Nothing
    Close FileNum
    Debug.Print RecCount
    frm.txtTranCount = TranCount
    frm.txtLineCount = LineCount
    On Error GoTo 0
    Exit Function

    TranCount = TranCount + 1
    If HoldRecNum & "" = "" Then
        HoldRecNum = "000000"
    End If
    If HoldToDT >= #12/1/2014# Then     'ignore older dates.  They are handled in FoxPro
            rs835!BatchID = frm.cboImportBatchID 'frm!txtImportBatchID
            rs835!ICN = HoldReceivedID
            rs835!TransmittalNum = HoldTransmittalNum
            rs835!RecNum = Left(HoldRecNum, 6)
            rs835!ResubmitCD = Right(HoldRecNum, 2)
            rs835!ErrGroup = HoldErrGroup
            rs835!ErrCode = HoldErrCode
            rs835!ems = HoldEMS
            rs835!FromDT = HoldFromDT
            rs835!ToDT = HoldToDT
            rs835!pCode = HoldpCode
            rs835!Units = HoldUnits
            rs835!BillAmt = HoldBilledAmt
            rs835!PaidAmt = HoldPaidAmt
            rs835!copay = HoldCoPay
            rs835!PaidCD = IIf(HoldBilledAmt = HoldPaidAmt, 1, IIf(HoldPaidAmt = 0, 3, 2))
            rs835!InvNum = HoldInvNum
            rs835!RunType = Forms!frmReceive835!txtRunType
            If Len(HoldInvNum) < 14 Then
                FoxProFlg = True
                FoxProFlg = False
            End If
            rs835!FoxPro = FoxProFlg
    End If
    HoldReceivedID = Null
    HoldTransmittalNum = Null
    HoldRecNum = Null
    HoldErrGroup = Null
    HoldErrCode = Null
    HoldEMS = Null
    HoldFromDT = Null
    HoldToDT = Null
    HoldpCode = Null
    HoldUnits = Null
    HoldBilledAmt = Null
    HoldPaidAmt = Null
    HoldCoPay = Null

    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import835 of Module modEDI"
    End Select
    Resume Exit_Proc
End Function

Open in new window

frimyAuthor Commented:
you code does indeed look challenging.
I just don't have time to change my code but I see I could also use the split command to parse the segment.
Thanks a lot.
Processing EDI records is challenging.  I just wanted to give you another idea.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.