Import file with comma in fields

I am using Foxpro ver 5.0a. I am a novice who has done some manipulation of a program file created by someone else. Here is the code below I have that imports a tsv file into a table. When the tsv file has a comma in it then number after comma is ignored. For example, price is is  $1,099.98 in the tsv file but the foxpro populates my wwl table with 1.00 in the price field because of the comma. I have been using the progam sucessfully for a period of time without a hitch only when sales price is over $999.99.
 Anyway to get around the problem with the comma without manipulating the tsv file beforehand?

USE wwl
APPEND FROM wwlimport.tsv TYPE DELIMITED with tab
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
If the fields contain a comma, then you need to use another delimiter. Make sure that the numeric field is big enough to handle all the values in the table.

How many fields are there in the file? If one, you can fool the system by specifying another delimiter like TAB.

You can also import the field to a character field and then remove all the "$," from them and convert to numeric:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
As your delimitere is TAB, the comma is not interpreted as delimiter towards the next field, still the conversion from string to numeric currency value stops there. There is no setting you could use to stop that, but Cyrils second advice is still good, change your wwl.dbf or wwl cursor to have a char field at that point and then in a second stpe convert to numeric.

Bye, Olaf.
Comma may be the delimiter, but is it Quote-Comma, or just Comma?

I have found that with a file containing just Commas as the delimiters it is simple to use Excel to make the conversion.

Change the file extension to CSV (if it is not already that).
Double click on the file and open it into Excel (2000 or 2003 - nothing later).
Highlight the desired Rows & Columns
Do a SaveAs...   into DBF4 format.
Exit Excel

Then within VFP, merely use the data as desired

Good Luck
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

You may use following code which will read the TSV file by low level file function and populate the fields in your DBF. You may define delimiter and any character which must be removed from numbers.

It supports just strings and numbers but it should be easy to add more data types. If you have VFP 5 then yo will have to load FoxTools.FLL library which is in FoxPro home folder obviously (the code contains all necessary commands to do it).


SET LIBRARY TO (HOME()+'foxtools.fll')

lcFile = "import1.tsv"
lcTable = "import1.dbf"
lcDelimiter = CHR(9)
lcIgnoreList = ","

lnHandle = FOPEN(lcFile)
IF lnHandle < 0
  WAIT WINDOW "File opening error " + TRANSFORM(lnHandle)

USE (lcTable)

DO WHILE !FEOF(lnHandle)
  lcLine = FGETS(lnHandle)
  FOR lnI = 1 TO MIN(WORDS(lcLine, lcDelimiter), FLDCOUNT())
    lcField = WORDNUM(lcLine, lnI, lcDelimiter)
    IF !EMPTY(lcField)
      DO CASE
      CASE TYPE(FIELD(lnI)) $ 'CM'
        REPLACE (FIELD(lnI)) WITH lcField
      CASE TYPE(FIELD(lnI)) $ 'NY'
        *-- Remove unwanted characters from number
        REPLACE (FIELD(lnI)) WITH VAL(CHRTRAN(lcField, lcIgnoreList, ''))
      CASE TYPE(FIELD(lnI)) $ 'L'
        ? "Logical fields are not supported yet"
      CASE TYPE(FIELD(lnI)) $ 'D'
        ? "Date fields are not supported yet"
      CASE TYPE(FIELD(lnI)) $ 'T'
        ? "Time fields are not supported yet"
        ? "Fields of type " + TYPE(FIELD(lnI)) + " are not supported yet"

= FCLOSE(lnHandle)

Open in new window

cebu1014Author Commented:
I define the field in table WWL to character as you describe. It imports the data as needed. But fieldneeds to be treated as value for further use in code.  How can I format the data in that table to go back to a value. Ideally with leading $.
In a Comma Delimited file, ALL field values are Character until and/or unless they are otherwise modified.  

That is due to the fact that Commas are characters and therefore everything in the file is initially expected to be a Character value.

You can change the field value type either AFTER the fields have been imported or you can modify the code to identify specific 'fields' and handle them accordingly.

In Captain's code above you notice that many field types are not handled.

If you know which 'word' you want to modify, then you have to modify his code to handle the value as you need.

Otherwise, just import everything as a Character and then, after-the-fact, then change the VFP data table field type as desired.

ALTER TABLE import1 ALTER ThisField N(12,2)
  && <-- Change Field type to N(12,2)

Good Luck
cebu1014Author Commented:
Also can I just leave the field which is called price in the wwl table as currency or numeric type and use your approach but alter the command was that used  for a character field

in other words.....

VAL(CHRTRAN("PRICE","$,","")) that will work in a numeric or currency field
Olaf DoschkeSoftware DeveloperCommented:

you need to convert from the character into currency field type. Cyril already gave you the expression to do that: VAL(CHRTRAN(characterfield,'$,','')).

If you don't mind the wwlimport.dbf to contain both the character price and the currency price field, then you could do as follows.

Create a wwlimport with the first price field in order of the tsv file as char named cPrice, and another currency price field as last field, as one more, than there are fields in the tsv file. APPEND will then keep this price field blank and you can do a replace or update from cPrice to Price within the dbf as in:

USE wwlimport In 0
ZAP IN wwlimport
SELECT wwlimport
APPEND FROM wwlimport.tsv TYPE DELIMITED with tab
REPLACE ALL Price With VAL(CHRTRAN(cPrice,'$,','')) In wwlimport.

Open in new window

Then you could copy that to a wwl.dbf via APPEND again, and the cPrice field will not be transferred or work with the wwlimport field, if you don't mind that extra char price field.

As a small conceptual example:
* three fields char currency, some text and some date.
lcRecord = "$1,234.56"+chr(9)+"text"+chr(9)+"20121221"
* store into a tsv file

* create import cursor with 4 fields, 3 as above and an additional currency price field "yPrice"
Create Cursor curImport (cPrice C(12), cText C(10), dDate D, yPrice Y)

* Append as usual
Append From (getenv("TEMP")+"/sample.tsv") TYPE DELIMITED with tab
* note: at this point the yPrice field is blank.
* copy over the price from the char to the currency field, 
* using Cyril's expression for the conversion from string to number:
Replace All yPrice With VAL(CHRTRAN(cPrice,'$,','')) In curImport

Open in new window

In regard to display of the currency with a currency symbol, see SET CURRENCY in the help, you can set the currency symbol and position of it right or left, eg:

Open in new window

Bye, Olaf.
It would be better to post the  wwl.dbf structure (LIST STRUCTURE TO FILE wwlstru.txt) and we may provide tailored help to your specific case. Post some import file sample as well.
cebu1014Author Commented:
Thanks. The formula for Character Transform was used along with utilizing another field in table to perform the conversion.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.