[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1485
  • Last Modified:

EXCEL TO AS400 TRANSFER

I try to transfer an excel file to AS400 vis client access.
In the excel file  I have the numeric data in a character column.
I parameter the access to conver the numeric to character and it work.
My problem is when the numeric in excel has more than 6 digits like (6108312457) I receive in the as400 charater field a data like  (6.10831E+009).
in  the lower part you have a example of the excel file and the as400 result file.
Need you advice to receive the data like it was in the excel file  (6108312457)

thanks

 
excel file 
CUST#	SKU#	UPC#
1Q1005	912222-2	6108312457
 
AS400 FILE 
Customer Code  Customer SKU     UPC Barcode 
  1Q1005            912222-2             6.10831E+009

Open in new window

0
BecoLP
Asked:
BecoLP
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
Gary PattersonVP Technology / Senior Consultant Commented:
Try formatting the Excel column as text, and eliminate the numeric-to-text conversion from your file transfer.

- Gary Patterson
0
 
daveslaterCommented:
You can also load directly from excel

1. Create using the excel plugin
in excel use "Tools/Addins" menu
if you do not see iSeries Access data Transfer click browse and go to
c:\program files\IBM\Client Access\Shared and select cbwtfxla

you will get two other tools
select the rage to transfer and then select transfer to icon, base the new file on the excel workbook and follow the prompts.
You can configure each cell as charater, date, numeric field etc.

dave


0
 
BecoLPAuthor Commented:
Thinks for your solutions, but not fit with my case:
- First, the transfer will to run in batch by the user.
- Second, is true that when I format the column as Text and refresh all cells the problem is resolved, but I project to give the platform to the users, who will use copy and paste to fill the file.
So the user can override the format.

Still need other solution please.
Thanks.  
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Gary PattersonVP Technology / Senior Consultant Commented:
We had a similar problem in a recent web application we designed where users uploaded XLS documents that we had to open.  We solved it using Excel automation to open up the document that we received, format the cells to text, and save the changed document.

Possible solutions:

  • Create a script or program (VBScript, Client Access Macro, Excel Macro, program) to open the spreadsheet (if needed), format the range in question, and optionally upload the document.
  • Create a macro to format the column and inform your users that they need to run it manually before saving and uploading.  Not advised.
  • Create an Excel macro that the user has to run to perform the formatting and the upload (using daveslater's method) all in one step.
  • Use the Worksheet_Change event to detect when the user changes a cell (pasting, for example), and include code to reformat the cell or range.  Code below.
  • Use Auto_Close or WorkbookBefore_Close macros to reformat the range when the user closes the spreadsheet.  http://support.microsoft.com/kb/141563
Note that security settings need to allow macros for many of these solutions to work.  You may want to consider signing you code with a digital certificate if this is going to be widely deployed.

- Gary Patterson



Private Sub Worksheet_Change(ByVal Target As Range) 
 If Intersect(Target, Range("C5")) Is Nothing Then 
  Exit Sub 
 Else 
  'The cell you are monitoring has changed! 
  'Do whatever you need to do... 
 End If 
End Sub 

Open in new window

0
 
daveslaterCommented:
If you can use macros then you can use ADODB to directly insert the records on the click of a button.

Dave
0
 
theo kouwenhovenCommented:
Hi BecoLP,

You mentioned "In the excel file  I have the numeric data in a character column."
Is that required? why dont you use a Numeric column?
Do the column also contain character data?
in that case make the numeric data real character by adding  leading or trailing blank(s) to it.

Regards,
 Murph
0
 
BecoLPAuthor Commented:
HI Everyone,
I have both the character and the numeric in this column,
the solution of adding the leading or trailing blank, is not good for me because, like I said before, is the user who will fill the excel file (by copy paste) and run the batch job from  as400.
I work actually on the  Gary Patterson solution. I will add the auto_close macro which will transfer the colunm to text.
you will have the my feedback
thnks for all,  
 
0
 
theo kouwenhovenCommented:
Hi BecoLP,

Transfering column to text is not good enough I think, The trnasfer will stop on every empty cell.
so use the close macro to add at least a space to the cell if it is empty.

Regards,
Murph
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
AngelIII,

Thanks for volunteering for cleanup.

I recommend spliiting points between Dave, Murph, and me.  We all contributed valuable, technically-correct advice.

Gary Patterson
0
 
theo kouwenhovenCommented:
Agree
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now