Text file I/O in VBA

[SteveInBeloit]

Hello,
Kind of struggling here. I need to read in a text file that contains prices
from a vendor. It is a formatted text file, so the first line is a heading,
second line spaces, third line heading, fourth line all dashes, then the
good stuff.
Each of the next lines have upc, date, old price, new price...

From reading, I thought this may work. Really hokey how I did the first
four lines, but don't know what to do:

Open "c:\pfms.txt" For Input As #1
Input #1, srtLine
Input #1, strLIne
Input #1, strLIne
Input #1, strLIne

Do While Not EOF(1)
Input #1, str1, str2, str3, str4, str5
Loop
Close #1

I have acouple of problems. When it starts reading the fifth line on, I get
the entire line in each variable. I don't know how to tell it they are
separate fields. It is formatted like a normal report, not csv.
Second, it reads passed the end of file.
Open to how to do this, or better options.

Thanks

 

[John Nurick]

Hi Steve,

If the file is ordinary comma-separated text once you're past the first
four lines, you can use the TrimFileHeader() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm

 

[SteveInBeloit]

Clarification, it is a fixed width file. And I get a new line in each of the
variables with each read.

 

[SteveInBeloit]

John,
Nope, no commas, just looks like a report.
Steve

 

[Sreedhar]

Hi, you can also try the following untested code for reading a fixed width
text file and clean up unwanted lines.

Public Sub CleanUpTextFile()
On Error GoTo ErrHandle

Dim f1 As Integer
Dim f2 As Integer
Dim fInput As String
Dim fOutput As String
Dim st As String

fInput = CurrentProject.Path & "\YourTextFile.txt"
fOutput = CurrentProject.Path & "\CleanTextFile.txt"

If Len(Dir(fOutput)) > 0 Then Kill fOutput 'make sure it doesn't exist
already

f1 = FreeFile
Open fInput For Input As #f1
f2 = FreeFile
Open fOutput For Output As #f2

While Not EOF(f1)
Line Input #f1, st
If Left(st, 8) = " " And Mid(st, 9, 1) <> "-" Then 'Here, you
can have your

'criteria
Print #f2, Mid(st, 9, 10) &" " & Mid(st, 20, 1)
End If
Wend

Close #f1
Close #f2

ExitHandle:
Exit Sub
ErrHandle:
ErrorLog "CleanUpTextFile", Err, Error
Resume ExitHandle
End Sub

 

[John Nurick]

In that case, trim the header lines and import it as a fixed-width file.

 

[SteveInBeloit]

the normal commas in the text are killing me. it splits them at the commas
like in $1,333.22.

It must need to know it is a fixed width. I didn't put anything on it to
tell it was should be comma separated, do I have to put something on it to
tell it it is fixed width?

Thanks very much
Steve

 

[SteveInBeloit]

Sreedhar,
What about this code tells it it is fixed width and not break on the commas?
Thanks
Steve

 

[Sreedhar]

Hi,

If you have a text file like this:

TEXT FILE HEADER DATE:25/08/2006
Item1 Item2 Item3 Item4
------- --------- ----------- ----------
abc xyz $1,023.00 Product1
def mno $2,077.00 Product2

etc etc.....

Now, if I understand your question correctly, you want a file like this:

abc xyz $1,023.00 Product1
def mno $2,077.00 Product2

removing all the header info and et al. The code assumes that the data lines
have a unique format somewhere in the line such as the "$" sign in the third
column, which the header or other lines will not have. (There can be other
such 'markers' as well).

To get that, in the code, you write like this:

While Not EOF(f1)
Line Input #f1, st
If Mid(st, 29,1) = "$" Then
Print #f2, Mid(st, 1, 3) &" " & Mid(st, 15, 3) & " " & Mid(st,29,10)
etc etc...
End If
Wend

Those numbers in the Mid function are the position of the characters in the
data line. As you can see, the commas are also handled well into your cleaned
up text file.

This only one possibility. You can also specify different criteria based on
your requirements. e.g. if you only want records containing "Product11" then
you can say

If Mid(st, 43,9) = "Product11"

assuming "P" is 43rd letter in the record.

Note: This is for a fixed-width text file. The example I've given is pulled
from air and doesn't guarantee the results. However, I used such code for my
every day needs of cleaning up text files before importing into Access.

 

[John Nurick]

When I wrote

trim the header lines and import it as a fixed-width file

I meant that you should first use the TrimFileHeader() function to
dispose of the header lines and get you an ordinary fixed-width text
file. Once you have that, you can use DoCmd.TransferText with an import
specification to import the file in the usual way.

If you don't know what that is:
1) Import the file manually (File|Get External Data|Import)
2) When you get to the text import wizard, make all the necessary
settings, click the Advanced button, make more adjustment if needed, and
save the result as an import/export specification.
3) Subsequently, to import the file (or files with the same structure)
under program control, use DoCmd.TransferText and pass the name of the
specification you saved.

 

[SteveInBeloit]

Sreedhar,
Ah, I see, by putting the LINE in front of the Input, that tells it it is
fixed length, and not to break on commas. Thanks.

I have it now so it gets rid of the first header rows, then instead of
writing it to another file, puts the values in a table. When I read in "
1,343.98" I would like to put it an a "money" field in the table. But when
I read it in, it sees it as a string and is not letting me convert it to
money. I have tried a few things, but can't get it to money.

 

[SteveInBeloit]

John,
I am trying to do it the way you described above, but it never gives me an
option to save the specification. On the advanced tab, the save and specs..
button are always disabled?

Steve

 

[SteveInBeloit]

John,
I have gone through the wizard, on the first time,it looks good.
Then in code I am trying:

DoCmd.TransferText , pfms.txt_ImportSpec, tblPfms, "C:\pfms.txt"

I keep getting "object Required".. Help said the name defaults to
filename_ImportSpec, so that is where I got the spec name. I never did have
a chance to save it.

Steve

 

[Sreedhar]

Hi Steve,

To import fixed width text files into Access, you will have to tell Access
the specifications of the import, i.e. Import Specification. You can search
the Access help files how to do it.

To create one, click on "Tables" tab on database window, click "New", select
"Import Table", and from the dialog, select your Text file. You have to
define the field names, field lengths and data types (e.g. Currency type for
your "Money" fields). Then, you are ready to save the Spec. Click on the
"Advanced" button. A new dialog opens with the specifications you have just
set. Click "Save As" to save it with a name of your choice.

Then, you can use this spec in the code you write to import text files. You
do it by the "TransferText" method of the "DoCmd" object.

The syntax is something like this:

DoCmd.TransferText acImportFixed, "Your Spec", "YourTableName", _
"CompletePath\YourTextFile.txt"

Hope that helps.

 

[SteveInBeloit]

Sreedhar,
I have tried this several times, the Save button is disabled.

I am using an ADP, not am MDB. Do you think that is the problem?

STeve

 

[Sreedhar]

Hi Steve,

Sorry for assuming that you were using an mdb file. I don't know much about
Access Projects (adp). You can try posting this problem as a fresh thread in
this NewsGroup. I'm sure somebody will throw light on this so that we both
can get to know the facts.

 

[SteveInBeloit]

Thanks for trying to help.

Until I found out about the adp, I think I will continue with what you had
said first. I now have it working with the LINE Input #1 strLine

Then I use the mid() to pull the fields out, I just need to figure out how
to get the 1,233.33 into a currency column. It still thinks it is a varchar.
I tried some converts, isn't working yet though.

Steve

 

[John Nurick]

Hi Steve,

Sorry for the slow response: I've been away for a couple of days.

I didn't realise you were using an ADP; that's why the Save and
Specifications buttons are disabled. You may find the "TSI ImpExp Spec
Tool" at www.trigeminal.com useful; otherwise, you can still import
fixed-width files by using a schema.ini file.