Importing Text File

  • Thread starter Thread starter Shawna
  • Start date Start date
S

Shawna

I thought I had this finished but my client threw me a
curve. If anyone out there can help - I've tried....

Below is what I need to import
01001,PHOS. LINE 2 2A PUMP,00,P2-2A-1087-C,2,316L
52,182.9428,219,CS,01-16-2003,0
1 REVISED DIMS.,02-04,CS,,,
2 MATERIAL,03-06,CS,,,
No PWHT,No Hydro,5153,,,
Priority,,,,,

I have the first two lines importing fine, line again. I
think the blank ,,, starting on the 3rd line is what is
giving me a problem I'm not sure how to handle - how can
I handle using (the following is last two fields picked
up from Line 2)

MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!Date = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!WtLb = Left(MyString, InStr(MyString, ",") - 1)

approach as used with the first two lines of import.
Can anyone help with this I just can't get how to handle
the blank fields. and get to the next line properly. I
have no control how this file looks.

Any help is appreciated!
 
Look at the input statement in help. If your data always has six
fields, then you can do something
like this.

Dim MyField1 as Variant
Dim MyField2 as Variant
Dim MyField3 as Variant
Dim MyField4 as Variant
Dim MyField5 as Variant
Dim MyField6 as Variant

Open "MyFile" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
Input #1, MyField1, MyField2, MyField3, MyField4, MyField5,
MyField6

Do something here to test the value of the fileds and to check to
see what line
you are on.

If IsNull(MyField1) then
do something
else
do something else
EndIf

Loop
Close #1 ' Close file.


Ron
 
Thank you for the advice. Each line alwasy does have 6
fields, but each line belongs to the same record,
different fields of that record.

I saw your suggestion for someone else awhile back using
the idea of

MyString = Mid(MyString, InStr(MyString, ",") + 1)
rst!Date = Left(MyString, InStr(MyString, ",") - 1)

and tried it for my application and it worked great -
like I said until I found out I have to import all of the
lines. (Actually I could skip any of the comment lines
the ones that start with a number in front - I tried to
do this but I couldn't get it looping properly to pick up
the next line right so I had to give up)
 
There has to be something in the data that will tell you what is the
first line of a record
or the last line of a record. If there is , then you can put logic in
the do loop that
will test for it and write the record to a table. Maybe checking the
first field
"01001" to see if it is a valid part number and a length of 5 characters.
If it is not 5 characters in length and not a valid part number, then it
is part of the
current record you are processing.

If you cannot determine what that is, then you may have to request the
client to
change the format of the exported data.

Also, try to see if you can read in 36 fields with 1 input statement.

Input #1, MyField1, MyField2, .... MyField36

Try reading in 1 field at a time and put the data into an array.
As you read each field, test to see if the length is 5 characters, if so,
then test to see if the number is a valid part number. If it is, save
the data in the array
to a table, clear the array, then start saving the data in the array
again until to file another vaild number.

Ron
 
Back
Top