import from text file

  • Thread starter Thread starter shawna
  • Start date Start date
S

shawna

I need to import information from a text file but it
isn't deliminated nicely. i.e. a sales order
The file consists of name and address info, but also the
parts info. Is there a different way to have access find
the right information for the right field/table besides
the standard one row = one record in the deliminated
fomat? Any help is appreciated!
 
Hi Shawna,

Access's built-in text file import system can't handle files like this.
Instead, you have to use VBA to read the file line by line, parse each
line and put the data into the appropriate tables and fields. If you
need more help, post back here with more information, including a sample
of the data.
 
Here is a skeleton for how to get started:

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub
 
Thank you for your reply.

I thought that VB code would be involved. I am
attachining a sample ofthe file below.
The top portion would need to go to one table and the
bottom a parts table (some of it I don't even want
imported if that is possible to parse as well.)

"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 "

************DON'T WANT TO IMPORT'
0
2
""
""
0
7

**********START OF PARTS INFORMATION
2,"16BAP0****S"
6,"15BAP0****S"
1,"16BRP0"
1,"16BSP0"
1,"16BXP0"
3,"15BRP0"
6,"15BSP0"
"1",279.4,"P16PSIBE","GxB",""
"2",685.8,"P15PSIBE","BxB",""
"3",395.3,"P15PSIBE","BxB",""
"4",481,"P15PSIBE","BxB",""
"5",2,"L15PCB*P1*","",""
"6",1,"E15PSIBW9015","",""
"7",1,"T16PSIBU15B","",""
"8",1,"R16PSIBQ15BBB","",""
"9",2,"A15PSIBC","",""
"END" ************************INDICATES END OF THIS
RECORD

"01000","PHOS. LINE 2 2A PUMP","00","P2-2A-1085-
B",3,"316L"
76,157.9179,173,"CS","01-16-2003",0
"1 ADDED PRE-GROVED 8IN X 6IN PUP","01-21","CS"

Thanks again for your help.

Shawna

-----Original Message-----
Hi Shawna,

Access's built-in text file import system can't handle files like this.
Instead, you have to use VBA to read the file line by line, parse each
line and put the data into the appropriate tables and fields. If you
need more help, post back here with more information, including a sample
of the data.

I need to import information from a text file but it
isn't deliminated nicely. i.e. a sales order
The file consists of name and address info, but also the
parts info. Is there a different way to have access find
the right information for the right field/table besides
the standard one row = one record in the deliminated
fomat? Any help is appreciated!
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Thank you for your reply.

I have included a sample of the information in a reply
(as well as a new post) for more information.

Shawna
 
Hi Shawna,

Ouch! On the basis of that sample, it's possible but far from simple.

You'll need to possess, acquire or rent good programming skills and
probably some knowledge of regular expressions, which are the simplest
way of programming a computer to distinguish between (e.g.) lines that
look like this
"1 REVISED DIMS.","02-04","CS"
and lines that look like this
"No PWHT","No Hydro","5153"
and so on.

It might be simpler to get the data sent to you in a different and more
easily parsed format. Are you in a position to influence that?


Thank you for your reply.

I thought that VB code would be involved. I am
attachining a sample ofthe file below.
The top portion would need to go to one table and the
bottom a parts table (some of it I don't even want
imported if that is possible to parse as well.)

"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 "

************DON'T WANT TO IMPORT'
0
2
""
""
0
7

**********START OF PARTS INFORMATION
2,"16BAP0****S"
6,"15BAP0****S"
1,"16BRP0"
1,"16BSP0"
1,"16BXP0"
3,"15BRP0"
6,"15BSP0"
"1",279.4,"P16PSIBE","GxB",""
"2",685.8,"P15PSIBE","BxB",""
"3",395.3,"P15PSIBE","BxB",""
"4",481,"P15PSIBE","BxB",""
"5",2,"L15PCB*P1*","",""
"6",1,"E15PSIBW9015","",""
"7",1,"T16PSIBU15B","",""
"8",1,"R16PSIBQ15BBB","",""
"9",2,"A15PSIBC","",""
"END" ************************INDICATES END OF THIS
RECORD

"01000","PHOS. LINE 2 2A PUMP","00","P2-2A-1085-
B",3,"316L"
76,157.9179,173,"CS","01-16-2003",0
"1 ADDED PRE-GROVED 8IN X 6IN PUP","01-21","CS"

Thanks again for your help.

Shawna

-----Original Message-----
Hi Shawna,

Access's built-in text file import system can't handle files like this.
Instead, you have to use VBA to read the file line by line, parse each
line and put the data into the appropriate tables and fields. If you
need more help, post back here with more information, including a sample
of the data.

I need to import information from a text file but it
isn't deliminated nicely. i.e. a sales order
The file consists of name and address info, but also the
parts info. Is there a different way to have access find
the right information for the right field/table besides
the standard one row = one record in the deliminated
fomat? Any help is appreciated!
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
I was hoping there would be an easy way for this import -
I know I don't possess the skills necessary.

Would there be a way to grab the first field from the
first line i.e. "01001" then skip the rest of the lines
until I get to the parts table (outlined in the example)-
I can import the other information using a different
method I was trying to limit the number of imports these
guys will have to do but....

Thanks for your help, very much appreciated.

Shawna
(this is an ongoing import that will need to be done so
I'd like to make end user function easy to perform)

-----Original Message-----
Hi Shawna,

Ouch! On the basis of that sample, it's possible but far from simple.

You'll need to possess, acquire or rent good programming skills and
probably some knowledge of regular expressions, which are the simplest
way of programming a computer to distinguish between (e.g.) lines that
look like this
"1 REVISED DIMS.","02-04","CS"
and lines that look like this
"No PWHT","No Hydro","5153"
and so on.

It might be simpler to get the data sent to you in a different and more
easily parsed format. Are you in a position to influence that?
Thank you for your reply.

I thought that VB code would be involved. I am
attachining a sample ofthe file below.
The top portion would need to go to one table and the
bottom a parts table (some of it I don't even want
imported if that is possible to parse as well.)

"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 "

************DON'T WANT TO IMPORT'
0
2
""
""
0
7

2,"16BAP0****S"
6,"15BAP0****S"
1,"16BRP0"
1,"16BSP0"
1,"16BXP0"
3,"15BRP0"
6,"15BSP0"
**********START OF PARTS INFORMATION
"1",279.4,"P16PSIBE","GxB",""
"2",685.8,"P15PSIBE","BxB",""
"3",395.3,"P15PSIBE","BxB",""
"4",481,"P15PSIBE","BxB",""
"5",2,"L15PCB*P1*","",""
"6",1,"E15PSIBW9015","",""
"7",1,"T16PSIBU15B","",""
"8",1,"R16PSIBQ15BBB","",""
"9",2,"A15PSIBC","",""
"END" ************************INDICATES END OF THIS
RECORD

"01000","PHOS. LINE 2 2A PUMP","00","P2-2A-1085-
B",3,"316L"
76,157.9179,173,"CS","01-16-2003",0
"1 ADDED PRE-GROVED 8IN X 6IN PUP","01-21","CS"

Thanks again for your help.

Shawna

-----Original Message-----
Hi Shawna,

Access's built-in text file import system can't handle files like this.
Instead, you have to use VBA to read the file line by line, parse each
line and put the data into the appropriate tables and fields. If you
need more help, post back here with more information, including a sample
of the data.

On Tue, 18 Nov 2003 10:38:41 -0800, "shawna"

I need to import information from a text file but it
isn't deliminated nicely. i.e. a sales order
The file consists of name and address info, but also the
parts info. Is there a different way to have access find
the right information for the right field/table besides
the standard one row = one record in the deliminated
fomat? Any help is appreciated!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Would there be a way to grab the first field from the
first line i.e. "01001" then skip the rest of the lines
until I get to the parts table (outlined in the example)-
I can import the other information using a different
method I was trying to limit the number of imports these
guys will have to do but....
Click to expand...

That makes it a good deal simpler. Some other things that could simplify
matters:

1) Lots of the comma-separated fields in the textfile are delimited by
quotes. This is standard practice to indicate that they are text fields;
but it's only *necessary* if there's a possibility that the data itself
may include commas. Do you know the data well enough to be certain that
there will never be a comma within a field? If so, coding becomes a lot
simpler because we can ignore the quotes.

2) Is the last record in the file always terminated with an "END" line,
or does the file just stop?

3) Is the first field of the first line *always* a 5-digit code? Could
it ever have a different number of digits or contain letters or other
characters?

Starting with code like Joe posted, you'd do something like this
(pseudocode) for the central loop. Details depend on answers to the
above questions, how much VBA you know, etc. Post back with the answers
and if the prospect isn't too intimidating, between us we can start
fleshing out the pseudocode.

blFirstLine = True 'flag to show we're just starting
Do While Not EOF(1)
If blFirstLine Then
Do
Line Input #1, sLine
Loop Until sLine matches the pattern of the
first line of the record (i.e. skipping any
blank lines)
Split sLine into fields
Create the corresponding record in one table
Store the first field in a variable for later
blFirstLine = False
Do
Line Input #1, sLine
Loop Until sLine matches the pattern of a parts record
Else
Do While sline matches the pattern of a parts record
Split sLine into fields
Create the corresponding record in the parts table,
(including the previously stored first field)
Line Input #1, sLine
Loop
'if we're here, we've passed the last
' parts line in this record
Do Until sLine Like "END" 'skip any blank lines
Line Input #1, sLine
Loop
'if we're here, we're just past an "END" line
blFirstLine = True 'beginning of the next record
End If
Loop




"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 "

************DON'T WANT TO IMPORT'
0
2
""
""
0
7

2,"16BAP0****S"
6,"15BAP0****S"
1,"16BRP0"
1,"16BSP0"
1,"16BXP0"
3,"15BRP0"
6,"15BSP0"
**********START OF PARTS INFORMATION
"1",279.4,"P16PSIBE","GxB",""
"2",685.8,"P15PSIBE","BxB",""
"3",395.3,"P15PSIBE","BxB",""
"4",481,"P15PSIBE","BxB",""
"5",2,"L15PCB*P1*","",""
"6",1,"E15PSIBW9015","",""
"7",1,"T16PSIBU15B","",""
"8",1,"R16PSIBQ15BBB","",""
"9",2,"A15PSIBC","",""
"END" ************************INDICATES END OF THIS
RECORD

"01000","PHOS. LINE 2 2A PUMP","00","P2-2A-1085-
B",3,"316L"
76,157.9179,173,"CS","01-16-2003",0
"1 ADDED PRE-GROVED 8IN X 6IN PUP","01-21","CS"

Thanks again for your help.

Shawna


-----Original Message-----
Hi Shawna,

Access's built-in text file import system can't handle
files like this.
Instead, you have to use VBA to read the file line by
line, parse each
line and put the data into the appropriate tables and
fields. If you
need more help, post back here with more information,
including a sample
of the data.

On Tue, 18 Nov 2003 10:38:41 -0800, "shawna"

I need to import information from a text file but it
isn't deliminated nicely. i.e. a sales order
The file consists of name and address info, but also
the
parts info. Is there a different way to have access
find
the right information for the right field/table besides
the standard one row = one record in the deliminated
fomat? Any help is appreciated!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Thank you again for your help, I don't have a lot of
knowledge with VB programming (not much experience with
it in the last few years.)

My answers to your questions:

1) There will never be a comma in any of the fields - it
is basically qty and part # information.

2)The last line of the file is END

3) The first field will always only be 5 characters - I
cannot guarantee it will never have a letter in but
typically it is a number

Below is a sample of the informaiton that I need to import
with the field names I'd use (after skipping all the
stuff I don't want). I would need to add the Order #
from the First line (first 5 char) in the record to each
Line # on the order so it would reference back.

Line#, QTY, PartNum, EndType, HeatNum (usually blank)
"1",279.4,"P16PSIBE","GxB",""

Shawna
-----Original Message-----
Would there be a way to grab the first field from the
first line i.e. "01001" then skip the rest of the lines
until I get to the parts table (outlined in the example)-
I can import the other information using a different
method I was trying to limit the number of imports these
guys will have to do but....
Click to expand...

That makes it a good deal simpler. Some other things that could simplify
matters:

1) Lots of the comma-separated fields in the textfile are delimited by
quotes. This is standard practice to indicate that they are text fields;
but it's only *necessary* if there's a possibility that the data itself
may include commas. Do you know the data well enough to be certain that
there will never be a comma within a field? If so, coding becomes a lot
simpler because we can ignore the quotes.

2) Is the last record in the file always terminated with an "END" line,
or does the file just stop?

3) Is the first field of the first line *always* a 5- digit code? Could
it ever have a different number of digits or contain letters or other
characters?

Starting with code like Joe posted, you'd do something like this
(pseudocode) for the central loop. Details depend on answers to the
above questions, how much VBA you know, etc. Post back with the answers
and if the prospect isn't too intimidating, between us we can start
fleshing out the pseudocode.

blFirstLine = True 'flag to show we're just starting
Do While Not EOF(1)
If blFirstLine Then
Do
Line Input #1, sLine
Loop Until sLine matches the pattern of the
first line of the record (i.e. skipping any
blank lines)
Split sLine into fields
Create the corresponding record in one table
Store the first field in a variable for later
blFirstLine = False
Do
Line Input #1, sLine
Loop Until sLine matches the pattern of a parts record
Else
Do While sline matches the pattern of a parts record
Split sLine into fields
Create the corresponding record in the parts table,
(including the previously stored first field)
Line Input #1, sLine
Loop
'if we're here, we've passed the last
' parts line in this record
Do Until sLine Like "END" 'skip any blank lines
Line Input #1, sLine
Loop
'if we're here, we're just past an "END" line
blFirstLine = True 'beginning of the next record
End If
Loop




"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 "

************DON'T WANT TO IMPORT'
0
2
""
""
0
7
Click to expand...
2,"16BAP0****S"
6,"15BAP0****S"
1,"16BRP0"
1,"16BSP0"
1,"16BXP0"
3,"15BRP0"
6,"15BSP0"
**********START OF PARTS INFORMATION
"1",279.4,"P16PSIBE","GxB",""
"2",685.8,"P15PSIBE","BxB",""
"3",395.3,"P15PSIBE","BxB",""
"4",481,"P15PSIBE","BxB",""
"5",2,"L15PCB*P1*","",""
"6",1,"E15PSIBW9015","",""
"7",1,"T16PSIBU15B","",""
"8",1,"R16PSIBQ15BBB","",""
"9",2,"A15PSIBC","",""
"END" ************************INDICATES END OF THIS
RECORD

"01000","PHOS. LINE 2 2A PUMP","00","P2-2A-1085-
B",3,"316L"
76,157.9179,173,"CS","01-16-2003",0
"1 ADDED PRE-GROVED 8IN X 6IN PUP","01-21","CS"

Thanks again for your help.

Shawna


-----Original Message-----
Hi Shawna,

Access's built-in text file import system can't handle
files like this.
Instead, you have to use VBA to read the file line by
line, parse each
line and put the data into the appropriate tables and
fields. If you
need more help, post back here with more information,
including a sample
of the data.

On Tue, 18 Nov 2003 10:38:41 -0800, "shawna"

I need to import information from a text file but it
isn't deliminated nicely. i.e. a sales order
The file consists of name and address info, but also
the
parts info. Is there a different way to have access
find
the right information for the right field/table besides
the standard one row = one record in the deliminated
fomat? Any help is appreciated!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Shawna,

You're going to have to do most of the work yourself: peer-to-peer
support only goes so far<g>. Here's yesterday's pseudocode revised in
the light of what you said, to show what I think is a reasonable line of
approach. First, a couple of custom functions:

Function ReadALine(FileNum As Long) As Variant
'read line from file, strip off quote marks
'and split into fields into an array
Dim strLine As String

Line Input #FileNum, strLine
strLine = Replace(strLine, """","")
ReadALine = Split(strLine, ",")
End Function

Function IsPartRecord(FieldArray As Variant) As Boolean
'Return true if the record matches a "part" line
'i.e. 5 fields with first two fields numeric, e.g.
1,279.4,P16PSIBE,GxB,
2,685.8,P15PSIBE,BxB,
'Use Ubound(FieldArray)+1 for the number of fields
'Use IsNumeric(FieldArray(0)) to see if first field
'is numeric

End Function

Then the main code will be something like this:

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String
Dim arFields As Variant
Dim sOrderNum As String
Dim blFirstLine as Boolean

Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1
blFirstLine = True 'flag to show we're just starting

Do While Not EOF(1)
If blFirstLine Then
Do
arFields = ReadALine(1)
Loop Until UBound(arFields) = 5 And _
arFields(0) Like "[!""][!""][!""][!""][!""]"
'or other test to identify the first line
'of the record
sOrderNo = arFields(0) 'store order number for later
blFirstLine = False
Do
arFields = ReadALine(1)
Loop Until IsPartRecord(arFields) 'custom function
Else
Do While IsPartRecord(arFields)
'Use the recordset rs to create and fill a record in
'in the parts table, using sOrderNo as well.
Line Input #1, sLine
Loop
'if we're here, we've passed the last
' parts line in this record
Do Until sLine Like "END" 'skip any blank lines
Line Input #1, sLine
Loop
'if we're here, we're just past an "END" line
blFirstLine = True 'beginning of the next record
End If
Loop




Thank you again for your help, I don't have a lot of
knowledge with VB programming (not much experience with
it in the last few years.)

My answers to your questions:

1) There will never be a comma in any of the fields - it
is basically qty and part # information.

2)The last line of the file is END

3) The first field will always only be 5 characters - I
cannot guarantee it will never have a letter in but
typically it is a number

Below is a sample of the informaiton that I need to import
with the field names I'd use (after skipping all the
stuff I don't want). I would need to add the Order #
from the First line (first 5 char) in the record to each
Line # on the order so it would reference back.

Line#, QTY, PartNum, EndType, HeatNum (usually blank)
"1",279.4,"P16PSIBE","GxB",""

Shawna
-----Original Message-----
Would there be a way to grab the first field from the
first line i.e. "01001" then skip the rest of the lines
until I get to the parts table (outlined in the example)-
I can import the other information using a different
method I was trying to limit the number of imports these
guys will have to do but....
Click to expand...

That makes it a good deal simpler. Some other things that could simplify
matters:

1) Lots of the comma-separated fields in the textfile are delimited by
quotes. This is standard practice to indicate that they are text fields;
but it's only *necessary* if there's a possibility that the data itself
may include commas. Do you know the data well enough to be certain that
there will never be a comma within a field? If so, coding becomes a lot
simpler because we can ignore the quotes.

2) Is the last record in the file always terminated with an "END" line,
or does the file just stop?

3) Is the first field of the first line *always* a 5- digit code? Could
it ever have a different number of digits or contain letters or other
characters?

Starting with code like Joe posted, you'd do something like this
(pseudocode) for the central loop. Details depend on answers to the
above questions, how much VBA you know, etc. Post back with the answers
and if the prospect isn't too intimidating, between us we can start
fleshing out the pseudocode.

blFirstLine = True 'flag to show we're just starting
Do While Not EOF(1)
If blFirstLine Then
Do
Line Input #1, sLine
Loop Until sLine matches the pattern of the
first line of the record (i.e. skipping any
blank lines)
Split sLine into fields
Create the corresponding record in one table
Store the first field in a variable for later
blFirstLine = False
Do
Line Input #1, sLine
Loop Until sLine matches the pattern of a parts record
Else
Do While sline matches the pattern of a parts record
Split sLine into fields
Create the corresponding record in the parts table,
(including the previously stored first field)
Line Input #1, sLine
Loop
'if we're here, we've passed the last
' parts line in this record
Do Until sLine Like "END" 'skip any blank lines
Line Input #1, sLine
Loop
'if we're here, we're just past an "END" line
blFirstLine = True 'beginning of the next record
End If
Loop




"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 "

************DON'T WANT TO IMPORT'
0
2
""
""
0
7


2,"16BAP0****S"
6,"15BAP0****S"
1,"16BRP0"
1,"16BSP0"
1,"16BXP0"
3,"15BRP0"
6,"15BSP0"
**********START OF PARTS INFORMATION
"1",279.4,"P16PSIBE","GxB",""
"2",685.8,"P15PSIBE","BxB",""
"3",395.3,"P15PSIBE","BxB",""
"4",481,"P15PSIBE","BxB",""
"5",2,"L15PCB*P1*","",""
"6",1,"E15PSIBW9015","",""
"7",1,"T16PSIBU15B","",""
"8",1,"R16PSIBQ15BBB","",""
"9",2,"A15PSIBC","",""
"END" ************************INDICATES END OF THIS
RECORD

"01000","PHOS. LINE 2 2A PUMP","00","P2-2A-1085-
B",3,"316L"
76,157.9179,173,"CS","01-16-2003",0
"1 ADDED PRE-GROVED 8IN X 6IN PUP","01-21","CS"

Thanks again for your help.

Shawna


-----Original Message-----
Hi Shawna,

Access's built-in text file import system can't handle
files like this.
Instead, you have to use VBA to read the file line by
line, parse each
line and put the data into the appropriate tables and
fields. If you
need more help, post back here with more information,
including a sample
of the data.

On Tue, 18 Nov 2003 10:38:41 -0800, "shawna"

I need to import information from a text file but it
isn't deliminated nicely. i.e. a sales order
The file consists of name and address info, but also
the
parts info. Is there a different way to have access
find
the right information for the right field/table
besides
the standard one row = one record in the deliminated
fomat? Any help is appreciated!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Thanks for your help to date, I don't want to overstep
the boundaries.... I will see what I can come up from
here.

Shawna
-----Original Message-----

Hi Shawna,

You're going to have to do most of the work yourself: peer-to-peer
support only goes so far<g>. Here's yesterday's pseudocode revised in
the light of what you said, to show what I think is a reasonable line of
approach. First, a couple of custom functions:

Function ReadALine(FileNum As Long) As Variant
'read line from file, strip off quote marks
'and split into fields into an array
Dim strLine As String

Line Input #FileNum, strLine
strLine = Replace(strLine, """","")
ReadALine = Split(strLine, ",")
End Function

Function IsPartRecord(FieldArray As Variant) As Boolean
'Return true if the record matches a "part" line
'i.e. 5 fields with first two fields numeric, e.g.
1,279.4,P16PSIBE,GxB,
2,685.8,P15PSIBE,BxB,
'Use Ubound(FieldArray)+1 for the number of fields
'Use IsNumeric(FieldArray(0)) to see if first field
'is numeric

End Function

Then the main code will be something like this:

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String
Dim arFields As Variant
Dim sOrderNum As String
Dim blFirstLine as Boolean

Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1
blFirstLine = True 'flag to show we're just starting

Do While Not EOF(1)
If blFirstLine Then
Do
arFields = ReadALine(1)
Loop Until UBound(arFields) = 5 And _
arFields(0) Like "[!""][!""][!""][!""][!""]"
'or other test to identify the first line
'of the record
sOrderNo = arFields(0) 'store order number for later
blFirstLine = False
Do
arFields = ReadALine(1)
Loop Until IsPartRecord(arFields) 'custom function
Else
Do While IsPartRecord(arFields)
'Use the recordset rs to create and fill a record in
'in the parts table, using sOrderNo as well.
Line Input #1, sLine
Loop
'if we're here, we've passed the last
' parts line in this record
Do Until sLine Like "END" 'skip any blank lines
Line Input #1, sLine
Loop
'if we're here, we're just past an "END" line
blFirstLine = True 'beginning of the next record
End If
Loop




Thank you again for your help, I don't have a lot of
knowledge with VB programming (not much experience with
it in the last few years.)

My answers to your questions:

1) There will never be a comma in any of the fields - it
is basically qty and part # information.

2)The last line of the file is END

3) The first field will always only be 5 characters - I
cannot guarantee it will never have a letter in but
typically it is a number

Below is a sample of the informaiton that I need to import
with the field names I'd use (after skipping all the
stuff I don't want). I would need to add the Order #
from the First line (first 5 char) in the record to each
Line # on the order so it would reference back.

Line#, QTY, PartNum, EndType, HeatNum (usually blank)
"1",279.4,"P16PSIBE","GxB",""

Shawna
-----Original Message-----
On Wed, 19 Nov 2003 14:56:51 -0800,

Would there be a way to grab the first field from the
first line i.e. "01001" then skip the rest of the lines
until I get to the parts table (outlined in the example)-
I can import the other information using a different
method I was trying to limit the number of imports these
guys will have to do but....

That makes it a good deal simpler. Some other things that could simplify
matters:

1) Lots of the comma-separated fields in the textfile are delimited by
quotes. This is standard practice to indicate that
Click to expand...
they
are text fields;
but it's only *necessary* if there's a possibility
Click to expand...
that
the data itself
may include commas. Do you know the data well enough
Click to expand...
to
be certain that
there will never be a comma within a field? If so, coding becomes a lot
simpler because we can ignore the quotes.

2) Is the last record in the file always terminated
Click to expand...
with
an "END" line,
or does the file just stop?

3) Is the first field of the first line *always* a 5- digit code? Could
it ever have a different number of digits or contain letters or other
characters?

Starting with code like Joe posted, you'd do something like this
(pseudocode) for the central loop. Details depend on answers to the
above questions, how much VBA you know, etc. Post back with the answers
and if the prospect isn't too intimidating, between us we can start
fleshing out the pseudocode.

blFirstLine = True 'flag to show we're just starting
Do While Not EOF(1)
If blFirstLine Then
Do
Line Input #1, sLine
Loop Until sLine matches the pattern of the
first line of the record (i.e. skipping any
blank lines)
Split sLine into fields
Create the corresponding record in one table
Store the first field in a variable for later
blFirstLine = False
Do
Line Input #1, sLine
Loop Until sLine matches the pattern of a parts record
Else
Do While sline matches the pattern of a parts record
Split sLine into fields
Create the corresponding record in the parts table,
(including the previously stored first field)
Line Input #1, sLine
Loop
'if we're here, we've passed the last
' parts line in this record
Do Until sLine Like "END" 'skip any blank lines
Line Input #1, sLine
Loop
'if we're here, we're just past an "END" line
blFirstLine = True 'beginning of the next record
End If
Loop





"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 "

************DON'T WANT TO IMPORT'
0
2
""
""
0
7


2,"16BAP0****S"
6,"15BAP0****S"
1,"16BRP0"
1,"16BSP0"
1,"16BXP0"
3,"15BRP0"
6,"15BSP0"
**********START OF PARTS INFORMATION
"1",279.4,"P16PSIBE","GxB",""
"2",685.8,"P15PSIBE","BxB",""
"3",395.3,"P15PSIBE","BxB",""
"4",481,"P15PSIBE","BxB",""
"5",2,"L15PCB*P1*","",""
"6",1,"E15PSIBW9015","",""
"7",1,"T16PSIBU15B","",""
"8",1,"R16PSIBQ15BBB","",""
"9",2,"A15PSIBC","",""
"END" ************************INDICATES END OF THIS
RECORD

"01000","PHOS. LINE 2 2A PUMP","00","P2-2A-1085-
B",3,"316L"
76,157.9179,173,"CS","01-16-2003",0
"1 ADDED PRE-GROVED 8IN X 6IN PUP","01-21","CS"

Thanks again for your help.

Shawna


-----Original Message-----
Hi Shawna,

Access's built-in text file import system can't handle
files like this.
Instead, you have to use VBA to read the file line by
line, parse each
line and put the data into the appropriate tables and
fields. If you
need more help, post back here with more information,
including a sample
of the data.

On Tue, 18 Nov 2003 10:38:41 -0800, "shawna"

I need to import information from a text file but it
isn't deliminated nicely. i.e. a sales order
The file consists of name and address info, but also
the
parts info. Is there a different way to have access
find
the right information for the right field/table
besides
the standard one row = one record in the deliminated
fomat? Any help is appreciated!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Do feel free to post back with specific questions.

Thanks for your help to date, I don't want to overstep
the boundaries.... I will see what I can come up from
here.

Shawna
-----Original Message-----

Hi Shawna,

You're going to have to do most of the work yourself: peer-to-peer
support only goes so far<g>. Here's yesterday's pseudocode revised in
the light of what you said, to show what I think is a reasonable line of
approach. First, a couple of custom functions:

Function ReadALine(FileNum As Long) As Variant
'read line from file, strip off quote marks
'and split into fields into an array
Dim strLine As String

Line Input #FileNum, strLine
strLine = Replace(strLine, """","")
ReadALine = Split(strLine, ",")
End Function

Function IsPartRecord(FieldArray As Variant) As Boolean
'Return true if the record matches a "part" line
'i.e. 5 fields with first two fields numeric, e.g.
1,279.4,P16PSIBE,GxB,
2,685.8,P15PSIBE,BxB,
'Use Ubound(FieldArray)+1 for the number of fields
'Use IsNumeric(FieldArray(0)) to see if first field
'is numeric

End Function

Then the main code will be something like this:

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String
Dim arFields As Variant
Dim sOrderNum As String
Dim blFirstLine as Boolean

Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1
blFirstLine = True 'flag to show we're just starting

Do While Not EOF(1)
If blFirstLine Then
Do
arFields = ReadALine(1)
Loop Until UBound(arFields) = 5 And _
arFields(0) Like "[!""][!""][!""][!""][!""]"
'or other test to identify the first line
'of the record
sOrderNo = arFields(0) 'store order number for later
blFirstLine = False
Do
arFields = ReadALine(1)
Loop Until IsPartRecord(arFields) 'custom function
Else
Do While IsPartRecord(arFields)
'Use the recordset rs to create and fill a record in
'in the parts table, using sOrderNo as well.
Line Input #1, sLine
Loop
'if we're here, we've passed the last
' parts line in this record
Do Until sLine Like "END" 'skip any blank lines
Line Input #1, sLine
Loop
'if we're here, we're just past an "END" line
blFirstLine = True 'beginning of the next record
End If
Loop
Click to expand...
Click to expand...
 
Back
Top