Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Help exporting records

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help exporting records

    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

  • #2
    Re: Help exporting records

    Yes
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

    Comment


    • #3
      Re: Help exporting records

      Originally posted by Tim Kiebert View Post
      Yes
      Any examples or direction would sure be appreciated.
      Thanks

      Comment


      • #4
        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?

        Comment


        • #5
          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

          Comment


          • #6
            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.
            Last edited by Tim Kiebert; 05-23-2010, 07:03 AM.
            Tim Kiebert
            Eagle Creek Citrus
            A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

            Comment


            • #7
              Re: Help exporting records

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

              Thanks!

              Comment


              • #8
                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?

                Code:
                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)

                Comment


                • #9
                  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.
                  Tim Kiebert
                  Eagle Creek Citrus
                  A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

                  Comment


                  • #10
                    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?

                    Code:
                    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

                    Comment


                    • #11
                      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?

                      Comment


                      • #12
                        Re: Help exporting records

                        Originally posted by Tom Cone Jr View Post
                        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

                        Comment


                        • #13
                          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:

                          Code:
                          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
                          Last edited by Tom Cone Jr; 05-23-2010, 09:04 PM.

                          Comment


                          • #14
                            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)
                            Last edited by Mark Williams; 05-23-2010, 09:19 PM.

                            Comment


                            • #15
                              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.
                              Mike
                              __________________________________________
                              It is only when we forget all our learning that we begin to know.
                              It's not what you look at that matters, it's what you see.
                              Henry David Thoreau
                              __________________________________________



                              Comment

                              Working...
                              X