In a number of places in AnyOrder, you can import data.  Microsoft Excel can be used to prepare files for data import.  AnyOrder accepts Comma Delimited Files (CSV Files) and Tab Delimited Files, both of which can be created by Excel.  Note that AnyOrder also accepts dBase files, but Excel has a number of bugs, which have never been fixed, when it comes to importing dBase files.  Because of these bugs we don't recommend the dBase import.  Rather, it's best to use Common Delimited or Tab Delimited Files instead.

Creating Comma Delimited and Tab Delimited Files.

Both Comma Delimited and Tab Delimited files are created in Excel by using FILE >> SAVE AS and then selecting "Text (Tab Delimited)" or a "CSV (Comma Delimited)" from "Save as Type" list.

Excel and Credit Card Numbers (or Long Numbers)

Moving Data into Excel. Excel often can be used as a conduit to move data from a third party program to AnyOrder.  In Excel, the data then can be saved in a tab delimited or comma delimited format data so that it can be imported into AnyOrder.  

If you open a file in Excel and the file that you open includes credit card numbers (or long numbers such as part or ISBN numbers), you'll need to take special precautions.  Excel will immediately convert a credit card number (or a long number) into scientific notation:  5.15598E+15.  Then, if you are planning to move data from Excel into AnyOrder, you'll end up with 5.1598E+15.  And, of course, the number isn't usable when it's in scientific notation.  In Excel, once you open a file, and if the number is put in scientific notation, it can not be converted back to the original.

However, there is a trick you can use with Excel to prevent this from happening.  If you are opening a Comma Delimited file in Excel, don't use the CSV extension.  If your file is named with a CSV extension, such as Orders.CSV, Excel will automatically convert all long numbers, including credit card number to scientific notation.

But if you change the extension of the file to TXT, i.e. Orders.TXT, Excel will start up the import wizard.  The wizard allows you to first indicate the delimiter (for Comma Delimited files the delimiter is a comma).  After you indicate the delimiter, you have an opportunity to format each of the columns in the file.  Find the credit card number column (or column with long numbers) and format it as "Text."  When formatted as text, the number will not be converted to scientific notation and will be usable in AnyOrder.

Making a File from Scratch in Excel. If you are starting in Excel and creating a file that you will import into AnyOrder, be aware that the same thing can happen.  Credit Card numbers, long part numbers, etc. will be automatically converted to scientific notation.  To prevent that from happening, format the column as "Text."  Then enter the numbers.  The order is important.  You need to format the column as "Text" before you enter the numbers.  Once you are finished, you can use FILE >> SAVE AS and create either a "Text (Tab Delimited)" or a "CSV (Comma Delimited)" file which can be accepted into AnyOrder.

Excel's Comma Delimited Files:  The Last Field Problem

Are you are getting the following error message when trying to import records?

"At least one of the records in the Import File doesn't have the same number of fields as the original file . . . ."

If so, the following information will be helpful . . .

There is something that you should know about Excel's Comma Delimited Files (CSV files).  Excel is quite sloppy when it creates a CSV file if the last field is not filled.  If the last field is empty in some records but not empty in others, then it creates a file that has varying numbers of fields.

In the world of database programming, that's a sin and for which Microsoft needs to do a lot repenting.  Fortunately, there's a way of cleaning up Microsoft's bad behavior.

(Note that we have added a new feature to "Other Functions" in the Import area of the program.  The new option is called: Pad Fields - Fixes Excel's (& Other Programs) Last Field Problem. You'll find more information here. You may able to forego the procedure, below, and solve the problem by using that option.)

When trying to import such a file, AnyOrder will trigger an error telling you that one record has 34 fields and the next record has 33.  (It's usually small differences between the records that indicate the end of field problem.)  Note that you can continue to have AnyOrder do the import, and it likely that AnyOrder will successfully import the data, but it's better to prepare the file so that you aren't bothered by the messages.

To clean things up, open the file in Excel.  Add a new column to the very end.  If you are using the first row to hold the names of the fields (and that's always a good idea), give this last column a name.  For lack of a better name, we'll call this last column:  "End."

Put something in the last column.  Use two or three symbols that won't be found in any other part of the file.  For example, type in #*#.  Copy and paste #*# so that fills the column from the top to the bottom.  You want the entire column filled.

Now when you create the CSV file, all the records will have the same number of fields.

Dividing Import Files in Excel

Excel comes in handy if you need to divide an import file in two or three separate files or reduce the size of a file.  However, when you divide a file, Excel often leaves a series of empty records of varying size at the end of the file that you can't visibly see on Excel's spreadsheet. They are there nevertheless and are included in the Comma Delimited or Tab File.  AnyOrder will start importing these records because they are a part of the file.  Whenever records are varying sizes, something is wrong with the import file, and AnyOrder will trigger errors.

To fix this problem, save the file in Excel as a Comma Delimited or Tab Delimited file.  Then open the file in Microsoft Word.  Page down to the bottom of the file.  Look for a series of empty records at the end.  If you find any empty records, delete them.  The file should end with a complete record with all of its fields and nothing else beyond it.

If you see a paragraph symbol at the end, delete it.  Note that sometimes when you try to delete the paragraph symbol at the very end, it remains.  That means it is an end of file marker.  In that case, it's fine to leave it.  The important thing is to try to delete it.  You want to make sure that you don't have anything at the end of the file that AnyOrder might interpret as a record.   Once you've done this, save the file.

Because of this particular problem with Excel, you always want to follow above procedure any time you divide up an import file.

What to do if double quotes, paragraphs (carriage returns), and comma are found in your Comma Delimited File

Things can get complicated if the import file that you are using has lots of extra carriage returns (paragraphs), double quotes and commas.  Normally, this is not a problem since you are importing short pieces of information.  But if you are importing data to the "Notes" area on invoices, the product database, or the customer database, you may be dealing with these.

Why is this a possible problem?  The fields in Comma Delimited Files are separated by commas and quotes, and the records are separated with carriage returns.  A few commas here and there are usually not a problem, but if you have a lot of extra commas, carriage returns and quotes, AnyOrder can get completely confused without cleaning up the file before importing.

You can usually tell right away during the import that you have a problem with this issue when error messages appear saying that one record has 34 fields, then the next has 10 fields.  The difference between the fields is quite variable, going from 34 to 10 to 1 to none.  AnyOrder just doesn't know where to divide up the fields and records.

There is, however, a way of cleaning things up so that AnyOrder can distinguish between fields and records.  To do so, run through the steps below:

1.  In Excel. Open the CSV file in Excel.  Create one last column.  If you are using the first row to hold the names of the fields (and that's always a good idea), give this last column a name.  For lack of a better name, we'll call this last column:  "End."

The reason we need to do this, has been mentioned above.  Excel is very sloppy when it creates a CSV file.  If the last column (columns are the same as fields) of the row (rows are the same as records) is empty, Excel doesn't include it -- which not good.   But if you put something in the last field, then Excel creates a file with the same number of fields in every record -- which is good.

What you need to do is to fill the last field (the last column) with a value that you won't find elsewhere in the file.  Let's fill it with #*#.   (Type in #*#.  Copy it.  Then paste it from the top to the bottom in the last column.)  Also include #*# in the top row along with the name of the row.  So you would have: End #*#  Save the file as a Comma Delimited Files (CSV).  Close the file.

2. In Microsoft Word.  Open the CSV file in Microsoft Word.  What we need to do is change all extra carriage returns (carriage returns are the same as paragraphs) that might cause problems when importing.  In order to do that we need to preserve the essential carriage returns, the ones that tell us when we've come to the end of a record.  We can find those easily because we've marked the last field with #*#.  In Microsoft Word use FILE >> REPLACE and "Replace All" using:

Find What: #*#^p   (The ^p means paragraph which is the same thing as carriage return.  So we're find #*# plus the carriage return.)

Replace With: #@# (We're just giving it a temporarily value which is different than anything else in file.  You could use some other combination of symbols.  Just use something that would not be found elsewhere in the import file.)

Save the file.  You'll be saving it as Plain Text (in other words, you do NOT want to save it as a DOC file).

3. In Microsoft Word.  At this point, we have all of the important carriage returns preserved.  We now need to get rid of the non-essential carriage returns.  These are the carriage returns that are found in the field(s) that will going into the Notes area.  What we'll do is to change them to a hyphen.  It works quite well.  Everything is still quite readable, and, at the same time, we eliminate a big problem with the import file.  Use FILE >> REPLACE and "Replace All" using:

Find What: ^p   (The ^p means paragraph (same thing as a carriage return).  So we're finding all other carriage returns)

Replace With: - (IMPORTANT Type in starting with a SPACE, then HYPHEN, then SPACE.  That helps makes thing more readable.)

Save the file.

4. In Microsoft Word.  Looking good.  We have all of the extra carriage returns squared away.  Now we need to bring back the essential carriage returns, the ones that tell us when we've reached the end of a record.  Use FILE >> REPLACE and "Replace All" using:

Find What: #@#  (This is what we used in step #2 above)

Replace With: #*#^p (We put back the end marked plus the paragraph.)

Save the file.

5. In Microsoft Word.  This is a bug in MS Word.  For some reason. it misses some paragraph replacements.  This step assures that everything is replaced.  Be sure to do this.  Otherwise some of the records may be doubled up.  Use FILE >> REPLACE and "Replace All" using:

Find What: #*# - (IMPORTANT  Type in:  POUND SIGN, ASTERISK, POUND SIGN, SPACE, HYPHEN.)  The SPACE is the same space that you put in from step 3.

Replace With: #*#^p

Save the file.

6. In Microsoft Word.  We need to remove something that appears at the bottom of the file.  Go to the bottom of the document.  Look for a single record consisting of just a hyphen.  Delete it and the paragraph symbol.  The file should end with a complete record with all of its fields and nothing else beyond it.   Note that sometimes when you try to delete the paragraph symbol at the very end, it remains.  In that case, it's fine to leave it.  Just make sure you get rid of the single hyphen.   Save the file.  Close it.  We're all through with MS Word.

7. In Excel.  Open the CSV file in Excel.  The last thing we need to do is get rid of any: ", (that's a double quote and a comma).  In a CSV file, a double quote and a comma indicate the end of the field, so there is any extras, the fields can get messed up.  In Excel, use EDIT >> REPLACE and "Replace All" using:

Find What: ",  (Type in: DOUBLE QUOTE and COMMA right after one another.)

Replace With: " , (Type in DOUBLE QUOTE, SPACE and COMMA.  Don't forget the SPACE).

Note:  We've found a bug in at least two versions of Excel.  This bug will occur if you have a long description going into Notes.  When you use "Replace All," it replaces most of the ", combinations, but with particularly long descriptions, it will trigger a "Formula Too Large" errors.  If that happens to you, no worries.  You just have to use EDIT >> FIND and make the change by hand.  Fortunately after the replace all, you'll find that there are a few more that need manual changes.

8. That's it.  Save the file in Excel.  Now it's ready to be imported to AnyOrder.

This looks fairly involved, largely because we've included a description of why each step is being done, but actually it goes quite quickly -- and since it's only needed with certain imports, you won't have to worry about doing it too often.