Help exporting records

[Mark Williams]

Fields in record
Name
Address
Date1
Time1
Comments1
Date2
Time2
Comments2
Date3
Time3
Comments3
Date4
Time4
Comments4
Date5
Time5
Comments5

A normal export file would be like this:

Name Address Date1 Time1 Comments1 Date2 Time2 Comments2 etc...

I need to create an export file like this:

Name Address Date1 Time1 Comments1
Name Address Date2 Time2 Comments2
Name Address Date3 Time3 Comments3
Name Address Date4 Time4 Comments4
Name Address Date5 Time5 Comments5

I am trying to accomplish a separate export record for each date(1-5).

Can this be done?

Thanks

[Tim Kiebert]

Re: Help exporting records

Yes

[Mark Williams]

Re: Help exporting records

Yes

Any examples or direction would sure be appreciated.
Thanks

[Tom Cone Jr]

Re: Help exporting records

Mark,

There may be a more elegant solution. However, first thought here is to write a script that steps through the records, parses each record, and then writes the field data to a text file, separating each line with a crlf pair.

Do you have some sample data you could post here?

Can you tell us the specific format you'd want in the resulting text file?

Can you confirm that you're actually talking about an export, and not a copy?

[Mark Williams]

Re: Help exporting records

Tom,
Thanks for the reply.

I am looking for the export to be in Excel format and it doesn't matter to me whether it is an export or copy operation.

I've attached the following:
Sample.zip - Sample table with a basic export operation saved
Sample1.xls - The result file from the above export operation
Desired Output.xls - The way I need to display the data

I appreciate any help!

Mark

[Tim Kiebert]

Re: Help exporting records

I thought you would never ask. :)

See attached
I have made the assumption that the export file is a text file of some sort.

The script Custom_Export operates against the sample table which is based on your description.

I also, as an example, re-arranged your data in a more normalized form.
The script Custom_Export2 operates against the Comments set.
The operation Exp_mapped operates against the mapped table comments_m

Edit: I did not see your last post before I posted.

[Mark Williams]

Re: Help exporting records

It didn't like the excel files so I zipped them and attached them to this message.

Thanks!

[Mark Williams]

Re: Help exporting records

Tim,
Thank you very much for your examples - it's been very helpful...

In your script 'custom_export' - how would you modify to export to an excel file instead of a text file?

dim tbl as P
dim tmpstr as C = ""
constant sp = " "

tbl = table.open("sample",FILE_RO_SHARED)
tbl.fetch_first()

while .not. tbl.fetch_eof()
	tmpstr = tmpstr + "Record " + tbl.Id + crlf()
	for i = 1 to 5
		tmpstr = tmpstr + tbl.Name + sp + tbl.Address + sp + eval("tbl.Date"+i) + sp + eval("tbl.Time"+i) + sp + eval("tbl.Comments"+i) + crlf()
	Next i
	tmpstr = tmpstr + crlf(2)
	tbl.fetch_next()
end while

tbl.close()

'msgbox(tmpstr)

filename = a5.Get_Path() + "\export.txt"

file.from_string(filename,tmpstr)

sys_open(filename)

[Tim Kiebert]

Re: Help exporting records

i would use a temp table as in the attachment. Process the the main table into the temp table. Base the normal export operation on the temp table.

[Mark Williams]

Re: Help exporting records

Tim,
I have since changed the time fields to short time format instead of character fields.

Could you tell me how I would have to modify the script to export the time fields to display as 02:15 am in excel?

dim tbl as P
dim tbltemp as P


tbl = table.open("sample",FILE_RO_SHARED)
tbltemp = table.open("sampletemp",FILE_RW_EXCLUSIVE)
tbltemp.zap(.t.)
tbl.fetch_first()


while .not. tbl.fetch_eof()
	for i = 1 to 5
		tbltemp.enter_begin()
		 tbltemp.name = tbl.Name
		 tbltemp.Address = tbl.Address
		 tbltemp.Date1 = eval("tbl.Date"+i)
		 tbltemp.Time1 = eval("tbl.Time"+i)
		 tbltemp.Comments1 = eval("tbl.Comments"+i)
		tbltemp.enter_end()
	Next i
	tbl.fetch_next()
end while

tbl.close()
tbltemp.close()

export.run("sampletemp")

Thanks,
Mark

[Tom Cone Jr]

Re: Help exporting records

Mark, doesn't this depend on what data type you select for the column in Excel? What have you tried? What results did you get?

[Mark Williams]

Re: Help exporting records

Mark, doesn't this depend on what data type you select for the column in Excel? What have you tried? What results did you get?

Tom,
When the field is defined as a short time field in A5 and I export it to an excel file using the above script, it exports each record as ,w%

I tried to format the column in excel as time and it didn't effect it.

Thanks

[Tom Cone Jr]

Re: Help exporting records

Tell us exactly how you're doing this. What's the target version of Excel? What's your goal? Do you need the column as "time", instead of character? Is an Excel "time" the same as an Alpha Five "short time"? Any chance you can post an updated data set for us to work with?

Mark, I was able to use the ADO Export genie to export short time field values as calc fields like this:

t2_start = left(cshorttime(start),2)+":"+substr(cshorttime(start),3,2)+":"+substr(cshorttime(start),5,2)

Where "start" is my short time field in my Alpha Five table. This gives a result in the spreadsheet in military format HH:MM:SS. Hope this helps.

-- tom

[Mark Williams]

Re: Help exporting records

Ok, I think I've been going about this all wrong. I've attached a sample table and I am trying to accomplish offering the client a report that looks like this:

Name Address Date_sent Time_Sent
Name Address Date1 Time1 Comments1
Name Address Date2 Time2 Comments2
Name Address Date3 Time3 Comments3
Name Address Date4 Time4 Comments4
Name Address Date5 Time5 Comments5

It can be in the form of a report (preferred), delimited file, excel spreadsheet or anything else that will allow me to sort by Date1 as the primary sort and Time1 as the secondary sort.

(This sample table is different from the original post as the time fields are now in short time format and I added the Date_Sent and Time_Sent fields that need to be included in the report)

Thanks for your help!
Mark

EDIT: I believe it needs to be in an excel file because all the dates would need to be in one column and all of the times in another column. This would allow it to be sorted by Date then Time (unless there is a way to do that with a report)

[MikeC]

Re: Help exporting records

Mark,
Generally, a report is simply another type of layout made to display your data, but has enhanced (read easier) methods for grouping and showing the data....so you can order however you want on a report and in several different ways in fact. I suggest you actually decide on what the end result is to be though as many times the suggestions of solutions will vary according to what is wanted and would have to be modified or changed for each way.