unix .txt in access vba (CR LF)

[dshibaev]

Hey guys,

Having some problem in processing plan ascii files in access generated by a unix system. The problem is that (ref: code below) when I try to parse one line at a time, it recognizes the whole file as 1 line.
I remeber that there is diference between how two systems format the endline statement, one with CarriageReturn-LineFeed and the other with only one of them. I tryed substituting these characters with a Replace() statement, but without any luck.

Any help would great

Sub ProcessReportFiles()

Dim inFile As Integer
Dim outFile As Integer
Dim txtLineIn As String
Dim txtLineOut As String

inFile = FreeFile
Open "C:\test.txt" For Input As inFile
outFile = FreeFile
Open "C:\result.txt" For Output As outFile
    
While Not EOF(inFile)
   ' Read line into variable.
   Line Input #inFile, txtLineIn
   'do something with the line
   txtLineOut = DoReplacements(txtLineIn)
   ' put line into an output file
   Print #outFile, txtLineOut
Wend

'tidy up
Close #inFile
Close #outFile

End Sub

thanks

 

[JBinQLD]

G'day fella

That code looks kinda familiar!

I've had a similar problem in past and i think i tried to get around it using the Replace function

That worked then i came across a diff format.

The other format is of course "Fixed width" If that's what you're dealing with then introduce another loop using the mid function to grab fixed size chunks.

Hope this helps,

JB

 

[PHV]

Replace this:
txtLineOut = DoReplacements(txtLineIn)
with this:
txtLineOut = Replace(txtLineIn, vbLf, vbCrLf)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dshibaev]

Excelent! I am becoming a fan of this forum and hope to be able to contribute sometime soon.

JBinQLD, in fact you are right I used the code you suggested in my last thread for it working quite nicely for what is required

PHV, your sollution worked exactly right, I just change the place where it does the substitution, so instead of

txtLineOut = Replace(txtLineIn, vbLf, vbCrLf)

I actually made the CR-LF replacement in a little higher in the code as

Open "C:\Documents and Settings\Shibaev\Desktop\AFS_DB\data\zafs_ascii_v1.txt" For Input As inFile

...Replaced with...

Open "C:\Documents and Settings\Shibaev\Desktop\AFS_DB\data\zafs_ascii_v1.txt" For Input As inFile
inFile = Replace(inFile, vbLf, vbCrLf)

thanks for your help, it was very useful and timely

 

[JBinQLD]

This is odd! Infile is an integer so how can the replace function work on it?!

 

[dshibaev]

yeah you are right, as at first I had all the databases created I did not notice any difference and thought that the code processed it correctly, but I was wrong. In a few minutes I shall post a workaround that I actually got to work

 

[dshibaev]

here goes the final solution:

inFile = FreeFile
Open "C:\Documents and Settings\Shibaev\Desktop\AFS_DB\data\zafs_ascii_v1.txt" For Input As inFile
lngChars = LOF(inFile)
strImport = Input(lngChars, inFile)
strImport = Replace(strImport, vbLf, vbCrLf)
Close #inFile
outFile = FreeFile
Open "C:\Documents and Settings\Shibaev\Desktop\AFS_DB\data\processed_v1.txt" For Output As outFile
Print #outFile, strImport
Close #outFile

inFile = FreeFile
Open "C:\Documents and Settings\Shibaev\Desktop\AFS_DB\data\processed_v1.txt" For Input As inFile

While Not EOF(inFile)
   Line Input #inFile, txtLineIn

...

I suppose this looks quite ugly and inefficient, but it actually works. I would be glad to improve my code, so post away

 

[JBinQLD]

Looks fine to me mate, rather than resaving the file and reopening it you could dump it straight into a string array and process it from there but that's a petty criticism!

The only thing i would say is learn early to write "nice" code. Don't use hard coded values, declare variables using Hungarian notation (int, str, etc) or similar, indent properly and comment your code. In 6 months when you have to revisit this for some reason you'll thank me i promise. You wont have to read every line!

Have a great weekend,
JB

 

[CautionMP]

dshibaev,
A little late but...

ADO will deal with Cr, Lf, or CrLf. Here are two different approaches.

An ADO Stream will let you specify the line delimiter in a text file.

Sub ProcessReportFilesADOStream()
  Dim strmIn As Object 'ADODB.Stream
  Dim strBuffer As String
  
  Set strmIn = CreateObject("ADODB.Stream") 'New ADODB.Stream
  With strmIn
    .Charset = "ascii"
    .Type = 2 'adTypeText
    .LineSeparator = 10 'adLF
    .Open
    .LoadFromFile "C:\result.txt"
  End With
  
  Do While Not strmIn.EOS
    strBuffer = strmIn.ReadText(-2) 'adReadLine
    '... do your thing here
  Loop
  
  strmIn.Close
  Set strmIn = Nothing
End Sub

You can also use a Recordset. The recordset will automatically create a new record for Cr, Lf, and CrLf so there is no need to specify.

Sub ProcessReportFilesADORs()
  Dim conIn As Object 'ADODB.Connection
  Dim rstIn As Object 'ADODB.Recordset
  Dim strBuffer As String
  
  Set conIn = CreateObject("ADODB.Connection") 'New ADODB.Connection
  With conIn
    .ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "Dbq=c:\;" & _
    "Extensions=asc,csv,tab,txt;"
    .Open
  End With
  Set rstIn = CreateObject("ADODB.Recordset") 'New ADODB.Recordset
  rstIn.Open "SELECT * FROM result#txt;", conIn
  
  While Not rstIn.EOF
    'GetString will automatically move next
    strBuffer = rstIn.GetString(, 1, ",")
    '... do your thing here
  Wend
  
  
  rstIn.Close
  Set rstIn = Nothing
  conIn.Close
  Set conIn = Nothing
End Sub

Hope this helps,
CMP


[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

You should also be able to do it with the FSO

Sub ProcessReportFilesFSO()
  Dim objFSO As Object 'Scripting.FileSystemObject
  Dim objTS As Object 'Scripting.TextStream
  Dim strBuffer As String
  
  Set objFSO = CreateObject("Scripting.FileSystemObject") 'New Scripting.FileSystemObject
  Set objTS = objFSO.OpenTextFile("C:\resultlf.txt", 1) 'ForReading
  
  While Not objTS.AtEndOfStream
    strBuffer = objTS.ReadLine
    '...do your thing here
  Wend
  
  objTS.Close
  Set objTS = Nothing
  Set objFSO = Nothing
End Sub

 

[dshibaev]

Wow, thanks you all, I definitely was not waiting for so many constructive, alternative answers.

I am quite accustomed to programming in matlab, so all this story with objects kind of scared me at first, but as I see now it's quite an efficient alternative.

Well thanks again.
I'll be around