Import text file based on content (1 Viewer)

Ziggy1

Registered User.
Local time
Today, 12:50
Joined
Feb 6, 2002
Messages
462
I'm mulling around some idea's and not too sure which direction to take.


I need to bring some data into my database, they are text files (actually EDI X12) standard) but I need to figure out which file is related to the record.

How a can I search the contents of a file ( in a directory) to find the file name of my search criteria?

Once I know the file name I can adapt it with existing code, but it is finding the file that is the problem.

thanks
 

Guus2005

AWF VIP
Local time
Today, 13:50
Joined
Jun 26, 2007
Messages
2,645
Do a line by line search for your filename in all textfiles

F1 on "Line"
Code:
Line Input # Statement Example
This example uses the Line Input # statement to read a line from a sequential file and assign it to a variable. This example assumes that TESTFILE is a text file with a few lines of sample data.

Dim TextLine
Open "TESTFILE" For Input As #1    ' Open file.
Do While Not EOF(1)    ' Loop until end of file.
    Line Input #1, TextLine    ' Read line into variable.
    Debug.Print TextLine    ' Print to the Immediate window.
Loop
Close #1    ' Close file.
Using the Dir command you can loop through all files on your directory.
Using the instr command you can check for the filename.

HTH:D
 

Ziggy1

Registered User.
Local time
Today, 12:50
Joined
Feb 6, 2002
Messages
462
thanks Guus2005, I didn't see your reply when I clciked on "userCP". I will see if it works for me.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Sep 12, 2006
Messages
15,613
one way is to let yoiur users pick the file they want to import - load it to a temporary table, then check the contents and react accordingly

a table is a lot easier to handle in access than a text file.
 

Ziggy1

Registered User.
Local time
Today, 12:50
Joined
Feb 6, 2002
Messages
462
i came up with a different method to get my data, which has nothing to do with what I requested....I was able to use the existing data by modifying the field length so thge message I was looking for would not get cut off.....

Gemma...that would not have worked because I don't know the file name, I needed to search the contents of the file, and I think Guus's might not work eighter because he is suggesting to search for the file name as well.

So not to put the idea to bed, as I think it could be useful....I wanted to perform a search which is similar to what you do in a Windows when searching for "Files and Folders"...the second field "A word or Phrase in the File". I think it is commonly referred to as "Grep", I use it in Unix to search a directory.
 

Attachments

  • ScreenShot_ ziggy.stouten007.jpg
    ScreenShot_ ziggy.stouten007.jpg
    11.7 KB · Views: 103

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Sep 12, 2006
Messages
15,613
grep is realy useful isnt it - im not sure if a substitute is avialabe for vba
 

DCrake

Remembered
Local time
Today, 12:50
Joined
Jun 8, 2005
Messages
8,632
Can you provide a sample of a typical text file and the criteria you are searching for which will determine the destination. This will provide a better starting point.

David
 

Ziggy1

Registered User.
Local time
Today, 12:50
Joined
Feb 6, 2002
Messages
462
Dcrake, here is a stripped down file, I have to hide all the data so I over wrote with X's and the few that are left are over writte, but it can be any file any content for an example.

ISA*00* *00* *01*xxxxxxxxx *12*xxxxxxxxxx *xxxxxx*xxxx*U*xxxxx*0xxxxxxxx*0*P*>
GS*OW*xxxxxxxxx*xxxxxxxxx9*xxxxxxxx*0xxx*xxxxxxx*X*004030
ST*940*xxxxx
W05*C*xxxxxxxxx4*556867
N1*PF*xxxxxxxxx
N3*15101 xxxxxxxxxxxxx RD.*STATION xxxx
N4*xxxxxxxxxx*AZ*xxxxx*US
N1*DE*xxxx cccc*9*xxxxxxxxxx000
N1*WH*xxxxxxxxxxxxxxx ON*9*xxxxxxxxxxx30
N1*RC*xxxxxxxxxxxxxxxxxxxxxxxxxx*9*xxxxxxxxxx030
N1*CN*xxxxxxxxxxxxxxxxxxxxxxxrio*91*xxxxxxxxxx
N3*6941 xxxxxxy Road
N4*xxxxxxxxxxx*ON*xxx xxx*CA
N9*BM*xxxxxxxxxxxxxxxxx
N9*WP*7565465161
N9*TH*TK
G62*10*20090221
G62*02*20090222
G62*07*20090216*W*0745
NTE*WHI*STA 2/23
NTE*WHI*Attn. Whse/Carrier: Delivery appts. must be made 72-hrs. in advance or
NTE*WHI*late fee/fine will be invoiced to the carrier.
NTE*WHI*If issues/delays with DELIVERY contact xxxxxxxxxxxxxxxxxxxxxxxxxxx
NTE*WHI*(x-999) or xxxxxx @ xxx-xxx-xxxx
NTE*TRA*STA 2/23
NTE*TRA*Attn. Whse/Carrier: Delivery appts. must be made 72-hrs. in advance or
NTE*TRA*late fee/fine will be invoiced to the carrier.
NTE*TRA*If issues/delays with DELIVERY contact xxxxxxxxxxxxxxxxxxxxxxxxxx2
NTE*TRA*(xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
W66*PP*ZZ
LX*1
W01*600*CA*084655854031*VS*0000000000009xxxxx*VN*xxxxxxxxxxx0
G69*xxxxx poiu MB 6/1.7L
N9*LI*10
W20****16200*A3*L**660*CF
LX*2
W01*0*EA*xxxxxxxxxxx0*VS*000xxxxxxxxxxxxxx1*VN*6xxxxxxxxx00
G69*CHEP Pallet
N9*LI*20
W20****0*A3*L**0*CF
W76*600*16200*PG*660*CF
SE*40*xxxx4
GE*1*xxxxxxx
IEA*1*xxxxxxxxx
 

DCrake

Remembered
Local time
Today, 12:50
Joined
Jun 8, 2005
Messages
8,632
What I was really after was an actual text file with say only one reocrd in it. If it has header row with filed names then better. The data means nothing to me but you can anonimise it. Could not tell from your example what character is used as a field delimiter and whether the contents for each record is read vertically or horizontally.

It also does not tell me which item is the last item for the record.

What you also ommitted was the actual piece of data that would tell the system what to do with the record. Ie what is the name of the field that needs evaluating.

Hope that helps
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Sep 12, 2006
Messages
15,613
but did you say the problem is you arent sure which file you need to process?

don't you want to bring all the EDI files into the database?

you either need to bring them all in, or decide outside access which ones you want to bring in. eg you mentioned grep, but you do have a windows search options to scan files for selected text

anyway, assuming a file like this has a definite sequence of line prefixes, and will be well formed ... then i think you have to open it as a text file, and read in lines one at a time

effectively

open diskfile for input as #channum
repeat
input line
process line
until eof

in the database you will have a true relational model of invoices/invoice lines etc, you will have to assemble new records details as you parse the text file, and then insert them into your true tables with SQL statements
 

wiklendt

i recommend chocolate
Local time
Today, 23:50
Joined
Mar 10, 2008
Messages
1,746
uhm... i might be showing my naivety, but why not use the windows search "a word or phase in a file"? or that grep thing you mentioned?
 

Ziggy1

Registered User.
Local time
Today, 12:50
Joined
Feb 6, 2002
Messages
462
What I was really after was an actual text file with say only one reocrd in it. If it has header row with filed names then better. The data means nothing to me but you can anonimise it. Could not tell from your example what character is used as a field delimiter and whether the contents for each record is read vertically or horizontally.

It also does not tell me which item is the last item for the record.

What you also ommitted was the actual piece of data that would tell the system what to do with the record. Ie what is the name of the field that needs evaluating.

Hope that helps

I know but it is not an issue working with the file and importing...the issue is searching a directory to find the file. This file layout is not good for importing, it is not a flat file but an EDI X12 standard...I would only be interested in the NTE segment...but really I am not even worried about that yet... 1st step is to see if there is a way to search.

* as i mentioned I already have a workaround, but I am curious if this is possible from Access because it could be useful....so don't kill yourself trying to figure it out.
 

Ziggy1

Registered User.
Local time
Today, 12:50
Joined
Feb 6, 2002
Messages
462
but did you say the problem is you arent sure which file you need to process?

don't you want to bring all the EDI files into the database?

you either need to bring them all in, or decide outside access which ones you want to bring in. eg you mentioned grep, but you do have a windows search options to scan files for selected text

anyway, assuming a file like this has a definite sequence of line prefixes, and will be well formed ... then i think you have to open it as a text file, and read in lines one at a time

effectively

open diskfile for input as #channum
repeat
input line
process line
until eof

in the database you will have a true relational model of invoices/invoice lines etc, you will have to assemble new records details as you parse the text file, and then insert them into your true tables with SQL statements

There are many files that I would not want, you'll have to explain what you posted because I'm not sure if you were posting an overview of the steps or some sort of Script? I wasn't overly concerned about all the fields in the file, basically once the file is identified, I would import it into a temp table and strip out what I don't need, but if you had a way ofreading the file and turning it into a flat file then let me know.

thanks
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Sep 12, 2006
Messages
15,613
lets start again

are all these files GRN's , or Invoices or something

so either you decide which ones you want to get into access, or import them all
you could pre-read the files with access, and then try to determine whether you should import them. Personally, I would leave that till later, and sort the base functionality first, so check them manually to find the ones you want.

so assuming you have a file to import, you need to bring it into access

now you cant use transfertext or anything similar, because the layout of each line depends on the line prefix

so you have to read in the lines, one at a time and process the lines

these things are generally headers and details, so you can have a structure to hold a header, probably a type declaration, and a structure to hold the details, probably an array of a different type declaration

so as you process the lines, populate the structures, and when you have finished (reached the end of the file) use a sql statement to insert the structure info INTO your real tables.


one other issue is whether the file is well formed or not - ie do you need to check that all the prefixes are in the correct order - you assume EDI files are Ok, but do you need to check everything just in case

hope these ideas are useful
 

Ziggy1

Registered User.
Local time
Today, 12:50
Joined
Feb 6, 2002
Messages
462
Gemma..thanks for the input, I would actually be able to use the transfer text, simply by loading it all as a single field, then loop through the recordset using Select Case to check the segments from the left in order to determine the position of the value of that segment, I believe the first record will tell me how many records.

Problem is the directory is for processed files so it is not like I am monitoring a directory for new files to arrive...if that was the case I would want to import every file and then decide what to do with it....this directory is massive
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Sep 12, 2006
Messages
15,613
in that case lets say these files are in a directory called

h:\processed

just create another called h:\processed\final and manually move everything into there, to give you a blannk folder.

now new files will arrive in to the H\:processed folder, and they will be all the new processed files, that havent been through access yet.

so deal with them in access, and get access to move them to the final destination folder
(look at commands name, and kill)
 

Ziggy1

Registered User.
Local time
Today, 12:50
Joined
Feb 6, 2002
Messages
462
thanks Gemma,

I have an import routine that can move and rename the files, I can't move the files to another directory because there are too many, and I can't do anything that will alter the existing file directories.


I found this link about Windows Grep...qgrep you can download HERE from MS

It works pretty much like the Unix one does, except I don't know how I can call it from access and pass the variable?

syntax... qgrep -l Variable 380*
 

Atomic Shrimp

Humanoid lifeform
Local time
Today, 12:50
Joined
Jun 16, 2000
Messages
1,954
Gemma..thanks for the input, I would actually be able to use the transfer text, simply by loading it all as a single field, then loop through the recordset using Select Case to check the segments from the left in order to determine the position of the value of that segment, I believe the first record will tell me how many records.
I've had to work with importing EDI files before - it's a massive headache (and creating them is worse). I've used either method - Gemma's line-at-a-time and your suggestion of importing each line into a single large text field - a couple of important points on that latter method:

-It will break if any line in the text file is ever more than 255 characters
-You need an autonumber in the table, to preserve the order of records (it's important in EDI, because one data object may be described by several lines in the file)

Other than that, the processing of the data is the same in principle whether you import it, or work straight out of the file - design a relational table structure that describes the content of the file, then keep those tables open while you process each line in the text file, slicing up the lines based on their first few characters, distributing the data into the right places in your tables as you go.

I'll have a look and see if I still have a backup of my code somewhere - it was a long time and several job changes ago - and won't be exactly what you want, but might be helpful on some of the general ideas.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Sep 12, 2006
Messages
15,613
yep - its a long job the first time you do it, because the only tools you have are string slicing tools (instr, mid, left and right) - but once you have done it, you dont need to do it again!

i dont think it breaks if the lines are >255 chars, as long as you store the data in text string variables, rather than table fields.

f you can rely on the file being well formed ie everything in the order it should be, its easier - otherwise you have to make sure the rows are in the correct order.

finally i really cant believe that you are unable to get rid of old/processed files - the only way to do this is to expect to bring in every file, or to select individual files with a file picker/dialog - you really dont want to be scanning every file in access, to find which ones you need.
 

Ziggy1

Registered User.
Local time
Today, 12:50
Joined
Feb 6, 2002
Messages
462
finally i really cant believe that you are unable to get rid of old/processed files - the only way to do this is to expect to bring in every file, or to select individual files with a file picker/dialog - you really dont want to be scanning every file in access, to find which ones you need.

They do get purged, but they build up fast, don't forget, the plan was to use something to search the directory to figure out the file that way i would not have to process all of them in Access. This qgrep would do that, but I'd need some sort of Shell Execute to use it..?
 

Users who are viewing this thread

Top Bottom