My client has asked me to export specified data to a CSV file That's the easy part. I have a CSV file with a header and 500 detail lines.
Now he has asked that Column 1, Row 1, (the header) MUST be called *contactname Column 7, Row 1, (some details) MUST be called *PAYDUEDATE ....and a few others
As you can see, this is more than 10 characters. I need some help here. Whats the easiest way to automate this?
On Wed, Apr 13, 2016 at 8:28 PM, Sytze de Boer sytze.kiss@gmail.com wrote:
My client has asked me to export specified data to a CSV file That's the easy part. I have a CSV file with a header and 500 detail lines.
Now he has asked that Column 1, Row 1, (the header) MUST be called *contactname Column 7, Row 1, (some details) MUST be called *PAYDUEDATE ....and a few others
As you can see, this is more than 10 characters. I need some help here. Whats the easiest way to automate this?
It's pretty easy actually...
Get your data, build a string with it or send to file then FILETOSTR the stuff, create your header line, append the data string, write to CSV file with the name you want.
Matt, thank you for that quick and great guidance. I think I may have to work out how to make that top line greater than 255 chars, but I'm working on it..... Text to myvar, right?
On Thu, Apr 14, 2016 at 3:32 PM, M Jarvis brewdaddy@gmail.com wrote:
On Wed, Apr 13, 2016 at 8:28 PM, Sytze de Boer sytze.kiss@gmail.com wrote:
My client has asked me to export specified data to a CSV file That's the easy part. I have a CSV file with a header and 500 detail lines.
Now he has asked that Column 1, Row 1, (the header) MUST be called *contactname Column 7, Row 1, (some details) MUST be called *PAYDUEDATE ....and a few others
As you can see, this is more than 10 characters. I need some help here. Whats the easiest way to automate this?
It's pretty easy actually...
Get your data, build a string with it or send to file then FILETOSTR the stuff, create your header line, append the data string, write to CSV file with the name you want.
-- Matt Jarvis Eugene, Oregon USA
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
On Wed, Apr 13, 2016 at 9:19 PM, Sytze de Boer sytze.kiss@gmail.com wrote:
Matt, thank you for that quick and great guidance. I think I may have to work out how to make that top line greater than 255 chars, but I'm working on it..... Text to myvar, right?
http://leafe.com/reportAbuse/CAG1nNy--MOhyDUr=AwN_WL14-3nkGhR-eRLPnSiaai3jaawYsA@mail.gmail.com
I always used FILETOSTR() but whatever scoops up the contents of a file and into a string var should work. You may have to parse the file or string to do some line breaks for each record, but it's pretty straight forward to figure out.
CSV doesn't care about 255 max length as far as I remember. I think you can go to 1024 if you want.
Dang, it's been a while since I've done this but that's how I remember things working...
Read in each line using FOPEN, etc. Write back the first line as required then write back the rest as they are. Haven't got access to documentation so can't give correct syntax
John Weller 07976 393632 01380 723235 Sent from my iPhone
On 14 Apr 2016, at 04:28, Sytze de Boer sytze.kiss@gmail.com wrote:
My client has asked me to export specified data to a CSV file That's the easy part. I have a CSV file with a header and 500 detail lines.
Now he has asked that Column 1, Row 1, (the header) MUST be called *contactname Column 7, Row 1, (some details) MUST be called *PAYDUEDATE ....and a few others
As you can see, this is more than 10 characters. I need some help here. Whats the easiest way to automate this?
-- Kind regards, Sytze de Boer
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
#define _crlf chr(13) + chr(10) set textmerge on noshow set textmerge to mycsvfile.csv select mytable \header1,header2,header3,header_whatever<<_crlf>> scan <<field1>>,<<field2>>,<<field3>>,<<field4>> endscan set textmerge off set textmerge to
Sytze de Boer wrote on 2016-04-13:
My client has asked me to export specified data to a CSV file That's the easy part. I have a CSV file with a header and 500 detail lines.
Now he has asked that Column 1, Row 1, (the header) MUST be called *contactname Column 7, Row 1, (some details) MUST be called *PAYDUEDATE ....and a few others
As you can see, this is more than 10 characters. I need some help here. Whats the easiest way to automate this?
Sytze,
If you are lucky enough to be able to use the COPY TO ... TYPE CSV, it may be easier than you think:
CREATE CURSOR csvoutput (contname c(30)) INSERT INTO csvoutput VALUES ("Paul") INSERT INTO csvoutput VALUES ("John")
SELECT contname as ContactName FROM csvoutput INTO CURSOR finaloutput
COPY TO sytzetest TYPE CSV
MODIFY FILE sytzetest.csv NOWAIT
Tracy Pearson PowerChurch Software
I'm glad I posted the question. The feedback has taught me much. Thank you all.
On Fri, Apr 15, 2016 at 4:31 AM, Tracy Pearson tracy@powerchurch.com wrote:
Sytze de Boer wrote on 2016-04-13:
My client has asked me to export specified data to a CSV file That's the easy part. I have a CSV file with a header and 500 detail lines.
Now he has asked that Column 1, Row 1, (the header) MUST be called *contactname Column 7, Row 1, (some details) MUST be called *PAYDUEDATE ....and a few others
As you can see, this is more than 10 characters. I need some help here. Whats the easiest way to automate this?
Sytze,
If you are lucky enough to be able to use the COPY TO ... TYPE CSV, it may be easier than you think:
CREATE CURSOR csvoutput (contname c(30)) INSERT INTO csvoutput VALUES ("Paul") INSERT INTO csvoutput VALUES ("John")
SELECT contname as ContactName FROM csvoutput INTO CURSOR finaloutput
COPY TO sytzetest TYPE CSV
MODIFY FILE sytzetest.csv NOWAIT
Tracy Pearson PowerChurch Software
[excessive quoting removed by server]
Tracy is right, a SQL SELECT is not limited to 10 character field names, as long as it's going INTO CURSOR. You are limited to 10 character names in a free table.
Fred
On Wed, Apr 13, 2016 at 8:28 PM, Sytze de Boer sytze.kiss@gmail.com wrote:
My client has asked me to export specified data to a CSV file That's the easy part. I have a CSV file with a header and 500 detail lines.
Now he has asked that Column 1, Row 1, (the header) MUST be called *contactname Column 7, Row 1, (some details) MUST be called *PAYDUEDATE ....and a few others
As you can see, this is more than 10 characters. I need some help here. Whats the easiest way to automate this?
-- Kind regards, Sytze de Boer
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
-
Alan Bourke -
Fred Taylor -
John Weller -
M Jarvis -
Sytze de Boer -
Tracy Pearson