APPEND FROM with "tab delimited files"

[Alan Porter]

Due to various problems/inconsistencies I have had importing excel 97
spread sheets I am trying to append from a "tab delimited" text file.

APPEND FROM (lcSource) TYPE DELIMITED WITH TAB

Would seem to do the trick, however when I export a cell that contains a
double quoted piece of text, it becomes (for example)

"He said ""one day the documentation will be correct"""

Let us Ignore the fact that excel makes no mention that it will enclose
quoted text in quotes (it says it will if it contains commas, which is
equally pointless - tab delimited means delimited with tabs, who cares
about quotes). Foxpro appears to take each pair of double quotes and makes
each a seperate field. As a result the above text translates into three
fields.

The WITH CHARACTER doesn't work with WITH TAB (or BLANK). Does anyone know
a fix/work around? If you would like to offer a different solution, the
excel -> foxpro append requires that an excel date becomes a foxpro date
(doesn't work directly), that a excel time becomes a text string, and that
a short free form piece of text that is restricted only by excel (tab
delimited looked good, because you can't get tabs in a cell).

Thanks in advance for any consideration.

Alan.

[iain brodie]

why not just export the Excel file as a dbf type?
Before you do, youll have to make sure all the fields are set to the correct
length, by double clicking on the column separator.
Alan Porter wrote in message <01be603b$0ac56c40$0601a8c0@digital-3100-2>...

[Cindy Winegarden]

Alan,

I saved my spreadsheet as type csv. [CSV (Comma delimited) (*.csv) choice in
Excel save as]

Then I use APPEND FROM MyCsvTable.csv DELIMITED.

Fox converts the data types unless it's impossible. ("ABC" into a numeric
field.)

--
Cindy Winegarden
Duke Children's Information Systems
Duke University Medical Center
cindy.wi...@duke.edu

<snip>

[Alan Porter]

iain brodie <uki...@aol.com> wrote in article
<#qeq53MY#GA....@uppssnewspub05.moswest.msn.net>...

> why not just export the Excel file as a dbf type?
> Before you do, youll have to make sure all the fields are set to the
correct
> length, by double clicking on the column separator.
> Alan Porter wrote in message
<01be603b$0ac56c40$0601a8c0@digital-3100-2>...

[..]

Mistake on my part in this section: Excel dates work fine, excel times get
converted to a date: 19000100 with dBase III and IV.
dBase II gets converted to 0.

> >The WITH CHARACTER doesn't work with WITH TAB (or BLANK). Does anyone

[Alan Porter]

Just tried the same thing. Exporting as csv will Change the field:

He said "one day the documentation will be correct"

To:

"He said ""one day the documentation will be correct"""

When foxpro imports that, It creates three seperate fields. Maybe the fact
that
the field that follows this freeform text is also a text field doesn't
help.

Here is a sample from the excel spread sheet if anyone want to try to get
it to work:

Initials % Date % Start % End % Description % Account
ADP % 02/26/1999 % 09:00 % 09:30 % Work, again, on "Excel" % SOFT
ADP % 02/26/1999 % 09:30 % 10:00 % Reply to kind posters % SOFT

Fields Excel Foxpro
Initials Text C(3)
Date Date D
Start hh:mm C(10)
End hh:mm C(10)
Desc. Text C(80)
Acct. Text C(8)

Cindy Winegarden <cindy.wi...@duke.edu> wrote in article
<#abj#4SY#GA....@uppssnewspub04.moswest.msn.net>...

> Alan,
>
> I saved my spreadsheet as type csv. [CSV (Comma delimited) (*.csv) choice
in
> Excel save as]
>
> Then I use APPEND FROM MyCsvTable.csv DELIMITED.
>
> Fox converts the data types unless it's impossible. ("ABC" into a numeric
> field.)
>
> --
> Cindy Winegarden
> Duke Children's Information Systems
> Duke University Medical Center
> cindy.wi...@duke.edu
>
> <snip>

[Christopher Carey]

When you save as .dbf, you get all but the times correctly into a table.
When you save using the other methods (csv, tab.) you can get the times, but
not
the text with quotes. Maybe do both, and just replace the text in the
created table with that from the file.

Alternately, if you are just doing this once, use the formatted text, (space
delimited) (*.prn) option. You will have to figure out how long each field
is, it is based on the width of the excel column, and if you did it again
later, with longer columns, the fields will be longer. That is why I would
only do this if you are going to convert once, or at least, not very often.

Alan Porter wrote in message <01be6198$ded07800$0601a8c0@digital-3100-2>...

[Andrew Coates]

Alan,

You may wish to consider doing some pre/post processing

1. Read each line of the input file using LLF functions into a variable (say
lcLine)
1a. lcLine = strtran(lcLine, [""], [%%%%])
1b. Write lcLine to a new text file (say NewFile.txt)
2. Append from NewFile delimited with TAB (into your import table)
3. Replace Desc with strtran(desc, [%%%%], ["]) for ! eof()

You could also use the filetostr if you're using VFP6

1. lcOldValue = FILETOSTR(inputFile)
1a. lcNewValue = strtran(lcOldValue, [""], [%%%%])
1b. STRTOFILE(lcNewValue, 'NewFile.txt', .f.)
2. Append from NewFile delimited with TAB (into your import table)
3. Replace Desc with strtran(desc, [%%%%], ["]) for ! eof()

Note that you would probably be better served if you didn't use DESC as a
field name - it'll confuse the SQL parser no end! (Try DESCR instead)

Cheers,

--
Andrew Coates
Civil Solutions
http://www.civilsolutions.com.au/
a.co...@civilsolutions.com.au
PGP Public Key on MIT Server

[Kooij van der, Niels]

instead of using "with charater" use "delimited with blank/tab"

I've written a program a while ago to strip characters which might
interfere with the field delimiter, this works fine (for me)
with plain text dumps.

* Set default to directory containing the source files
SET DEFA TO C:\TMP\DUMP\COSTCHRG
*
* The extension of the source files
Lext=[*.D]
*
* The destination directory
Fdest=[C:\TMP\DUMP\COSTCHRG\AA\]
*
* The delimiter used between the Fields
FDelim=[^]
*
* The delimiter used for carachter fields
CDelim=["]
*
* The new delimiter for use between the fields
NFDelim=[ ]
*
* The new value for the character which was equal to the field delimiter
NewValue=[&]

FFILE=ADIR(FF,Lext)
FOR II = 1 TO FFILE
OLDF=FOPEN(FF(II,1))
FSIZE = FSEEK(OLDF,0,2)
NEWF=FCREATE(FDEST+FF(II,1))
=FSEEK(OLDF,0)
LC=FGETS(OLDF,1000)
DO WHILE NOT EMPTY(LC)
FIN=[]
LCL=ALLTR(FDELIM)+ALLTR(LC)
FOR I = 1 TO LEN(LCL)
DPOS1=AT(FDELIM,LCL,I)
DPOS2=AT(FDELIM,LCL,I+1)
NUMER=OCCURS(CDelim,SUBSTR(LCL,DPOS1,DPOS2-DPOS1))
IF NUMER>0
REMO=SUBSTR(LCL,DPOS1+2,DPOS2-DPOS1-3)
LPO=STRTRAN(SUBSTR(LCL,DPOS1+2,DPOS2-DPOS1-3),CDelim,NewValue)
LCL=STRTRAN(LCL,REMO,LPO,1,1)
REMO=STRTRAN(SUBSTR(LCL,DPOS1,DPOS2-DPOS1),CDelim)
ELSE
REMO=STRTRAN(SUBSTR(LCL,DPOS1,DPOS2-DPOS1),CDelim)
ENDIF
IF NOT EMPTY(REMO)
IF NUMER#0
FIN=FIN+STRTRAN(REMO,FDELIM,FDELIM+CDelim)+CDelim
ELSE
FIN=FIN+REMO
ENDIF
ENDIF
ENDFOR
LFIN=LEN(FIN)
FIN=SUBSTR(FIN,2,LFIN-1)
LCS=FIN
LCS=STRTRAN(LCS,FDELIM,NFDelim)
=FPUTS(NEWF,LCS)
LC=FGETS(OLDF,1000)
ENDDO
=FCLOSE(OLDF)
=FCLOSE(NEWF)
ENDFOR

[Anders Altberg]

Hi Alan,

Save as Lotus WK3 in Excel and append as  TYPE WK3 in FoxPro, or save to dBase and append as dbf-file. This way there's no problem with date columns or in-text quotes in text columns, but datetime and time don't work.

Save to normal Excel format too because the WK3 file doesn't look as nice when opened again in Excel.

After some experimenting a found a way that preserves  Excel text, numbers, dates, datetimes and time formatted data: Open the Excel work sheet, Ctrl+A, Ctrl-C, switch to VFP.  On the VFP side this code did it for me:

 

create cursor temp (memo m)
append blank
modi memo memo nowait
replace memo with _cliptext
set safety off
copy memo memo to text4.txt
create cursor vv  ;

(cc c(20), num1 n(6), dd d, cc2 c(9), time1 c(8), bb n(12,10))
append from text4 delimited with tab

Using the clipboard is convenient for getting data into Excel too:

SELECT table

_vfp.DataToClip(,,3)

* createobject an Excel sheet object and use the Paste method.

 

-Anders

[Anders Altberg]

You can copy with any delimiter and a space as separator from VFP
COPY TO textfile DELIMITED WITH "#" WITH CHARACTER " "
Excel allows you to specify delimiters and separators in its Text Import
Wizard.