Append From (delimited) 1

[TGrahmann]

Hello! I am having some difficulty finding a solution the following problem. I have a file that is delimited by pipes (|) and I need to know if there's a way to append that file into a table! Any help would be much appreciated!

 

[Dan Freeman]

Delimited with pipes or separated with pipes?

A delimiter appears at the beginning and end of each field. The separator appears between each field.

Delimited with pipes:

|First|,|Name|

Separated with pipes:

"First"|"Name"

VFP's APPEND FROM supports DELIMITED WITH and an additional WITH clause for the separator. It usually takes a little fiddling to get just the right combination.

To give help, we need an exact example of the data.

 

[TGrahmann]

Separated, sorry

 

[Dan Freeman]

Without actually seeing a data sample you probably want something like this:

Append From (filename) SDF With Character "|"

But again, it's fiddly. If it doesn't work, don't give up on us. Come back with more information.

 

[TGrahmann]

That doesn't seem to be working. I get a syntax error

 

[Dan Freeman]

That's not exactly the "more information" I asked for.

If you're done providing information about your situation I'm done trying to help.

We can only help you if you'll let us.

 

[mjcmkrsr]

Hi,
From Hacker's Guide to VFP

DELIMITED is for files that contain data with delimiters and separators. There's a lot of confusion about those two, particularly since the command itself mixes them up. Delimiters are characters that surround the data in a field. For example, the word "snazzlefritz" is delimited by quote marks in this sentence. Separators come between two data items. For example, the following list of colors uses commas as separators: "chartreuse, lavender, fuchsia, taupe, teal." A delimited file normally contains both delimiters and separators. Here's an example (generated from the Labels.DBF that comes with VFP):

"DATAW","LABELLYT","Avery 4143",F,4869,/ /
"DATAW","LABELLYT","Avery 4144",F,39266,/ /
"DATAW","LABELLYT","Avery 4145",F,24620,/ /
"DATAW","LABELLYT","Avery 4146",F,32961,/ /
Each character field is surrounded by quotes (the delimiters), and fields are separated by commas. This is the default format for a delimited file.

TYPE DELIMITED can handle several other options. DELIMITED WITH BLANK and DELIMITED WITH TAB use quotes for delimiters, and fields are separated by spaces or tabs. DELIMITED WITH DELIMITER lets you specify the delimiter—fields are separated by commas.

VFP 5 introduced the DELIMITED WITH CHARACTER clause that lets you specify the separator. So DELIMITED WITH CHARACTER ! means that there's an exclamation point between each pair of fields. We can't see why they couldn't have improved the situation here by giving this option a useful name like SEPARATED BY. Nonetheless, we're very grateful to have this option because it increases the number of files we can handle without having to break out the low-level file functions.

You can combine DELIMITED WITH and DELIMITED WITH CHARACTER to specify both the delimiter and the separator.

Hence you may try

Append From (filename) Delimited With ["] Delimited With Character [|]

hh
MK

 

[Scott24x7]

TGrahmann,
Can you provide 1 or 2 lines of what the data looks like (you can make dummy data if it's otherwise sensitive) but it will help a lot if we can "See" the structure you have. You may be intuitively omitting important details that will help us resolve it, by just saying "| separated".

Actually if you can give us 5 - 10 lines, even better...


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

Almomst, it's

APPEND FROM (lcFile) DELIMITED WITH " WITH CHARACTER |

lcFile is the variabe name holding the filename as its value.

the separator is specified with the WITH CHARACTER clause, not DELIMITED WITH CHARACTER, there is no such clause. If you write DELIMITED WITH CHARACTER you combine the short form DELEIMIED of the DELIMITED WITH X clause with the WITH CHARACTER clause. The short form DELIMITED just tells the file has char fields delimited with double quotes, which is the default delimiter, it leaves out the WITH part as you don't want to specify another delimiter character. Combining the two clauses it looks like one, that is the bad thing about the structure of this command, badly designed.

The paragraph of the help about DELIMITED WITH CHARACTER Delimiter is wrong. The sample at it's end is good, though, just with strange delimiter and separater characters:

APPEND FROM mytxt.txt DELIMITED WITH _ WITH CHARACTER *

This would import something like _hello, world_*123456*NULL.

Also notice the only character that you'd specify in quotes is a semicolon, as it otherwise would be interpreted as line continuation indicator, you also can't specify unprintabe chars, but therfore you can specify BLANK or TAB for space or tab as delimier or separator.

Bye, Olaf.

 

[TGrahmann]

I've attached a text document with the data I'm trying to import

 

[TGrahmann]

Thanks, All! I've got it fixed. But There's emerged another issue. when using the following code I get the error "Table access denied (inventory.db) or (inventory.cdx) or (dhitems.db). Any ideas?

thisform.olecontrol1.UnlockComponent("70XZT-6V87E-SMFLB-0N56G-5Y0RH-L6QM6-MK7L4")
thisform.olecontrol1.username=ALLTRIM(thisform.text1.Value)
thisform.olecontrol1.password=ALLTRIM(thisform.text2.Value)
nCon=thisform.olecontrol1.connect()
IF nCon=1
WAIT WINDOW "Connected to D&H FTP. Downloading Items." TIMEOUT 2
thisform.olecontrol1.getfile("ITEMLIST",cPath+"ITEMLIST")
*this.Parent.olecontrol1.asyncgetfilestart("ITEMLIST","ITEMLIST")
thisform.olecontrol1.disconnect()
USE cPath+"dhitems.db"
APPEND FROM cPath+"ITEMLIST" Delimited With Character |
nRecs=RECCOUNT()
FOR x=1 TO nRecs
USE cPath+"dhitems.db"
GOTO x
WAIT WINDOW "Processing Record "+TRANSFORM(x)+" of "+TRANSFORM(nRecs) nowait
nSku=dhitems.mfno
nBarcode=dhitems.barcode
nLong=dhitems.long
nShort=dhitems.short
nCost=dhitems.cost

USE cPath+"inventory.db"
GO BOTTOM
APPEND BLANK
replace inventory.sku WITH nSku,;
inventory.barcode WITH nBarcode,;
inventory.longdesc WITH nLong,;
inventory.shortdesc WITH nShort,;
inventory.cost WITH nCost
ENDFOR
MESSAGEBOX("All D&H Inventory Has been imported!",64,"ShftPOS")
ELSE
MESSAGEBOX("Unable to connect to D&H Distributing FTP. Please verify that your username and password are correct!",48,"ShftPOS")
ENDIF

 

[mjcmkrsr]

Hi,
1) Is there a reason you name your tables XXX.BD instead of .DBF?
2) You have to open your index files
3) You don't need to close and open your tables all the time - you may want to ALIAS them
4) Use SCAN ... ENDSCAN instead if FOR ... ENDFOR

thisform.olecontrol1.UnlockComponent("70XZT-6V87E-SMFLB-0N56G-5Y0RH-L6QM6-MK7L4")
thisform.olecontrol1.username=ALLTRIM(thisform.text1.Value)
thisform.olecontrol1.password=ALLTRIM(thisform.text2.Value)
nCon=thisform.olecontrol1.connect()
IF nCon=1
[indent]WAIT WINDOW "Connected to D&H FTP. Downloading Items." TIMEOUT 2
thisform.olecontrol1.getfile("ITEMLIST",cPath+"ITEMLIST")
*this.Parent.olecontrol1.asyncgetfilestart("ITEMLIST","ITEMLIST")
thisform.olecontrol1.disconnect()

USE cPath+"dhitems.db" ORDER TheIndex ALIAS TblItems in 0
APPEND FROM cPath+"ITEMLIST" Delimited With Character |
USE cPath+"inventory.db" ORDER TheIndex ALIAS TblInventory IN 0
SELECT TblItems

[indent]SCAN
WAIT WINDOW "Processing Record "+TRANSFORM(RecNo())+" of "+TRANSFORM(Reccount()) nowait
nSku=TblItems.mfno
...

SELECT TblInventory
APPEND BLANK
replace TblInventory.sku WITH nSku,;
...

SELECT TblItems
ENDSCAN[/indent][/indent]

MESSAGEBOX("All D&H Inventory Has been imported!",64,"ShftPOS")
ELSE
MESSAGEBOX("Unable to connect to D&H Distributing FTP. Please verify that your username and password are correct!",48,"ShftPOS")
ENDIF

 

[Olaf Doschke]

The question is at ´which lines do you get the error?
opening and closing the whole table every time before you Goto x surely is not a good idea.
The OS is involved in any file operations and does a lot of status maintenance if you open and close a file, cache is involved, handles are involved, in the end your opening might fail because the closing is not processed fully. You don't do that, you open a table, process it and close it at the end. To get more immeditate writes to the dbf you don't buffer it, but closing and opening it for each record is really a bad idea.

Bye, Olaf.

 

[mjcmkrsr]

Hi,
To go further, you may also want NOT to change workareas, get rid of the variables and modify the code as follows

SCAN
WAIT WINDOW "Processing Record "+TRANSFORM(RecNo())+" of "+TRANSFORM(Reccount()) nowait
APPEND BLANK IN TblInventory

REPLACE IN TblInventory ;
TblInventory.sku WITH TblItems.sku, ;
TblInventory.xxx WITH TblItems.xxx
...

ENDSCAN

Finally please do also have a look at SQL UPDATE and SQL INSERT

hth
Mk

 

[Dan Freeman]

Table access denied" is not a FoxPro error message.

 

[Olaf Doschke]

True, so either the message is not 1:1 as reported or the message is coming from the application, eg through DBC events, eg the dbc_BeforeOpenTable might not permit access.

Bye, Olaf.

 

[mjcmkrsr]

Hi,

btw, are you aware that your are bloating your INVENTORY table with the same data from DHITEMS over and over again?

hth

MK

 

[Olaf Doschke]

He's not, he opens it reccount times and each time vopies over 1 record via GOTO x, APPEND BLANK+REPLACE

This iteration could be done much better, if not completely replaced by a single INSERT INTO inventory SELECT ... FROM dhitems. If not even APPENDING from the text file right away.

Bye, Olaf.