Select and Import delimited data file (ERA)

[Troutwater]

I have begun to chase my tail on this one, so help me out.

I need to summarize some data from multiple files but I can't seem to have the user select the file and import it.

It's a ERA(electronic remittance advice) 835 file that will import with the "Import Text File" routine. I've added the "GetOpenFileName" to it but apparently have not appropriately adjusted my "QueryTable.Add" routine.

I removed the .Refresh Backgroundquery:=False line as this was causing an error and this not SQL data.

The desired files have names like this: C123456.835.EDIPROCESSERPROCEEDED

My simpleton code reads:

Sub IMPORT_835()
' IMPORT_835 Macro
Dim InputFile As Variant

InputFile = Application.GetOpenFilename(Title:="Choose your file", _
FileFilter:="All Files (*.*), *.*")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Inputfile", _
Destination:=Range("$B$2"))

.Name = InputFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "*"
.TextFileColumnDataTypes = Array(2, 2, 2, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
.TextFileTrailingMinusNumbers = True

End With
End Sub

NO DATA IS IMPORTED, WHY?

 

[Andrew Poulsom]

The line also applies to text files, so it shouldn't cause a problem. Can you post a sample of the data in the file that you are importing?

 

[Troutwater]

It looks like this:

CAS*CO*45*21.05~AMT*B6*26.21~SVC*HC>99213>25*112.69*59.75*0510*1~

with ~ being a end of line indicator
and * being a delimitor

 

[Andrew Poulsom]

I had no problem running a macro to add a query table for that. Why do you have?

.TextFileColumnDataTypes = Array(2, 2, 2, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)

The macro recorder gave me:

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

 

[Troutwater]

Do you think this problem has something to do with the end of line?
The file has the ~ for a "end of line" indicator but that may not be recognized. When I view the file I have to replace ~ with a ~^p to force a linefeed.

Plus each line do not necessarily have the same number of fields maybe causing unseen problems.

I thinking I need to approach this with a difference software because there are several more complicating issues to associate the parent to child records within the data.

Thanks for helping me.

 

[krishnaoptif]

Hi Experts, I have seen this post code but it did not help me in my file..

Can you download my text file and test.. and update code and share...
https://drive.google.com/file/d/0Bx8Ry12AXZ87OVRLY0FKRXNydkU/edit?usp=sharing
File format is Electronic Remittance Advice (835) - ER

I have converted and Columns and format should be in equals of below file.
https://drive.google.com/file/d/0Bx8Ry12AXZ87ZUtfUVRENUpRdTg/edit?usp=sharing

This is really really urgent for me and it is very important.

Regards,

 

[Andrew Poulsom]

Your links are asking me to sign in.

 

[krishnaoptif]

Your links are asking me to sign in.

I have given public rights now.. pls download now...

https://drive.google.com/file/d/0Bx8Ry12AXZ87OVRLY0FKRXNydkU/edit?usp=sharing
https://drive.google.com/file/d/0Bx8Ry12AXZ87ZUtfUVRENUpRdTg/edit?usp=sharing

 

[Andrew Poulsom]

Those files look completely different to me.

 

[krishnaoptif]

you forget about excel format.. Please see only text file which will be convert into excel...
File format is Electronic Remittance Advice (835) - ER..

so please help on this...

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    Dim FileName As Variant
    FileName = Application.GetOpenFilename(Title:="Choose your file", FileFilter:="Text Files (*.txt), *.txt")
    If FileName = False Then Exit Sub
    Workbooks.OpenText FileName:=FileName, Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
        Space:=False, Other:=True, OtherChar:="*"
    Cells.Replace What:="~~", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.EntireColumn.AutoFit
End Sub