Alpha Video Training
Results 1 to 12 of 12

Thread: Importing Excel spreadsheet

  1. #1
    Rick Hanson
    Guest

    Default Importing Excel spreadsheet

    I have an Excel spreadsheet which is really a 7 field table of names, dates, etc. I need to import each spreadsheet into a master table that I have created that has the same field (column) structure as the spreadsheet. I have saved the spreadsheet in Excel version 4 to be able to imprort it into Alpha Five. When I try to import the spreadsheet I get the error message "Invalid number of fields". The number of fields in both the table and the spreadsheet are the same. Any ideas of what is wrong?

    Thanks,
    Rick Hanson

  2. #2
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default RE: Importing Excel spreadsheet

    Rick,

    Try and import to a new table and then append to your master table.

    RegardsKeith HubertAlpha Guild MemberLondon.KHDB Management Systems

  3. #3
    Member
    Real Name
    robert adler
    Join Date
    Apr 2000
    Location
    Boynton Beach, BL
    Posts
    591

    Default RE: Importing Excel spreadsheet

    Rick:

    Check the field names. The import from Excel will only take the first ten positions of the name. if the first ten are the same it can cause problems.

    I hope this helps.

    bob adler

  4. #4
    "Certified" Alphaholic
    Real Name
    Ed Barley
    Join Date
    Mar 2002
    Location
    Southern California
    Posts
    1,056

    Default RE: Importing Excel spreadsheet

    Hi Rick,

    We have a customer that sends us an excel spreadsheet, and we import it into alpha once or twice a week.

    You might try this, as it works in our case.

    After your data adjustments
    We format all the cells to text
    highlight A1
    SAVE AS DBF IV
    from there we import the dbf file into the import table.

    Here are a couple of threads to look at for ideas also:

    http://msgboard.alphasoftware.com/alphaphorum/read.php3?num=7&id=572&loc=0&offset=40&sortby=lastreply&direction=desc&thread=572

    http://msgboard.alphasoftware.com/alphaphorum/read.php3?num=7&id=786&loc=0&offset=0&sortby=lastreply&direction=desc&thread=786

    Regards Ed

  5. #5
    Rick Hanson
    Guest

    Default RE: Importing Excel spreadsheet

    Keith--

    Thank you for your reply. I was going to try that but I can't find how to append one table to another!! I'm sure there is some easy way to do this but I couldn't find it in the help files.

    Rick

  6. #6
    Rick Hanson
    Guest

    Default RE: Importing Excel spreadsheet

    Robert--

    Thank you for your reply. Do you mean there may be a problem if the first 10 characters are the same between the import table and the existing table field names or did you mean within the imported spreadsheet itself?

    Thanks,
    Rick

  7. #7
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Importing Excel spreadsheet

    Append Operations

  8. #8
    Rick Hanson
    Guest

    Default RE: Importing Excel spreadsheet

    Ed--

    Thanks for your prompt reply. I just got the info on doing an append operation which I think is what you are doing once the spreadsheet is converted into *.dbf format. I'm going to give it a try.

    Thanks,
    Rick

  9. #9
    Rick Hanson
    Guest

    Default RE: Importing Excel spreadsheet

    Stan--

    Thank you for finding the Append help files--I don't know why I didn't find them before when I searched the index--it was late at night?!

    I did use the Append operation after saving the spreadsheet as a *.dbf file but I did run into a couple strange (to me anyway) problems. The transaction table (being imported) has a field name "Account_#". I tried to duplicate this field name in the master table but it didn't work. After entering "Account_#" for the first field, when I went to the second line to enter the next field name, the "Account_#" switched to "Account__" (2 underlines and no "#"! Thus, my fix was to go to the transaction table and change "Account_#" to "Account" (which was the field name I settled on for the master table after the problem above). Does that make any sense?

    The reason I tried to match the same field names for both the master table and transaction table, is that when I go to the append table where transaction table field names are mapped to the master table field names (eg @customer2->customer_id), after mapping 3 field names, I get the error message "field is not recognized" when I go down to the next blank line to enter the next field name. Thus, I can't finish mapping all the field names. However, I discovered that if the master table and transaction table field names are the same, there is not a problem with the mapping. Does that make any sense?

    I have worked around these quirks by changing the transaction table field names to the same as the master table but this is not very convenient when you have multiple trables to append. Am I doing something wrong?

    Sorry I have rambled so long. Appreciate any help.

    Rick

  10. #10
    "Certified" Alphaholic glenschild's Avatar
    Real Name
    Glen Schild
    Join Date
    Apr 2000
    Location
    Frome, Somerset, UK
    Posts
    1,525

    Default RE: Importing Excel spreadsheet

    Rick

    And to make sure no data is truncated before saving in dbf format I first highlight the whole spreadshhet (clicking on the box in the top left hand corner of the sheet) and then double click on the first column separator (between columns A and B) this makes sure that each column is sized to fit.

    Regards

    Glen Schild

  11. #11
    Rick Hanson
    Guest

    Default RE: Importing Excel spreadsheet

    Glen--

    Thanks for your post...good idea...will incorporate that.

    Rick

  12. #12
    "Certified" Alphaholic
    Real Name
    Ed Barley
    Join Date
    Mar 2002
    Location
    Southern California
    Posts
    1,056

    Default RE: Importing Excel spreadsheet

    Hi Rick,

    I should have been more specific.

    The excel column heading names are simple in my case, all one word. And short.

    After making the .dbf, I do an append like Robert and Stan have told you to do. I already have a table waiting in the application with the field names exact to my excel file column headings, with the correct width and mine are C.

    From there I copy the data to another table that my reports are based on. We only use the data for print out reports and then zap the table and refill it again for the next data. This happens 2 or 3 times per week.

    In this case when we receive the excel file by email, and make the changes, the whold process maybe takes 5 minutes for the printed reports to come out of the printer. After it was set up, it is very easy to do.

    Hope this helps a little more

    Ed

Similar Threads

  1. Export to Excel spreadsheet
    By Ray Dean in forum Alpha Five Version 1
    Replies: 1
    Last Post: 07-18-2003, 11:42 AM
  2. Pointing to & opening an Excel spreadsheet
    By Roberta Stack in forum Alpha Five Version 5
    Replies: 2
    Last Post: 04-14-2003, 08:00 PM
  3. Importing from Excel V4
    By Ranny Robertson in forum Alpha Five Versions 2 and 3
    Replies: 2
    Last Post: 04-12-2002, 05:57 AM
  4. Importing 123 spreadsheet?
    By Bob Elliott in forum Alpha Five Version 4
    Replies: 1
    Last Post: 02-04-2002, 02:55 PM
  5. Importing Excel
    By Lane Beckes in forum Alpha Five Version 4
    Replies: 5
    Last Post: 08-21-2001, 06:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •