transfer file from pc to as400, can' t use csv or txt

[thelearner]

I want to transfer file from pc to as400. If I save under .PRN then I have to count position of the data. Every time I upload the data, I have to modify my program as to what position to move what field.
I can't use comma-delimited(.csv) because some of the data contain comma(,). Any suggestion???

 

[koresnordic]

You say if you save as .PRN. What format is the data in originally?



Graham

 

[jmd0252]

Have you thought of trying it just as a text file, with everything coming accross as alpha, with no numeric fields??

 

[thelearner]

I receive the file as the excel format.
To reply to jmd0252, text file won't work. If I save it as a text file then I have to modify my RPG program as to what position to grap what data. I receive this file regularly and I want to automate my procedure as much as I can.

 

[jmd0252]

Have you tried this,,,, bring the file down as an excel file,,, which generates the "file format definition",,, and then try taking the file back, using the machine generated definition.

 

[thelearner]

No. Can you please tell me exactly how to do this. I have no idea what you talking about.

 

[jmd0252]

see the new thread I posted,,,, that doesn't make any sense as a new thread,,,,, sorry for the incoivience..

 

[abacuscorp]

rapeek

I've created .prn files and uploaded to the as400 using the DOS FTP command and the data stays where it belongs.

warren

 

[RedMage1967]

May I make a suggestion, have you looked at the CpyFrmImpF command?

RedMage1967
IBM Certifed - RPG IV Progammer

 

[thelearner]

Hi jmd0252,

Here is my sample file:
STYLE # HTS#
12345 1234.45.6789
12455 1234.45.6666
A4578 8978.45.8798

I clicked on the whole sheet and changed every columns to text format. Still, I got an error message saying that col. 1 row 2 is not match with PC file diffinition.
I did another test by adding some alpha char. to all the STYLE# and using macro to transfer again. This time it's work.
So it seem like I can't have all numeric char. in my data. Or do I miss something?

to reply to abacuscorp: the column width is not always the same. So using .PRN to upload will not always get same data position. Thanks anyway for you input.

 

[thelearner]

Hi RedMage1967,

to use CpyFrmImpF command, how should I save the file. I cann't save under .CSV. My data contain comma.

 

[Pekka]

Hi,
I have also had some trouble with excel text format cells that looks like alpha but excel keep them numeric. To make sure that exel keeps your alphanumeric as text you can use this simple macro in excel :

' ifNumeric convert to text
Sub num_totext()
For Each c In Worksheets("Sheet1&quot.Range("A1:A100&quot.Cells
If Application.IsNumber(c.Value) Then
c.Offset(0, 0).Value = "'" & c.Value
End If
Next c

End Sub

 

[koresnordic]

Do you have M$ Access? If so, you can link to the file on the 400 and then use an append query to put the data across. Mo problem with field sizes at all. of if you don't want to do anything else wiht the data first, just use the access transferpreadsheet function to move directly to the 400 file.



Graham

 

[thelearner]

Hi Pekka,

You said to use your macro code in excel, exactly how do I do that. I've never write macro or any pc program before. And is that all the code I need?
For now I found another way to convert the column to text. Go to 'Data', then select 'Text to Columns'. It's work. However the problem is I can convert only one column at a time.

 

[RedMage1967]

Rapek

I convert Excel spreadsheet to CSV all the time. If the data fields has commas in it, Excel will automatically place double quotes (&quot around the field (it will treat it as a character field). The only problem you'll have is if your data contains double quotes, that will cause a problem.

RedMage1967
IBM Certifed - RPG IV Progammer

 

[thelearner]

RedMage1967

I used command cpyfrmimpf to upload .CSV file to my data base file. Data that contains comma will get chopped at the comma.

 

[thelearner]

Ok, now I'm able to transfer data using macro function inside excel. I have file definition (.FDF) and the template(.DTT) saved for future use.
Here is my next problem.
I tried to transfer again using the 'Data Transfer to iSeries' tab on the AS400 screen and use file definition that I created from Excel. It won't let me.
I got error msg saying that I have to use Data Transfer From iSeries to create FDF file. So I tried to do that. However when it came to the scan pc file to create iseries database file, it got stuck. After I clicked the start scan, there is a message 'progresspening file' on the top of the scanning box. But it did not scan at all.

 

[Pekka]

Rapeek,
in Excel, hit alt+F11 you get to visual basic editor. Then just copy the macro piece and paste it to editor. Check worksheet name and range, close the editor. To run the macro: Tools->macro->macros-select the macro and run. And ofcourse save your workbook before running.

 

[Pekka]

Have you guys noticed that at least with iSeries Access for Windows comes with excel add-inn by wich you can directly transfer data to iSeries table or replace or append based on the excel datasheet. There you can define fields attributes, lenghts, types etc.

 

[thelearner]

Hi pekka,

Yes, I did that, trying to transfer data from iSeries Access window. My problem is when I have to define FDF file. I can't use the one that I created within Excel. I got an error msg. saying that I have to use iSeries fxn to create. When I tried to create from iSeries Access window, it stopped at the file scanning. It just did not scan and no error msg. I must have missed something.