Table importing from pdf or cvs

The 1C:Enterprise developers forum

#1
People who like this:0Yes/0No
Just came
Rating: 1
Joined: Dec 2, 2019
Company:

Hello all,
I would like to know whats the most effective way of data importing? I have to import product table from pdf or csv extensions. Do I have to write my own code or there is wizard for it?

Thanks in advance,
Jānis

 
#2
People who like this:0Yes/0No
Administrator
Rating: 9
Joined: Oct 3, 2019
Company:

Hi Jānis Čačis!

You can import data from Excel using the following source code:


Code
&AtClient
Procedure ReadXLS(Command)
   
   ReadXLSAtServer();
   
EndProcedure

&AtServer
Procedure ReadXLSAtServer()

   Spreadsheet = New SpreadsheetDocument;
   Spreadsheet.Read("D:\Temp\temp.xls");
   
   Area = Spreadsheet.GetArea("Sheet1");
   
   CountLines       = Spreadsheet.GetDataAreaVerticalSize();
   CountRows      = Spreadsheet.GetDataAreaHorizontalSize();
   
   For curRow = 1 To CountRows Do 
      
      For curLine = 1 To CountLines Do 
         
         Value = Area.GetArea("R" + curLine + "C" + curRow).CurrentArea.Text;
         
      EndDo;
      
   EndDo;
   
EndProcedure

 
#3
People who like this:0Yes/0No
Just came
Rating: 1
Joined: Dec 2, 2019
Company:

So then from Spreadsheet document I can save that data in specific catalog or what?
I want to import products inside product catalog.
Sorry if first question wasn't clear

 
#4
People who like this:0Yes/0No
Interested
Rating: 12
Joined: Nov 10, 2011
Company: 1A Software e.U

Hello Janis!

I have another code. I usually save a csv-file as Excel-File.
My code looks so:

Code
&AtClient
Procedure SelectFile(Command)
   
   Mode = FileDialogMode.Open;
   OpeningFileDialogue = New FileDialog(Mode);
   OpeningFileDialogue.FullFileName = "";
   Filter = "Excel(*.xlsx)|*.xlsx|Excel 2003(*.xls)|*.xls|Commer(*.dta)|*.dta|Commern(*.dtn)|*.dtn";
   OpeningFileDialogue.Filter = Filter;
   OpeningFileDialogue.Multiselect = False;
   OpeningFileDialogue.Title = "Sel ect file";
   If OpeningFileDialogue.Choose() Then
      FilesArray = OpeningFileDialogue.SelectedFiles;
      For Each FileName In FilesArray Do
         Selection = New File(FileName);
      EndDo;
      ImportAtServer(Selection.FullName);

   Else
      DoMessageBox("File(s) not selected!");
   EndIf;   


EndProcedure



In this case you do not need to write a fix path to your file, you can select it fr om whatever you want.

Then at Server:

Code
&AtServer
Procedure ImportAtServer(File)
   try
         ExcelApp    = New  COMObject("Excel.Application");
   except
         Message(ErrorDescription()); 
         Message("Can't initialize Excel"); 
         Return; 
   EndTry; 
   
   try 
   ExcelFile = ExcelApp.Workbooks.Open(File);

      NRows  = ExcelApp.Sheets(1).UsedRange.Rows.Count;
      
         For n= 2 To NRows Do
            
             ProductCreated = False;
            
             LineTotal = 0;
            
            OrderID = ExcelApp.Sheets(1).Cells(n,2).Value;
            SendingCosts = ExcelApp.Sheets(1).Cells(n,5).Value;
            Versandschein = ExcelApp.Sheets(1).Cells(n,8).Value;
            SendingDate =  ExcelApp.Sheets(1).Cells(n,7).Value;
/// Here do something

         EndDo;
   
   Except
   Message(ErrorDescription()); 
   ExcelApp.Application.Quit();
   EndTry;
   
   Try
   ExcelApp.ActiveWorkbook.Close(False);
   Except
   EndTry;

EndProcedure



It starts with n = 2 because the first line in Excel is the header.
For example:

Product code || Product Name - this is the header.
The second line is the product

    || Item 1
    || Item 2

an so on.

Please consider that in 1C:Enterprise the first field in an array is not 0 (zero) like in java or C++ but 1(one).

The first column (Product code) let just empty because the platform will give automatic code but you can also use your codes.

Then it is somehow:

Code
NewProduct = Catalogs.Products.CreateItem();
////Do not forget to write the item after creating.
NewProduct.Write();

 
#5
People who like this:0Yes/0No
Just came
Rating: 1
Joined: Dec 2, 2019
Company:

Hello, Lioudmila!
Thank you for your help, but I am not 100% sure that I am implementing this code properly.
Can you please provide me where should I put that code and a bit more information about code lines?

Thanks in advance
Jānis

 
#6
People who like this:0Yes/0No
Interested
Rating: 12
Joined: Nov 10, 2011
Company: 1A Software e.U

Hello Janis,

you can create some form and a command. In my case is an action SelectFile(Command). Then you can put the code.

Code
ImportAtServer(Selection.FullName); // it calls a procedure at Server


Code
OrderID = ExcelApp.Sheets(1).Cells(n,2).Value; /// Cells(n,2). means it is a column in Excel 2
            SendingCosts = ExcelApp.Sheets(1).Cells(n,5).Value; // This is column 5
            Versandschein = ExcelApp.Sheets(1).Cells(n,8).Value; // This is column 8
            SendingDate =  ExcelApp.Sheets(1).Cells(n,7).Value; // Column 7


To check which column contains your data, I usually use:

Message (SendingCosts);
Message (Versandschein);
Message (SendingDate);

Code
Message (SendingCosts);
Message (Versandschein);
Message (SendingDate);


So you can see that your data from Excel are read correctly.

Please let me know if you could manage or not, which line in the code is unclear.
Regards,

Lioudmila

 
Subscribe
Be the first to know tips & trick on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.