Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copy data from a deliminated file (using | instead of ,) to a cursor 2

Status
Not open for further replies.
PatMcLaughlin

PatMcLaughlin

Programmer
Dec 10, 2010
97
US
Every night we are downloading a .csv file from an FTP site. I then need to copy the data into a cursor. Here is what I did...
Code: 
CREATE CURSOR WITCURSOR (WIT_NO C(8), ACTIVE C(1), SETUPCUDTE C(19), EXPIREDTE C(19), ;
	LAST1 C(30), FIRST1 C(30), N1_MIDDLE C(1), LAST2 C(30), FIRST2 C(30), N2_MIDDLE C(1), ;
	ATTN C(30), ADDR1 C(30), ADDR2 C(30), CITY C(20), STATE C(2), ZIP C(10), PHONE C(10), SNOW_ATTN C(30), ;
	SNOW_ADDR1 C(30), SNOW_ADDR2 C(30), SNOW_CITY C(30), SNOW_STATE C(2), SNOW_ZIP C(10), PHONE2 C(10), ;
	SERIAL C(8))

SELECT WITCURSOR
APPEND FROM (tcLoadFile) DELIMITED WITH _ WITH CHARACTER |

I cannot get any data to the cursor.
I have verified all fieldnames, their order, etc yet an empty cursor results. Any help for a dummy (Me)?
 
Sort by date Sort by votes
Pat,

First point: Are you sure you have got your DELIMITED WITH and WITH CHARACTER clauses the right way round? The way I read it, you are using underscores as the enclosing characters (rather than the more common double-quotes), and pipes as the separator (rather than commas). I suspect you need to reverse those.

Also, are you happy that the text file is pure ASCII, that is, no special characters of any kind? If for any reason it has an embedded CHR(26), that could cause the problem you are seeing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
Anything simpler, tcLoadfile missing the path to the file?

Bye, Olaf.
 
Upvote 0 Downvote
Thanks for the quick response, but I still have no joy...
I have changed that line Mike every way I can think of and Olaf, I hard coded the filename into the (tcLoadFile) slot and still no joy.
Below is the first 3 lines of the download file. Maybe you can spot what I am missing.
Code: 
"WIT_NO"|"active"|"setupcudte"|"expiredte"|"last1"|"first1"|"n1_middle"|"last2"|"first2"|"n2_middle"|"attn"|"addr1"|"addr2"|"city"|"state"|"zip"|"Phone"|"snow_attn"|"snow_addr1"|"snow_addr2"|"snow_city"|"snow_state"|"snow_zip"|"Phone2"|"serial"
"I000248"|"1"|"2009-02-01 00:00:00"|"2016-03-31 00:00:00"|"DOE"|"JOHN"|"H"|"DOE"|"JILL"|""|""|"1111 HUNGRY HOLLOW BLVD"|""|"MYTOWN"|"IA"|"55555-5636"|"5554440865"|""|""|""|""|""|""|""|"70J37950"
"I000334"|"1"|"2012-09-21 00:00:00"|"2015-09-30 00:00:00"|"SMITH"|" LEO"|""|"SMITH"|"REBECCA"|""|""|"1424 COMPTON PL"|""|"YOURTOWN"|"IA"|"55555-2869"|"5553339481"|""|""|""|""|""|""|""|"10R77323"

I did look for other characters (CHR(26)) but can find none.
 
Upvote 0 Downvote
The strings in this are delimited with ", so use ", not _

Code: 
APPEND FROM (tcLoadFile) DELIMITED WITH " WITH CHARACTER |

Bye, Olaf.
 
Upvote 0 Downvote
The parts of the help topic on APPEND FROM, which are ambiguous:

DELIMITED WITH Delimiter
Indicates that character fields are separated by a character other than the quotation mark.
Click to expand...

should rather be
DELIMITED WITH Delimiter
Indicates that character fields are delimited by a character other than the quotation mark.
Click to expand...
In your case that's the normal quotation marks ".

DELIMITED WITH CHARACTER Delimiter
Specifies files that contain fields all enclosed by the character specified with Delimiter.
Click to expand...

should rather be
WITH CHARACTER Separator
Specifies files that contain fields all separated by the character specified with Separator.
Click to expand...
1. DELIMITED is not part of that clause and 2. it's about the Separator.

As your delimiter is the standard quoataion mark ", you can omit the DELIMITED WITH ", but you can't write APPEND FROM (tcLoadFile) WITH CHARACTER |, you can only skip the WITH ", so for just having a non standard separator you write APPEND FROM (tcLoadFile) DELIMITED WITH CHARACTER |. And that shortned version makes it hard to understand the | character is about the separator and not the delimiter...

Confused? Don't worry. The VFP team and the documetation team surely were, too.

Bye, Olaf.
 
Upvote 0 Downvote
>But Nothing in the Cursor
What are you doing now. It works for me to do either
APPEND FROM (tcLoadFile) DELIMITED WITH " WITH CHARACTER |
or
APPEND FROM (tcLoadFile) DELIMITED WITH CHARACTER |

At least just appending these three lines. Try it with your sample lines only. If you get no result with the whole file, there is something about it in later lines, perhaps.

Bye, Olaf.
 
Upvote 0 Downvote
Pat, I've tested Olaf's code with your data. It does work. I can see the three records - including the one that contains the field names, as per my previous post.

My conclusion is that there is something unexpected in the import file itelf. You say you have checked it for control characters. That's fine. But is it possible it uses a non-ASCII coding scheme, such as 16-bit characters?

If that's a possibility, you could try creating a new text file within VFP (MODIFY FILE) and pasting the data from your import file into it. You might also try using the VFP import wizard to import the file. I'm not suggesting that as the overall solution. But it might just highlight any problem with the actual physical file.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
I am really getting confused... I even copied from this site and made a new csv file to use. I tried both ways listed in your response and still no data in the cursor. When I step through it, it shows "lcError = .T." as soon as it executes the 'Append From' line of code and nothing goes to the cursor. I again copied the data to a new name and location, hard coded the filename into tcLoadFile with the same result. I am really stumped!
 
Upvote 0 Downvote
When I step through it, it shows "lcError = .T." as soon as it executes the 'Append From'
Click to expand...

What is lcError? You haven't mentioned that before.

Do you have some sort of error-trapping in place? If so, try disabling it, so that VFP will display an error message on the screen.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
lcError was used in a later (commented out) section of code used when the FTP site was connected. I have commented out all instances of the variable and still get the error and no cursor. This is turning into a Friday kind of thing! I used Mikes MODIFY FILE to create a new file then used that... no joy.
 
Upvote 0 Downvote
Sound like you have an error handling like ON ERROR lcERror = .T., which is simply suppressing any error. To suppress errors while testing and debugging code will just hide the most valuable info about the reason of failing code. Simply put ON ERROR in front of the append and just the normal error messages will appear.

Bye, Olaf.
 
Upvote 0 Downvote
I tried these commands:
Code: 
CREATE CURSOR WITCURSOR (WIT_NO C(8), ACTIVE C(1), SETUPCUDTE C(19), EXPIREDTE C(19), ;
	LAST1 C(30), FIRST1 C(30), N1_MIDDLE C(1), LAST2 C(30), FIRST2 C(30), N2_MIDDLE C(1), ;
	ATTN C(30), ADDR1 C(30), ADDR2 C(30), CITY C(20), STATE C(2), ZIP C(10), PHONE C(10), SNOW_ATTN C(30), ;
	SNOW_ADDR1 C(30), SNOW_ADDR2 C(30), SNOW_CITY C(30), SNOW_STATE C(2), SNOW_ZIP C(10), PHONE2 C(10), ;
	SERIAL C(8))

APPEND FROM x.x DELIMITED WITH " WITH CHARACTER |
and it worked fine for me. Maybe you don't have sufficient 'write' rights for your cursor. Check wherever your "tmp" or "temp" files are located.
Try using READWRITE at the end of your create cursor clause, or even CREATE TABLE instead of CREATE CURSOR somewhere on a drive where you know you have sufficient rights.



-Dave Summers-
[cheers]
Even more Fox stuff at:
 
Upvote 0 Downvote
I even copied from this site and made a new csv file to use
Click to expand...

Why would you make a new csv file. That ABSOLUTELY won't work with the code we're discussing here.

(CSV stands for "comma separated values", so the data are separated by a comma, not a pipe.)

Multiple people have said they've tried your CREATE CURSOR statement and your data as posted here and it worked. What aren't you telling us? [sadeyes]

Is it possible UAC is creating this file in a location you aren't expecting, so virtualization is making sure you don't get any data?
 
Upvote 0 Downvote
As mentioned above the code works fine.

Try the following to debug in a newly created vfp command window.

Code: 
close databases
create table t_1 (field1 c(8), field2 c(1))
append
* Add some three records and esc out
copy to t_2.txt sdf
append from t_2.txt sdf
browse    && Check if upto this point your table contains what it should

* then try your original file and original append command.
APPEND FROM x.x DELIMITED WITH " WITH CHARACTER |
browse && Check if you get something in the table
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SitesMasstec
  • Locked
  • Question
Update data in a Cursor 2
Replies
19
Views
2K
SitesMasstec
SitesMasstec
bharons
  • Locked
  • Question
Update Data SQL server Using Create Cursor VFP
Replies
13
Views
887
bharons
bharons
Filip Brnic
  • Locked
  • Question
Importing XML data into a cursor inside a form
Replies
7
Views
861
tomk3
tomk3
SitesMasstec
  • Locked
  • Question
How to order a column of data in a Grid
Replies
3
Views
558
EinTerraner
EinTerraner
J
  • Locked
  • Question
Insert data of Arduino on a plot 1
Replies
11
Views
693
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top