How do I get a memo field into a csv document

[StewartUK]

We are in the middle of migrating our database and I need to get my notes fields, which are memos, into a csv file.

How can I do that?

Thanks,

Stewart

 

[alvechurchdata]

Try using textmerge:

Set Textmerge On
Set Textmerge To c:\test.csv noshow

Scan
   \<<NotesField>>
Endscan

*-- Close the text file and stop merging
Set Textmerge To
Set Textmerge Off

In this snippet, the backslash means "output" and the double chevrons mean "evaluate this".

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

Stewart,

I wish there was an easy answer to this. As you know, memo (and general) fields are ignored by COPY TO and EXPORT, even if you add them to the FIELDS clause.

Your only options are to write the file in some other way, such as with SET TEXTMERGE, as per Geoff's suggestion, or STRTOFILE(), or low-level file I/O.

One other possibility would be to use Automation to write the data to Excel, and then programmatically save that as a CSV, but that's probably a lot more trouble than the other methods.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[StewartUK]

Thanks Geoff & Mike. I found that the textmerge idea is simplest.

Stewart

 

[cbasoz]

There is no way that you could write a memo to a CSV whatever method you use (unless by memo you mean single line text).

Cetin Basoz
MS Foxpro MVP, MCP

 

[r0gerc]

Geoff Franklin's snippet does insert a memo field into a .csv file. I have used this method before but if I remember correctly the memo would need any commas removed first, though.

Roger

 

[alvechurchdata]

I agree with Cetin Basoz that you'll need to take care with the contents of the memo file. If the text in the memo stretches across multiple paragraphs then you'll have to talk to the people who control the csv format and ask them how they plan to represent a carriage return/line feed in their file.

If you need to have commas in the memo field then the csv needs to have each text field delimited with quotes.

A lot depends on what the reader of the data means by "csv file".


Geoff Franklin

http://www.alvechurchdata.co.uk

 

[cbasoz]

Really?
Try putting this message in a memo field and getting into a CSV.
If you can then post back the content of CSV.
Thanks.


Cetin Basoz
MS Foxpro MVP, MCP

 

[alvechurchdata]

We'll have to do some preprocessing. Perhaps html would be easiest:

STRTRAN(memoField, CHR(13)+CHR(10), "<br />")

gives us this:
[TT]
Really?<br />Try putting this message in a memo field and getting into a CSV.<br />If you can then post back the content of CSV.<br />Thanks.
[/TT]
As I said, there needs to be a discussion with the owners of the csv as to how they want to represent a CR/LF.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[cbasoz]

Instead of inventing ways to represent CR, LF, CRLF I would directly deal with the main problem and use OLEDB or ODBC to "migrate" the data. Or XML, but not CSV.

I don't think saying "migrate" StewardUK meant that from now on he wanted to use CSV based data. If he really meant it then the problems he would have are much more then finding a way to represent CR/LF.

Cetin Basoz
MS Foxpro MVP, MCP

 

[StewartUK]

Cetin is correct - we are changing our CRM database from the one I designed and purchasing another system.

Therefore I need to output the data from the current system in to csv files so that it can be imported in to the new system.

Stewart

 

[cbasoz]

Stewart,
You are migrating your data to where? What I am saying is you don't need to export to CSV to import into new system if you are using one of well known backends.

CSV is not a good format to do export/import of data. It works for plain text with some limitations. On the other hand database tables might have CSV incompatible content (like Memo, blob and some data might have problems like date/datetimes). IMHO do not use CSV in between but do a direct OLEDB/ODBC transfer. If you tell us from/to databases then we might have much better solutions.

Cetin Basoz
MS Foxpro MVP, MCP

 

[StewartUK]

Thanks for the offer Cetin. As it happens the back end of the new system in SQL Server Express. There are no blob or general type fields.

I know I could have simply worked out the mapping from my data structure to theirs and transferred the data straight in with ODBC (which I partly did when testing), but the whole situation is complicated, the provider requested csv files and in this case I am looking for the simplest way.

The textmerge method certainly appears to have created the csv files correctly and it's now up to the provider to work out the mapping & migration.

Thanks everyone,

Stewart

 

[cbasoz]

Oh SQL server!
Then here is a simple import for the requestor (one of the many options):

-Create a linked server to VFP. ie: You can do it in code from SQL server management studio like this

EXEC sp_addlinkedserver 
    @server = N'VFP_SERVER',  -- Your linked server name here 
    @srvproduct=N'Visual FoxPro 9',  -- can be anything 
    @provider=N'VFPOLEDB', 
    @datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\Samples\data\testdata.dbc"'

-Execute select into/insert into queries using VFP tables via linked server.ie:

select *
into vfpCustomers
from OpenQuery(VFP_SERVER,
'select 
  cast(evl(Shipped_on, null) as date) as shippedOn, 
  order_date as OrderDate, 
  Company,
  cust_id as CustomerID
from orders
inner join customer
on customer.cust_id = orders.cust_id')

Cetin Basoz
MS Foxpro MVP, MCP

 

[cbasoz]

Note: The query inside the OpenQuery() is a VFP query (VFP syntax).

Cetin Basoz
MS Foxpro MVP, MCP