<div>
I think we're missing something. 
 
Standard text file records end with CrLf, not a comma. &nbsp;Commas do cause a problem in .csv files since the &quot;c&quot; in .csv = COMMA and therefore, commas have a structural meaning. &nbsp;In a .csv file, any field that contains a comma MUST be enclosed in double quotes.
</div>

<div>
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.
</div>

<div>
That is different from your original statement. &nbsp;Once you read the input record into a variable, then use the Replace() function to replace &quot;,&quot; with &quot;&quot;. &nbsp;the &quot;&quot;&nbsp;is a ZLS (Zero length string). &nbsp;I doubt that you actually want to embed extra spaces in the string but if you do, then instead of &quot;&quot;, use &quot;&nbsp;&quot;. &nbsp;I'm not sure if you can see visually the difference but &quot;&quot;&nbsp;= two double quotes with no character between them and &quot;&nbsp;&quot; is two double quotes with a space between them.
</div>

<div>
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. &nbsp;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?
</div>

<div>
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 
&nbsp; &nbsp; Input #1, w1 
&nbsp; &nbsp; If Left(w1, 3) &lt;&gt;&nbsp;&quot;ISA&quot; Then &nbsp;'check for customer receivr commID 
&nbsp; &nbsp; &nbsp; &nbsp;DoCmd.Beep: MsgBox wFile &amp;&nbsp;&quot;, WRONG 940 DOCUMENT!&quot;, vbCritical: GoTo EdiCrt940_exit 
&nbsp; &nbsp; End If 
&nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; If InStr(w1, &quot;~&quot;) &gt;&nbsp;0 Then 
&nbsp; &nbsp; &nbsp; &nbsp;w1 = Replace(w1, &quot;,&quot;, &quot;&nbsp;&quot;) 
&nbsp; &nbsp; &nbsp; &nbsp;w1 = Replace(w1, &quot;~&quot;, vbCrLf) 
&nbsp; &nbsp; &nbsp; &nbsp;Close #1 
&nbsp; &nbsp; &nbsp; &nbsp;Open wFile For Output As #1 
&nbsp; &nbsp; &nbsp; &nbsp;Print #1, w1 
&nbsp; &nbsp; &nbsp; &nbsp;Close #1 
&nbsp; &nbsp; End If
</div>

<div>
Dale, 
your assumptions are correct and also to use the Line Input. 
I was thinking about it but you get the credit. 
Thanks
</div>

<div>
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. &nbsp;Is that really what you want to do?
</div>

<div>
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 
 
BEFORE, 
ISA*00* &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;*00* &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;*12*PROMLA &nbsp; &nbsp; &nbsp; &nbsp; *01*364430512 &nbsp; &nbsp; &nbsp;*171222*1312*U*00401*000000113*0*T*&gt;~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 
 
AFTER, 
ISA*00* &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;*00* &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;*12*PROMLA &nbsp; &nbsp; &nbsp; &nbsp; *01*364430512 &nbsp; &nbsp; &nbsp;*171222*1312*U*00401*000000113*0*T*&gt; 
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
</div>

<div>
OK, that makes sense for an EDI file format. &nbsp;Just wanted to be sure.
</div>

<div>
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. &nbsp;This is how I imported the details for an 835 and assembled what I needed into a table record. &nbsp;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. 

<pre><code class="code-100 nolinks" id="code-20-42415699-1">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(&quot;tbl835BlobImport&quot;)
 Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)
 Set td835 = db.TableDefs(&quot;tbl835Raw&quot;)
 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, &quot;~&quot;) '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
 DoEvents
 frm.txtTranCount = LineCount
 frm.Repaint
 End If
 If sRecBlob &amp; &quot;&quot; = &quot;&quot; Then
 Else
 sTranCode = Left(sRecBlob, InStr(sRecBlob, &quot;*&quot;) - 1)
 aryColumns = Split(sRecBlob, &quot;*&quot;)
 'add record to table
 rs.AddNew
 ''rs!ImportBatchID = frm!txtImportBatchID
 If sTranCode = &quot;NM1&quot; Then
 If aryColumns(1) = &quot;QC&quot; Then
 saveEMS = aryColumns(9)
 End If
 End If
 If sTranCode = &quot;CLP&quot; 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 = &quot;CLP&quot; Or sTranCode = &quot;PLB&quot; Or sTranCode = &quot;SE&quot; Or sTranCode = &quot;GE&quot; Or sTranCode = &quot;IEA&quot; Then 'don't populate EMS for these codes
 Else
 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
 rs.Update
 RecCount = RecCount + 1
 ''''' save 835 rec
 Select Case sTranCode
 Case &quot;CLP&quot;
 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) &gt;= 5 Then 'Access
 HoldTransmittalNum = Left(aryColumns(1), 6)
 HoldRecNum = Mid(aryColumns(1), 7)
 Else
 HoldTransmittalNum = Left(aryColumns(1), 5) 'FoxPro
 HoldRecNum = Mid(aryColumns(1), 6)
 End If
 
 HoldBilledAmt = aryColumns(3)
 HoldPaidAmt = aryColumns(4)
 HoldCoPay = IIf(aryColumns(5) &amp; &quot;&quot; = &quot;&quot;, Null, aryColumns(5))
 Case &quot;NM1&quot;
 If aryColumns(1) = &quot;QC&quot; Then
 HoldEMS = aryColumns(9)
 End If
 Case &quot;DTM&quot;
 Select Case aryColumns(1)
 Case &quot;232&quot;
 HoldFromDT = CDate(Left(aryColumns(2), 4) &amp; &quot;/&quot; &amp; Mid(aryColumns(2), 5, 2) &amp; &quot;/&quot; &amp; Right(aryColumns(2), 2))
 Case &quot;233&quot;
 HoldToDT = CDate(Left(aryColumns(2), 4) &amp; &quot;/&quot; &amp; Mid(aryColumns(2), 5, 2) &amp; &quot;/&quot; &amp; Right(aryColumns(2), 2))
 End Select
 Case &quot;SVC&quot;
 aryPCode = Split(aryColumns(1), &quot;:&quot;) 'this field has multiple parts separated by colons. The second one is pcode
 HoldpCode = aryPCode(1)
 HoldUnits = aryColumns(5)
 Case &quot;CAS&quot;
 HoldErrGroup = aryColumns(1)
 HoldErrCode = aryColumns(2)
 End Select
 End If
 Next i
 Loop
 
 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 &quot;Import Complete&quot;, vbOKOnly
 
 'close files and release
 rs.Close
 Set rs = Nothing
 Close FileNum
 Debug.Print RecCount
 frm.txtTranCount = TranCount
 frm.txtLineCount = LineCount
 frm.Repaint
Exit_Proc:
 On Error GoTo 0
 Exit Function

Write835:
 TranCount = TranCount + 1
 If HoldRecNum &amp; &quot;&quot; = &quot;&quot; Then
 HoldRecNum = &quot;000000&quot;
 End If
 If HoldToDT &gt;= #12/1/2014# Then 'ignore older dates. They are handled in FoxPro
 rs835.AddNew
 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) &lt; 14 Then
 FoxProFlg = True
 Else
 FoxProFlg = False
 End If
 rs835!FoxPro = FoxProFlg
 rs835.Update
 End If
 Return
 
Clear835:
 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
 Return
 
Err_Proc:

 Select Case Err.Number
 Case Else
 MsgBox &quot;Error &quot; &amp; Err.Number &amp; &quot; (&quot; &amp; Err.Description &amp; &quot;) in procedure Import835 of Module modEDI&quot;
 End Select
 Resume Exit_Proc
 Resume
End Function
</code></pre>
</div>

<div>
Pat, 
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.
</div>

<div>
Processing EDI records is challenging. &nbsp;I just wanted to give you another idea.
</div>

<div>
<div class="content wysiwyg-content">
Hello All, 
I'm reading in code a text file with Input #1, TextLine 
but if there is a comma &quot;,&quot; 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? 
Thanks
</div>
</div>

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?
Thanks

Reading Text file in Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.