Need help Importing files (Excel)into Alpha5

[vaxennet]

I need to work with a database of my suppliers products. This record is supplied to me as a Excel or CSV file. I am trying to get this into Alpha5 to work with it.I have tried everything and still i cannot get it done properly. I hope it can actually be done and someone can help me.

I have tried saving the Excel file as CSV, Excel Worksheet, DBF and Excel 4.0. I get mixed result which are allways unacceptable.

The main problem is that this file has a discription field (usually in HTML). It is absolutely essential that the complete field be imported, or I will have end up editing them singularly. This file has over 3000 record. So i cannot accept this option.

For starters, when i save the file as Excel 4 (as required by Alpha5)the conversion cuts off a part of the discription. So this narrows my option of file formats to work with to CSV, DBF, and excel worksheet format.

first i tried USE IMPORT GENIE. > File type > EXCEL

SPREADSHEET (4.0). Then edit the field structure before

completing, I would find that I have a pretty decent

result, except that I have to contend with the Alpha 5

Excel 4.0 limitation. Which inherited the cut off

discription field. Best result so far, but I need the

full description field imported, so I will not have to

edit 3000+ records


next i tried USE IMPORT GENIE. > File type > ASCII text

file. > import data into a new table > Character

delimited format > etc, etc This is the worst one. No

record imported plus Alpha 5 locks up and I have to do a

ctrl/Alt/Delete to continue to work on my computer

then i try saving file in dBase 4 file. i use the Add

Table/Set which appears to be easy and works fine

(imports all the records)until I realize that pretty

much all fields are cut off. The discription field would

only be little more than 10 characters, as well as all other fields-This is totally unacceptable

use import ODBC WITH regular EXCEL worksheet > Next >

SELECT EXCEL> SELECT FIELDS>REFRESH qUERY> CLICK ON NEXT
(ERROR: Convert: unable to open *\*\*\masterData.dbf)
(ERROR: Script: Template: Line 1, unable to find or open

memo file)

Also: use import ODBC WITH EXCEL4 > Next > SELECT EXCEL>

SELECT FIELDS>REFRESH qUERY> CLICK ON NEXT

(ERROR: THE PROCESS CANNOT ACCESS THE FILE BECAUSE IT IS

BEEN USED BY ANOTHER PROCESS)
OF COURSE IT ONLY IMPORTS 720 OF 3000+ RECORDS. This is

no use to me anyhow because it imports records with the

discription field cut short

Nothing seems to work. I am desperate. Can someone please tell me how to do this.

Thanks

[Al Buchholz]

Let Excel save as a dbf.

An easier way to accomplish this, is in Excel - make the column headings legal dbase fieldnames and then let Excel save to a dbf format.

Then add that table to an Alpha database.

Is the description field more than 255 characters long? If so, you'll need to save in a memo field in A5. In that case, use the A5 import and adjust the sizes of the fields coming in.

When I import Excel tables, my first choice is to let Excel save as a dbf. Second is to save as a csv, then import that into A5 with adjusted field sizes or types.

Don't forget to close the file in Excel before trying to read with Alpha.

[vaxennet]

Thanks
Sounds like a start, but how do I get the Excel column heading a legal dBase fieldnames. I do not know what are legal dbase fieldnames.

Yes the discription fields are definately more than 255 characters

as my post mentioned, i tried saving as dbf but i do not remember it giving me an option to adjust field sizes, but i will try it again and this time to be very careful

regards

[Tom Cone Jr]

Search Alpha Five Help for

"Basic Concepts and Terminology"

include the quote marks in your search string.

Use field names that meet the recommendations you'll find there.

-- tom

[vaxennet]

Absolutely frustrated!

Thanks, but it did not help much. I found 3 references. 2 dwelt with security and the other about something unrelated to my problem. My problem is, I am simply trying to import a Excel file into Alpha5. If I cannot get beyond that, I am worried, this investment is a loss. Alpha software pitch it as something for non-programmers like myself. The future does not bode well for me if I cannot even import the Excel file. I am probably not stupid, because I did import it into Microsoft Access - took less than 10 seconds! I am really dissapointed. Thanks for your suggestion though.

regards

[Ed Barley]

Look In the code Archive

Look in the code archive, there are a few posts that deal with this issue.

In one of my applications, I change an excel file to a DBF IV, and then import the DBF IV into a temp table in Alpha. From there a couple of reports are printed with the imported information. It is very easy, but I do not deal with field descriptions or width that are more than 255 char.

You say 3000 records, how many fields are in each record? Quite a few? So that it is not possible you could change the descriptions for the import?

Ed

[vaxennet]

Hi
I see nothing in the archive that address this issue.

yes, file is 20 fields. Cannot change because i need all the characters in the fields. But i am just curious as to why Microsoft Access does this quite easily and I cannot get it done in Alpha 5. I have been working at this for several weeks now and everything i do report some errors.

[Tom Cone Jr]

Headley,

I'm not sure why you're struggling so.

Here's the information on table and field names, directly from the help file.

Basic Concepts and Terminology

Information in an Alpha five database is stored in one or more files called tables. Each table consists of a multiple records, where each record represents one person, inventory item, transaction, etc.

Each record can have multiple fields, and each field contains one item of data of a specific type. For example, in a table of employees, there's one record for each employee, and individual items of information about that employee are contained in fields for that record. Information like name, address, social security number, etc. might make up the fields for the employee's record.

Table and Field Names

Alpha Five recommends that table names, field names, and paths to your files start with a letter (A-Z or a-z) and be composed of letters, numbers (0-9), and underscore (_) characters. Do not use tilde "~" characters.

I found it two ways.

a) using the "Alpha Five Help" file (chm version); and
b) using WebHelp, which is the online equivalent.

In case you're curious here's the sequence for WebHelp:

Click Resources link on the page you're currently viewing
click WebHelp
Click Search menu
Type "Basic Concepts and Terminology", including the quote marks (mentioned previously).

[vaxennet]

i actually did find it the first time, but since it was not related to my problem of importing the Excel file into Alpha5, I called it "unrelated" in my post. But the information was considered, and i confirmed that the field names confirmed to dbase naming convention. But it still does not work. Saving the Excel file as a dbase file was one of the option I already tried over the weeks i am strugling with this. Problem is, all i ever got was a useless structure with truncated fields of about 10 character lenght. That definalely took care of the discription field which is more than 255 characters long. I was assuming that was common knowledge in this forum. But then again, I may be missing something or it may have something to do with the software i am using. I have Microsoft Office XP and i hope it is not my security settings. I suspect something is wrong with the Excel table, but i cannot figure it out all these weeks, i am working on it, thats why with all options used up, i thought someone else may have had this problem in the past and could point me to a fix. Ideally it would be best for me to figure out myself as part of the learning curve. (i just bought Alpha5 a couple months ago and start using it about 3 weeks ago) but i am running way behind schedule with what appears to be no light at the end of the tunnel. But i do appreaciated your help.

regards

[French]

If I remember correctly, you need to set column widths in Excel to a size wide enough to display the entire cell contents when exporting to .dbf format files, otherwise the cell contents get truncated to the width of the Excel column upon export. Double-click on the right border of each column heading and it will expand the column width to a size sufficient to display data in all cells in that column. That will not solve the problem of the field with a width greater than 255 characters.

I don't know if that's true for all versions of Excel or not, but I believe I ran into it a year or two ago.

Other spreadsheets handle the export to .dbf without having to play with column widths -- they simply export all of the data.

[Tom Cone Jr]

Headley,

Why not post a copy of the Excel sheet here, or at least a representative sample from it?

-- tom

[vaxennet]

Thanks ... I will post the file on Monday
Also expanding the column width in Excel is something i did not try. I cannot emagine why i never tried that, but i will als do it on Monday and report back.

Thanks for all the help

Regards

[CALocklin]

I just created an excel spreadsheet with some huge amounts of text in one column. Importing it using the ADO import worked perfectly and brought in all the text.

- Right click on any table name. (In this case, it's just a way to get to the Create Operation action.)
- Choose Create Operation.
- Click on Import.
- in the next window, choose ADO... rather than ASCII
- fill in the necessary info in the import definition window and run it.

[vaxennet]

Hi
I was out for most of the day, but better late than never. For some reason I believe I may have actually uplosded the file without a message, so please forgive me it it appears twice. Would like to know what is really going onn with this file. Thanks.

[vaxennet]

Expand excel fields and it worked!

Darnit! aparently French above was correct. I expanted the Excel fields before saving to dbase... and guess what, It worked perfectly! :D

I still would like to know why I am getting error messages when I try to import it , using the other methods. I do not want to be stuck with this method alone. Replies appreciated.

Regards