Copy to... overcome memo field restriction 1

[keepingbusy]

All

(Version 9)

I recently changed a field in a table from a character field (called column3) to a memo field. Part of the code used, copies the contents of the table into a CSV file:

lnLoop = CEILING(RECCOUNT()/70000)
FOR lnFor = 1 TO lnLoop
  lcFileName = [products_description]+TRANSFORM(lnFor)+[.CSV]
  COPY TO (lcFileName) NEXT mprod DELIMITED WITH CHARACTER ";"
NEXT

As the data in table (products_description.dbf)field colum3 contains track listings, this can be more than 254 characters so a character field wont do.

The VisFox 9 help file states:

COPY TO Command........

FIELDS FieldList
Specifies which fields are copied to the new file. If you omit FIELDS FieldList, all fields are copied to the file. If the file you are creating is not a table, memo fields are not copied to the new file, even if memo field names are included in the field list.

As mentioned above, the data for column3 contains track listings that are more often than not, over 254 characters so a character field wont do.

How do I overcome this by getting the contents of the memo field into the CSV file?

Thank you

Lee

 

[dgrewe]

Lee,

Right out of VFP Help "Copy To" sits

FIELDS FieldList
Specifies which fields are copied to the new file. If you omit FIELDS FieldList, all fields are copied to the file. If the file you are creating is not a table, memo fields are not copied to the new file, even if memo field names are included in the field list.

If you want a memo field in a CVS file you will have to do it manually

local lnHandle as integer
local lcString as Char
local lcDelimit as char
lcDelimit=';'
lnHandle=fcreate(filename)
scan ....
for x = 1 to fcount()
lcString=&Field(x) && if not Char Convert it
=fwrite(lnHandle,lcString)+lcDelimit
......
endfor
=fputs(lnHandle,"")
endscan
=fclose(lnHandle)




David W. Grewe (Dave)

 

[keepingbusy]

Dave

Thank you for the post. I did notice the section in the help file about memo fields and posted it at the start of the thread.

I have not come across the code you have shown, so please can you give me some pointers on what goes where and why?

Will your code affect what I have already written and can this be used in conjunction to add the memo field as well as the other fields I am copying to the CSV file?

The table name is products_description.dbf and the field names (all character) are column1, column2 etc with the memo field being column3.

I'm not asking for the sollution, just some direction.

Thank you Dave

Lee

 

[ilyad]

You can scan your file and write to a file using either strtofile or low-level file functions as shown above.

Another way would be to create Excel file and then save it as CSV. There is a sample code of creating file in Excel with memo fields by Cetin Basoz which can be found on UT.

 

[dgrewe]

OK here goes.
From your code example you want to scan a table that has 3 fields. You want to make a CSV file for each Record in the file or 70,000 CSV files. At least that is what your code is doing.

So Since you can not follow the code example #1 here is the EXACT code for you.

local lcString as char
local lnLoop as Integer
local lcFileNAme as char
local lnHandle as Integer
local lcDir as Char

lcDir = 'C:\output\' && or where ever you want the files

USE products_description
lnLoop = CEILING(RECCOUNT()/70000)
FOR lnFor = 1 TO lnLoop
lcFileName=lcDir+[products_description]+TRANSFORM(lnFor)+[.CSV]
lnHandle=fcreate(lcFilename)

* I assume columns1&2 are Char or memo fields
lcString=alltrim(Column1)+";"+alltrim(column2)+";"+alltrim(column3)

* Note there is a 4k size limit on the size of a char string

=fputs(lnHandle,lcString)
=fclose(lnHandle)
endfor
use

That code will create your 70,000 ";" delimited text files assuming I did not make a typing error and the information you gave is is correct.

David W. Grewe (Dave)

 

[ilyad]

Should it be comma delimited? And I hope it's not 70000 files, but just a few. I actually don't understand the part with ceiling at all.

 

[dgrewe]

Guess since I made one string out of all 3 files you could use strtofile() now

for lnFor = 1 to lnLoop
lcFileName=lcDir+[products_description]+TRANSFORM(lnFor)+[.CSV]

* I assume columns1&2 are Char or memo fields
lcString=alltrim(Column1)+";"+alltrim(column2)+";"+alltrim(column3)

* Note there is a 4k size limit on the size of a char string

=strtofile(lcString,lcFileName)

endfor


David W. Grewe (Dave)

 

[ilyad]

Where did you get 4K size limit of the character string? The limit of the command is 8192 and the strings are basically limited by available memory, but usually the limit is about 16MB.

 

[keepingbusy]

Dave

From your code example you want to scan a table that has 3 fields. You want to make a CSV file for each Record in the file or 70,000 CSV files. At least that is what your code is doing.

The code is taking 70000 records at a time from a table that usually contains about 160000. The reason for this is because phpmyadmin upload has a file limit of 8mb with csv files. (There are two other tables but if I get this one right, I can sort the rest).

I am out of the office today so I'll try this later on and post back.

Ilyad: Thank you for your posts

Lee

 

[dgrewe]

Ilyad,

I looked it up, Your right the limit is 8k not 4k.

Thanks


David W. Grewe (Dave)

 

[keepingbusy]

David

Had a chance to try the code and added the additional fields in the table. It now looks like this:

for lnFor = 1 to lnLoop

  lcFileName=lcDir+[products_description]+ ;
    TRANSFORM(lnFor)+[.CSV]

  lcString=alltrim(prod_id)+";"+alltrim(Column1)+";"+ ;
    alltrim(column2)+";"+alltrim(column3)+ ;
    alltrim(Column4)+";"+alltrim(column5)+";"+ ;
    alltrim(column6)+alltrim(Column7)+";"+alltrim(column8)

  =strtofile(lcString,lcFileName)

endfor
use

The code adds all the fields in a single string (as required and in the correct format) but it only adds one record to the CSV file.

Any suggestions?

Thank you
Lee

 

[ilyad]

You need to use scan loop to put all records into the file, e.g. something like:

#define MAX_SIZE 70000
nLoop = CEILING(RECCOUNT()/MAX_SIZE)
lnStart = 1
lnEnd = 70000
go top
for lnFor = 1 to lnLoop

  lcFileName=lcDir+[products_description]+ ;
    TRANSFORM(lnFor)+[.CSV]
  scan while between(recno(),m.lnStart, m.lnEnd)
  lcString=alltrim(prod_id)+";"+alltrim(Column1)+";"+ ;
    alltrim(column2)+";"+alltrim(column3)+ ;
    alltrim(Column4)+";"+alltrim(column5)+";"+ ;
    alltrim(column6)+alltrim(Column7)+";"+alltrim(column8)
   
  =strtofile(lcString,lcFileName,1)
  endscan
  lnStart = m.lnStart + m.lnEnd + 1
  lnEnd = m.lnStart + MAX_SIZE  
endfor
use

 

[dgrewe]

Creating one file per record is what your sample code did in the very first posting of this thread.

If you want the all in one file then

local lcString as char
local lnLoop as Integer
local lcFileNAme as char
local lnHandle as Integer
local lcDir as Char

lcDir = 'C:\output\' && or where ever you want the files
lcFileName=lcDir+[products_description]+[.CSV]
lnHandle=fcreate(lcFilename)

USE products_description
lnLoop = CEILING(RECCOUNT()/70000)
FOR lnFor = 1 TO lnLoop
lcString=alltrim(prod_id)+";"+alltrim(Column1)+";"+ ;
alltrim(column2)+";"+alltrim(column3)+";"+ ;
alltrim(Column4)+";"+alltrim(column5)+";"+ ;
alltrim(column6)+";"+alltrim(Column7)+";"+alltrim(column8)
=fputs(lnHandle,lcString)
endfor
=fclose(lnHandle)
use

PS, you forgot a couple of delimiters between columns.


David W. Grewe (Dave)

 

[ilyad]

Hi David,

Take a closer look at the original code and my code. Basically, the thread originator wants to create few text files each with 7000 records. I wrote the solution from the top of my head, so it may require some changes to make it work, but it should give the idea.

 

[dgrewe]

iLyad,
OK, That is what I get for answering questions after 12 hours of working. Your right again.


David W. Grewe (Dave)

 

[ilyad]

No problem <g> I did nothing today except talking with the users, got to go home earlier...

 

[keepingbusy]

David

Take a closer look at the original code and my code. Basically, the thread originator wants to create few text files each with 7000 records

ilyad is right on this one!

The table that contains the information can in some circumstances have around 160,000 records. As there is a limit of 8megs upload with phpmyadmin (with our hosting) we had to reduce the size of the CSV file. Another tek-tips user helped us along with some of the code I posted at the start of the thread (Thank you for pointing out the missed delimeters)

ilyad
Problem solved (Thank you)
Here is the final code that did the job:

lnLoop = CEILING(RECCOUNT()/70000)
lnStart = 1
lnEnd = 70000
go top
for lnFor = 1 to lnLoop
  lcFileName=lcDir+[products_description]+ ;
    TRANSFORM(lnFor)+[.CSV]
  scan while between(recno(),m.lnStart, m.lnEnd)
  lcString=alltrim(prod_id)+";"+alltrim(Column1)+";"+ ;
    alltrim(column2)+";"+alltrim(column3)+";"+ ;
    alltrim(Column4)+";"+alltrim(column5)+";"+ ;
    alltrim(column6)+";"+alltrim(Column7)+";"+alltrim(column8)
  =strtofile(lcString,lcFileName,1)
  endscan
  lnStart = m.lnStart + m.lnEnd + 1
  lnEnd = m.lnStart + 70000  
endfor

My sincerest thanks to both of you for posting your suggestions.

Lee

 

[ilyad]

Always glad to help <g>

 

[keepingbusy]

Guys

Just ran a test on this code and noticed it is only creating a single CSV file with the required records (70,000)

The initial code I submitted looked like this:

lnLoop = CEILING(RECCOUNT()/70000)
FOR lnFor = 1 TO lnLoop
  lcFileName = [products_description]+TRANSFORM(lnFor)+[.CSV]
  COPY TO (lcFileName) NEXT 70000 DELIMITED WITH CHARACTER ";"
NEXT

This produced a CSV file called products_description1.csv and any subsequent records after that were saved in further CSV files for example: products_description2.csv and products_description3.csv etc

Can you please tell me why it is stoppping at 70000 and only creating the one CSV file?

Thank you
Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[ilyad]

Put a break point right after the endscan and see, what the code is doing and what is current recno()