Seemingly you do this often enough to warrant some effort.
You could consider a shadow table where in the fields are calculations. So let's say you have a Amazon table with a field named "Customer First Name" which would be calculated to equal "b_First" in your normal data base table. Etc., etc. Then just create the Excel file from the Amazon table. Naturally, I don't know how many fields you are dealing with and someone else may have another idea. However, I don't know of any way to change the field names during the output step such that you wouldn't have to open and touch the Excel file every time.
I want to export field contents as an excel file, save it as a .txt file and then feed it to Amazon and other websites.
What's the point of going through Excel? And what exactly is "a .txt file"? What format does it use?
Amazon and the other websites to whom I feed these reports to reports stipulate tab delimited text files. I can save it as a tab delimited file from excel. If there way I could do this and avoid losing the integrity of the data I'd love to know because this method is a bit cumbersome.
Good idea. I'll go with that as a last resort. Calculations for so many fields could slow down the database.
Amazon and the other websites to whom I feed these reports to reports stipulate tab delimited text files. I can save it as a tab delimited file from excel.
You could do one of the following:
1. Export your data directly to a tab delimited file. Then use either OS-scripting or a plugin to insert a header line into the exported file.
2. Export as XML, using a custom XSLT stylesheet to build the file you need - see, for example:
Option 2 looks good. I see the list of column headings in the style sheet. Is that the only thing I'd need to adapt? How does that list of headings match up with the correct columns? Would the script fail if the field export order was changed?
How does that list of headings match up with the correct columns?
You just need to make sure the number of headings matches the number of exported fields.
Would the script fail if the field export order was changed?
Not really. It just places a tab after each field in a record, except the last one. But if you change the field export order, you should also change the heading order to match.
I do quite a bit of exporting to tab-delimited files... and I take a slightly different approach.
I just write a script to collect all the data into a single variable, and the use a plug-in like Troi File to write the contents to a flat file with an extension of ".tab".
The script that collects the data first loads all the headings in the variable:
Set Variable $Data = "Heading 1" & Char( 9 ) & "Heading 2" & Char( 9 ) & "Heading 4" & Char( 13 ) & Char( 10 )
The "Char( 9 )" inserts a tab and the "Char( 13 ) & Char( 10 )" inserts a carriage return and a line feed to signal end of record.
Then the script loops thru the data records, appending the appropriate fields from each record:
Go To Record/Request First
Set Variable $Data = $Data & MyTable::Field1 & Char ( 9 ) & MyTable::Field2 & Char( 9 ) & MyTable::Field3 & Char( 9 ) & MyTable::Field4 & Char( 13 ) & Char( 10 )
Go To Next Record; Exit After Last
After collecting all the data, my script calls two Troi File functions to write the file to disk:
$FilePath = "C:/MyFile.tab"
Set Variable $Result = TrFile_SetDefaultFileSpec( "" , $FilePath )
Set Variable $Result = TrFile_SetContents( "" , $Data )
Just another option ... The downside is it relies on a plug-in (not so bad, really), but the upside is the process is very simple, very flexible, and very reliable.
This was the alternate method I was thinking about. The advantage is that multiple headers can be created and a case statement used to select the one needed. The OP said that there were multiple exports all to different places with different headers.
-- sent from my iPhone4 --
Beverly Voth wrote:
This was the alternate method I was thinking about. The advantage is that multiple headers can be created and a case statement used to select the one needed.
You can do the same thing with XML/XSLT, either by preparing different stylesheets and having the script select one, or by exporting a global field to tell the stylesheet which header to use.
-- sent from my iPhone4 --
Happy new year. I'm interested in using your converting my exporting as XML. I'm new to this. Will the XML method change it automatically to .txt? If not is it easy to change to .txt? Also is it possible to encode certain fields in ASCII?
Peter, the "method" for xml transformation by XSLT can be to: xml (another schema), text, or hmtl. Each of these gives a slight different export format. You can specify whatever extension you choose (.txt) when you create the XML export (manually or by script).
The "encode in ASCII"? do you mean you need something other than UTF-8 encoding in the file, then the answer is yes. Please let us know what you need. Do you have the fields formatted a certain way?
Hi Beverly, thanks for your speedy reply. Some of the fields are sent to web pages and I noticed that apostrophes are translated into their ASCII alternative when they are live on the web pages.
I also downloaded some of my content from the source code of some web pages. This is already ASCII encoded.