Import and import template issues

lueu

Hi!
I am trying to figure out how I can make it possible for my users to import an Excel file. So far I have figured out that they can't, it will have to be a csv file. So I try to import CSV, but only get error message saying "invalid description of data column" and that the column must start with BO name? Makes no sense to me.

So I try to make a template, maybe that will make it easier. Now I can import files, but not every column seems to be imported. So I will try to edit my template to see if there is anything I could change. But then the web site is not responding, and I can't do anything.. Why can't I edit the template?

Any hints on where I am going wrong?
Also, it seems to me that import will never be user friendly, the form is too complicated with too many choises. How are they supposed to know what opitons to tick, or what about the encoding, how is the user supposed to know what to choose?

Is there any hope that there is a way to do this that could actually work? Or could I just forget the whole import possibility? If users wants to register a lot of objects at once, an excel import would be great, but I seems like it can't be done.

joben

lueu wrote
Any hints on where I am going wrong?
Also, it seems to me that import will never be user friendly, the form is too complicated with too many choises. How are they supposed to know what opitons to tick, or what about the encoding, how is the user supposed to know what to choose?

My opinion is that you should present the users with as few choices as possible when they are about to import something. That means not showing them the default import form.
Letting them upload the csv to a Temp BO, then storing the file on the server, and then doing an import via a process using a template is what I suggest.
Like: "IMPORT Blah FROM 'server folder' USING "my cool template".
Then all you need to display for the user is the document attribute where they are supposed to upload the file.

Excel imports are only possible if you let the file land on the server, and then use EXECUTE PROGRAM to do some magic.
In this thread, ACDC uses a third party program to store the excel directly in the MySQL database, while I use Powershell to convert it from xslsx to csv:
Import csv works but same with xls/xlsx gives parser error (comment)&hilit=csv#p58764

Jaymer

lueu wrote
Hi!
I am trying to figure out how I can make it possible for my users to import an Excel file. So far I have figured out that they can't, it will have to be a csv file. So I try to import CSV, but only get error message saying "invalid description of data column" and that the column must start with BO name? Makes no sense to me.

1) CSV imports work fine.
The 1st line, as in most CSV files, needs to define the fields.

*L.PriKey,L.ob_Order.Order_Num,L.Order_Num,L.Line,L.Status,L.Qty,L.UM,L.Product,L.QCode,L.ps_Product.ID,L.Inventory_Id,L.Description,L.Factory_Line,L.Cust_ID,L.Cost_Line_Item,L.Cost_Ext,L.CommentsYN,L.Dimensions,L.Dim_Width,L.Dim_W_Fract,L.Dim_Height,L.Dim_H_Fract,L.Dim_W_Points,L.Dim_H_Points,L.Option_1,L.Option_2,L.Option_3,L.Option_4,L.Option_5,L.Option_6,L.Option_7,L.Option_8,L.Option_9,L.Correlated_Order,L.Correlated_Line,L.MU_Num_Children,L.MU_Parent_Line,L.MU_Group,L.Feat1_Abrv,L.Feat2_Abrv,L.Feat3_Abrv,L.Feat4_Abrv,L.Feat5_Abrv,L.Feat6_Abrv,L.Feat7_Abrv,L.Feat8_Abrv,L.Feat9_Abrv,L.Category1,L.Category2,L.Category3,L.Category4,L.Category5,L.Category6,L.Category7,L.Category8,L.Category9,L.Section1,L.Section2,L.Section3,L.Section4,L.Section5,L.Section6,L.Section7,L.Section8,L.Section9,L.FtrTable1,L.FtrTable2,L.FtrTable3,L.FtrTable4,L.FtrTable5,L.FtrTable6,L.FtrTable7,L.FtrTable8,L.FtrTable9,L.Repair_Remake,L.Oriel_Offset,L.OrielYN,L.Extreme_SizeYN,L.OO_YN,L.OO_ProductYN,L.OO_DenverYN,L.Date_Completed,L.Date_Data_Entry,L.Data_Entry_ID,L.Install_Loc1,L.Install_Loc2,L.Breakdown,L.Template,L.ps_Template.ID,L.Pos2,L.Pos3,L.Pos4,L.Pos5,L.Pos6,L.Pos7,L.Pos8,L.Pos9 -1584
90816102,908161,908161,02,F, 1,EA,SH25,PDSR,Mast_Product#5549,,"P/D SERVICE SASH RIGHT",27,A00005, 686.98, 686.98,Y,37 1/4 x 76 11/16, 37,1/4, 76,11/16, 4,11,B4,TL,NG,NO,MP,AB,A1,A1,,,,,,,BZ\TN\EO,TEMPLOWE,NO GRIDS,NO,MULTI-PT,ABRS HDL,N/DOG DR,STD SCRN,,Color,Glass,Grids,OPERATE,Hardware,PD HANDL,DOG DOOR,DOG SCRN,,,G1-GLAS-RT,GR-GRDD-RT,,,,,,,160,171,003,089,090,027,112,115,,, 0.000000,,N,Y,,,07/06/2023,07/22/2022,VKER,"","",,,Mast_Template#0000,RIGHT,RIGHT,,,,,,
93522001,935220,935220,01,F, 1,000,ZS01,ZCT,Mast_Product#6647,Z1 SHAPE,"8200 CIRCLE TOP",50,000760,,,Y,33 1/2 x 16 3/4, 33,1/2, 16,3/4, 8,12,WH,LE,05,,,,,,,,,,,,WHITE,LE ARGON,Sunburst,,,,,,,Color,Glass,ShGrids,,,,,,,,,,,,,,,,073,002,104,,,,,,,, 0.000000,,N,N,Y,Y,02/22/2023,06/28/2023,MNER,"","",,,Mast_Template#0000,,,,,,,,

In my case, the Table name is "L", so "L." is the prefix on all column names.

2) Editing a template is broke - and has been for a few years. Its a pain. I always had to start over, OR export it, modify the text file, and re-import.

3) I have users that import - specifying the template and clicking the next 3 checkboxes (if I remember correctly).

4) One you make a template, you can launch it from a Menu and the user doesn't have to go through all those options - but then the file has to be on the server - which works for me, but for you wouldn't allow them to specify a specific filename and import it from their local drive.

5) Make an export template for the table you are trying to import into, and then examine the file that Aware created. That will help you on that header row.

lueu

joben wrote
Then all you need to display for the user is the document attribute where they are supposed to upload the file.

Thanks, I was thinking something like that should be possible.
I will look into the options you suggest.

lueu

Jaymer wrote
1) CSV imports work fine.
The 1st line, as in most CSV files, needs to define the fields.

In my case, the Table name is "L", so "L." is the prefix on all column names.

2) Editing a template is broke - and has been for a few years. Its a pain. I always had to start over, OR export it, modify the text file, and re-import.

3) I have users that import - specifying the template and clicking the next 3 checkboxes (if I remember correctly).

4) One you make a template, you can launch it from a Menu and the user doesn't have to go through all those options - but then the file has to be on the server - which works for me, but for you wouldn't allow them to specify a specific filename and import it from their local drive.

5) Make an export template for the table you are trying to import into, and then examine the file that Aware created. That will help you on that header row.

J

Thanks for the thorough explanation!

I have defined the colum names, but do I really have to have the table name as prefix on all columns? Is this only if I don't use a template (because of the mapping)?
Really? Isn't this something the developers would like to fix? At least they could remove the Edit option and state that editing isn't an option..
That might work if I can add som guidelines for them to follow
How can I launch it from a Menu?
Tried that, did not make any wiser :? :lol: Maybe I did't do it right, I will check documentation on export, only looked into import so far.