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
Devcon 2020 Goes Virtual! LEARN MORE >

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

field type for product number won't import correctly

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
    #1

    field type for product number won't import correctly

    I'm having problems importing Excel files into A5v9. I have Excel 2000. Back when I was using A5v6 and A5v7 I learned to save my excel files as Excel Version 4 before trying to import into Alpha Five. I also learned to use 8-character field names.

    Now, with Version 9 I have the Alpha DAO import and it is supposed to support all Excel files mush better than the previous ADO import genie. I am still using Excel 2000. No matter if I save as Excel version 4 or keep the files in the Excel 2000 version I can't get the import genie to give me the correct field type.

    I have some price list files where the Product_Number field is set to "Text" in the Excel file. When I use the default import settings in the Alpha DAO genie that field is set to "Exponent/Numeric". Even when I re-set the default field type settings for that field to "Character" using the genie I get the same result. The result in all cases using the import genies is that this field is blank (or sometimes a zero) whenever the product number is a non-numeric entry.

    A product number like "XYZ001" will not come over in the import. It will be blank, or a zero. A product number like "0001234" will come over as "1234". A product number like "5678123" will come over fine, as "5678123".

    When I use the CSV/ASCII import genie I can set the field types and everything comes over fine. But can I set up a CSV operation in the control panel? I haven't been able to figure it out. I can use the Alpha DAO or the ADO import genies and everything seems okay accept for this one problem. Is this a problem with the genie, my version of Excel, or the ODBC driver?

    I've been reading until my eyeballs are burning and I've been experimenting all day. Sure would be nice if I could get the Alpha DAO genie to do what I want. Where do I go next?

    Strange that I can use the ASCII import genie and set the field types for my prices to numeric but I can't use the DAO genie to set my product number field to character.

    Please, help. Thank you,
    Sean
    Tags: None
    #2
    Re: field type for product number won't import correctly

    I've given up trying to import directly from an Excel file. I figured out how to save an operation for importing using a CSV file. This seems to work.

    There must be something that Excel does that A5 can't handle in some cases. I'm not smart enough about these things to figure it out. Seems to me the AlphaDAO should work on Excel2000 files but I can't make it work.

    Now my approach for importing my price lists is to open the Excel file in OpenOffice Calc and export as a CSV file. Then I use the ASCII Import genie and save as an operation.

    Sean

    Comment

      #3
      Re: field type for product number won't import correctly

      Alpha DAO can over ride a field definition.

      Look at this attached image.
      Al Buchholz
      Bookwood Systems, LTD
      Weekly QReportBuilder Webinars Thursday 1 pm CST

      Occam's Razor - KISS
      Normalize till it hurts - De-normalize till it works.
      Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
      When we triage a problem it is much easier to read sample systems than to read a mind.
      "Make it as simple as possible, but not simpler."
      Albert Einstein

      http://www.iadn.com/images/media/iadn_member.png

      Comment

        #4
        Re: field type for product number won't import correctly

        Thanks Al,

        Yes, I found that and thought it would be my solution. But the records still imported at zeros in that field when the product number did not start with a number.

        I think there is some weird thing in the Excel file that A5 doesn't like.

        The CSV import works. I can live with that.

        Thanks,
        Sean

        Comment

          #5
          Re: field type for product number won't import correctly

          Since you have letters in your product fields, you can only bring that into a character field.

          A target numeric field will act as you've indicated when posting a character field into it..
          Al Buchholz
          Bookwood Systems, LTD
          Weekly QReportBuilder Webinars Thursday 1 pm CST

          Occam's Razor - KISS
          Normalize till it hurts - De-normalize till it works.
          Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
          When we triage a problem it is much easier to read sample systems than to read a mind.
          "Make it as simple as possible, but not simpler."
          Albert Einstein

          http://www.iadn.com/images/media/iadn_member.png

          Comment

            #6
            Re: field type for product number won't import correctly

            Thanks Al,

            Yes, I understand. In fact, I am trying to import a column that is formatted as text in the Excel file into a character field in A5. That is why I am so confused as to why it is not working.

            When I export as CSV and import the CSV file everything comes over fine. Only when I try to import from the Excel file do I have this problem. I figure there is something in the way the Excel file is wrapping the data in this column. It says it is formatted as text but A5 seems to think differently.

            Thanks,
            Sean

            Comment

              #7
              Re: field type for product number won't import correctly

              Can you send a small example of data to me?

              Something isn't making sense....
              Al Buchholz
              Bookwood Systems, LTD
              Weekly QReportBuilder Webinars Thursday 1 pm CST

              Occam's Razor - KISS
              Normalize till it hurts - De-normalize till it works.
              Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
              When we triage a problem it is much easier to read sample systems than to read a mind.
              "Make it as simple as possible, but not simpler."
              Albert Einstein

              http://www.iadn.com/images/media/iadn_member.png

              Comment

                #8
                Re: field type for product number won't import correctly

                I just started having the same problem when some data values changed. I tried many combinations of specifying the "id" column as text in Excel, but there is a problem when converting to dbf. I am going to use the intermediate step of CSV to solve the problem.

                Here is what I found.
                Specifying cell format of "text" vs. "General" makes no difference when saving as DBF. Excel seems to just look at the value when deciding what to do with it. If all values are the same "type" (character or numeric) then they export from Excel corretly and ADO override settings can be used to get format you want in Alpha. We know that dbf format was dropped from Excel Save As in Office 2007, even though older formats like SYLK were retained. Maybe they had "issues". Using Save As in Excel 2003 gives the same bad results, so the problem seems not to be with Alpha.

                Attached is a sample file if anyone has more insight.

                Bill.

                Comment

                  #9
                  Re: field type for product number won't import correctly

                  Today in the stranger than truth segment of our show, we find that the legacy import operation works, but Alpha DAO doesn't.( or after playing with it, I didn't make it work...)

                  It does require that you change the import field type to character in the legacy import operation (suggested thought it should be numeric)

                  Sounds like a bug report...
                  Al Buchholz
                  Bookwood Systems, LTD
                  Weekly QReportBuilder Webinars Thursday 1 pm CST

                  Occam's Razor - KISS
                  Normalize till it hurts - De-normalize till it works.
                  Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
                  When we triage a problem it is much easier to read sample systems than to read a mind.
                  "Make it as simple as possible, but not simpler."
                  Albert Einstein

                  http://www.iadn.com/images/media/iadn_member.png

                  Comment

                    #10
                    Re: field type for product number won't import correctly

                    This is the response I got from Selwyn.
                    I am not sure that it is a bug.
                    If you are using AlphaDAO, then you can't have a column of data in which some of the rows have numeric values and some have character values, as your spreadsheet appears to have.

                    the 'classic' import is more forgiving, but the alphaDAO goes through the Microsoft MDAC and it is probably much less forgiving.
                    So I will have to avoid AlphaDAO for this type of import unless I am certain there is no variability in the import data.

                    Bill.

                    Comment

                      #11
                      Re: field type for product number won't import correctly

                      An update. From a thread in Features Request forum I found a way to solve the problem in Excel.

                      We know that formatting the column as text does not solve the problem. Instead use the menu item Data -> Text to Column wizard. Step through the 3 pages and select Text on page 3. This does the equivalent of putting an apostrophe at the beginning of numeric cells, without the manual hassle. Then the entire column will import into Alpha as text.

                      Using CSV as an intermediate file is the other alternative.

                      Bill.

                      Comment

                      Previous template Next
                      Working...
                      X