Item Number Change - Excel Import 1

[johnhugh]

Hi,
I'm using Accpac 5.5.
Is there any trick to get the Excel import working for the item number change utility?
I followed the instructions from the help file and used Excel's 2003 file format but instead of importing items it just shows random characters.
Excel 2007 format shows even more random characters.

I tried in Accpac 5.6 as well. Excel 2007 still does not work. The file in Excel 2003 format just gives me an error: "Error reading Worksheet file

 

[ettienne]

Nope, I have used that many times. Make sure that the data in a column is of a consistent type, i.e. the item numbers must all be text and not mixed text and numbers. Also try save the file as a CSV and see if that helps.

 

[johnhugh]

I cannot get this to work. No idea why.

I have an Excel 2003 file with 5 columns
1-01-01-01-00001 1-01-01-01-00002 1 Item

The last column is just a whitespace as suggested by the manual.
(also tried adding text to the last column)
The row has been formatted as text. I have ' placed in front of the item numbers and the number 1.

Below is what I get when trying to import.
I import from my desktop on a terminal Server running MS Server 2008 with SQL 2005.

Old code = New code = . Rejected: Invalid Action Code
Old code = New code = ?. Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = C. Rejected: Invalid Action Code
Old code = New code = 5. Rejected: Invalid Action Code
Old code = ÿ New code = N. Rejected: Invalid Action Code
Old code = New code = ;. Rejected: Invalid Action Code
Old code = New code = <. Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code
Old code = New code = . Rejected: Invalid Action Code

 

[Jay Converse]

Save it in Excel 97 format.

 

[johnhugh]

Excel 97 won't work as well.
Same with Excel 95.

I've done it now as a CSV file and it works.
If I save this CSV file as .xls I get the same rubbish as above.
Can't figure that out.
Anyway, CSV should do the trick for me.

Thanks.

 

[DjangMan]

At the time the number changers were written there weren't many 3rdparty controls to handle importing from different formats so it has been stuck with that older technology for a while.

 

[planetbluau]

I have two clients who have their Accpac 5.6 on PU1, as far as GL's and Account Sets are concerned, are set up pretty much the same.

In one client, when goods are shipped and invoiced the Shipment Clearing GL maintains a ZERO balance. (eg, AR-IN is credit for X and the OE-SH is debited X as per the GL TRANSACTION LISTING REPORT.

However, in another client, when goods are shipped and invoiced the Shipment Clearing GL accrues a balance. The GL TRANSACTION LISTING REPORT the OE-SH part does not appear.

Can anyone shed some light on why this is happening?

Thanks,
Rodney

 

[ettienne]

Have all batches been posted in the GL?
Run IC day end and OE Create GL batches and see if any GL batch is created.... if so then post it.

 

[planetbluau]

Thank you! That led me to the difference in the two systems. The G/L Integration setup of OE was set to do Manually On Request Using Batch Icon whereas the desired method was during Automatically During Posting.

This has been bugging the client for ages. It had messed up the GL and thus the accountant had made journals to fix.

We would like it to be Automatically During Posting. So if we change the option now I presume it will just pick up from the next transaction. However, what happens to the batches waiting to be created? What if the Create Batch gets clicked. I presume it would massively wreck the GL Balances? Any ideas of ways to get rid of those transactions. There would be hundreds of thousands I presume.

If you have any suggestions, that would be appreciated. We're just running it in a test database now to see the effect.... it's still running!

(Not sure how I ended up posting this in this thread - maybe a moderater can put it in it's own correct thread)

Thanks again,
Rodney

 

[DjangMan]

Just as an aside - all of us on this forum are on the dealer forums as well.

 

[ettienne]

Reverse the GL entries that the accountant made and post the OE entries in the GL.

 

[planetbluau]

How did I know you were going to say that! Thank U.