Importing Sequential Text File

[Ed Merritt]

I think this is called a sequential file, but I'm not sure. Anyway, I need a way to import a text file in this format using v10.5 in a desktop app:

John smith
9999 TANGLEWOOD CT
RIDGEFIELD, CT 06877-5512
Phone:000-000-9250


Dougie Smith
88888 Stanford Ave
Dallas, TX 75225-6931
Phone:777-777-7712


Linda Davisss
8989 W Grand Ave
Connersville, IN 46666
Phone:766987777

I obtain the information through copying and pasting and creating a text file, but would like to automate the import. The number of lines often changes - sometimes four, but could be five or six.

Anyone able to get me started on this?

Thanks.

[Clunes]

Re: Importing Sequential Text File

Is there any defined start and end point. Eg: I note you have a line space between each record. If this is the case then I would loop through the list replacing the CR with something "|" and where the line spac is I would replace with a CR. This would then give you a list that could be imported. The main issue at the end would be lining each record up so all fields match.

Do you have a 5 and 6 line example - I am presuming te extra lines will be part of the address section but the rest is contant like the name and the phone?

[Clunes]

Re: Importing Sequential Text File

I had a bit of a play with your example. I copied and pasted into notepad and saved. Then I made this script. Copy it and paste into code then run. It should:

1. Allow you to find the file
2. Edit it and put into a listview xdialog

dim prmpt_title as c
dim prmpt_filter as c
dim prmpt_default as c
dim prmpt_flag as c
dim cont1 as c
prmpt_title = "Select File"
prmpt_filter = "(*.txt)"
prmpt_default = ""
DIM SHARED myfile AS C

myfile = ui_get_file( a5_eval_expression(prmpt_title,local_variables()),prmpt_filter,prmpt_default,"X")

cont1 = file.to_string(myfile)
cont1 = stritran(cont1,crlf(),"|")
cont1 = stritran(cont1,"|||",Crlf())

ui_dlg_box("Quick ListView",<<%dlg%
{wrap=100}
[%M;K;%.120,20id^"Col1:30|Col2:30|Col3:30|Col4:30"Cont1!idchange];
%dlg%,<<%code%

%code%)

[Mike Wilson]

Re: Importing Sequential Text File

If you want to completely parse this into individual fields and individual records, this example might work for you. The example makes 3 suppositions:

1. the difference between those with 4, 5, 6 lines are additional address lines
2. there will always be a comma following the city name
3. there will always be a the full-colon before the phone number

[Ed Merritt]

Re: Importing Sequential Text File

Mike:
My original post left out a few things, so I grabbed a screen cap of an example address when it's in a Word document and the non-printing characters are viewable. Each address that I'm able to scrape from the web page is essentially the same, although some addresses have up to six lines. The constants in the addresses are the words "Your Merchant Order ID", "Payment Information" followed by a hard return, "Shipping Address:" followed by a hard return, and the last line "Shipping Phone:" and the final hard return.

Here's what it looks like: 2012-01-04_1532.png

Hope this helps a bit.

[Mike Wilson]

Re: Importing Sequential Text File

So,
I don't see Shipping Phone. Is that Phone:? That example is 7 lines.

Starting from the bottom, it is always: phone, city-state-zip, address, name, then lines (0-?)above that are not needed?

Why not just post an example of a notepad file that has about all the combinations that you might envision and that you would be wanting to extract so I can see what the needs are?

[Mike Wilson]

Re: Importing Sequential Text File

If it is that the last 4 lines are constant and the top 3 are the garbage, the attached is an example that would parse those.

[Ed Merritt]

Re: Importing Sequential Text File

So,
I don't see Shipping Phone. Is that Phone:? That example is 7 lines.
Starting from the bottom, it is always: phone, city-state-zip, address, name, then lines (0-?)above that are not needed?
Why not just post an example of a notepad file that has about all the combinations that you might envision and that you would be wanting to extract so I can see what the needs are?

You're right, it's "Phone:" only. The lines at the top contain the customer order number and that is essential. The first text is always "Your merchant order ID: #". Payment information is a link that is garbage, then "Shipping Address:" then the actual data, then it always ends with "Phone:".

Here's a more detail. An Amazon seller's account displays a web page of all orders sold by Amazon for the merchant's items. The customer's name and address is not available for download but it is displayed on the web page detail of a given order. Oddly, they do make available a report that can be downloaded with everything except that address information.

What I've been doing is to open the web page with the order detail, copy and paste the name and address along with the order number. I then manually massage that data and turn it into a comma-delimited text file which I then import into A5 as order header data. The download report also contains the order number and comprises the order detail data. When joined as a set, I have a complete record of the transaction and the ship-to recipient's address. The blank lines under the "Phone:" data are put there by me for readability, but they can go away if that makes things easier.

What I'm hoping to accomplish is to develop a script that will essentially do what I do manually - parse the data, and convert it to a comma-delim file that I can import. I'll still have to do the copy 'n paste routine, but that's easy. It's the manual massaging that's a killer.

Anyway, I've attached a text file the way it looks when I do the copy 'n paste stuff. In this example, there are two international addresses, and that's where the extra lines usually come in. They shouldn't be a deal-breaker because they comprise only a few orders each month and handling them manually is fine. The majority are US addresses and a script would be a slice of heaven as opposed to the drudgery of copy 'n pasting and manipulating text.

I appreciate you taking time to look at this Mike. I know there are file formats such as the .QIF used by financial programs that are laid out like this, and even XML files are somewhat similar, but they have markers that delineate where a script should start and stop. This one has me stumped, and my limited Xbasic skills are of little help.

[JohnZaleski]

Re: Importing Sequential Text File

Parsing text files can be an arduous task.There is a software program
called Monarch, which is a "data mining " program that i highly recommend for tasks like you describe. I have clients that import text files from their customers with varying formats.They are not programmers nor are they technically gifted.They successfully use Monarch to create parsing templates geared to particular customers.
I use Monarch commands within Alpha to generate text files to be imported by Alpha's predefined import operations.

[Mike Wilson]

Re: Importing Sequential Text File

Ed,
I know that your trying to be as clear as possible but exactness in what exactly are the consisancy of the text blocks is absolutely necessary to solve your need. It is essential to have clearly defined what are the constants and what are the variables in the text blocks that need evaluating to define the extraction routine.

I'll still have to do the copy 'n paste routine, but that's easy. It's the manual massaging that's a killer.

The majority are US addresses and a script would be a slice of heaven as opposed to the drudgery of copy 'n pasting and manipulating text.

First you say cutting and pasting is easy, then it's drudgery. Is cutting and pasting going to be a task that is maintained in the final solution, or are you seeking a solution that replaces your having to cutting and pasting?

You gave me an example with every text block containing the top three lines containing "Your Merchant Order ID: #,Payment Information, Shipping Address:. Again, I ask, is this a constant? Will every text block have those as the top three lines? Will there always be a Phone:xxxx line?

You have now added that there are addresses for foreign countries. An added "Oh by the way" that changes the ball game. I'd be more than happy to help you, but like I said in post thread #6 -

Why not just post an example of a notepad file that has all the combinations that you might envision and that you would be wanting to extract so I can see what the needs are?

Absent exactness and completeness, a routine for extraction cannot be acheived.