Tabular Section, ValueTable, FormDataCollection Vs SpreadSheetDocument

The 1C:Enterprise developers forum

#1
People who like this:0Yes/0No
Interested
Rating: 27
Joined: Apr 5, 2012
Company: 1TÇ Şirketi - Merv Bilgi İşlem Otomasyonu Yazılım Ltd. Şti.

Hi

Is there any method to load values from SpreadSheetDocument to TabularSection or ValueTable or FormDataCollection without traveling in each row of spreadsheetdocument?

The task is to copy values from Excel which contains 500.000 and more rows.

By using Ctrl+C and Ctrl+V it's possible to copy values from Excel in little time. But is there any methods to copy that values to tabularsection o valuetable or formdatacollection without travelling in each row?

 
#2
People who like this:0Yes/0No
Timofey Bugaevsky
Guest

Joined:
Company:

Hello!

You can use external data sources for that.

In short: you need to add an external data source, set names and names in data source for table and columns. External data source name for table will be the name of the MS Excel sheet and a $ sign after that. Names of columns are values in first row of the sheet.

After that by setting the external data source connection string you can set the path to file and after that execute queries to that external data source.

Code
   ConnectionParameters = New ExternalDataSourceConnectionParameters; 
   ConnectionParameters.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};"
      + "DBQ=C:\import\test.xls; DriverID=790"; 
   ExternalDataSources.ImportExcel.SetCommonConnectionParameters(ConnectionParameters); 
   ExternalDataSources.ImportExcel.Connect();

   Query = New Query();
   Query.Text = "SELECT
                 |   Test.Name AS Name,
                 |   Test.Value AS Value
                 |FROM
                 |   ExternalDataSource.ImportExcel.Table.Test AS Test";
   
   ValueToFormAttribute(Query.Execute().Unload(), "ValueTable");


See the attached files for example.

Download 1Cv8.dt (11.84 KB)
 
#3
People who like this:0Yes/0No
Just came
Rating: 0
Joined: Jun 23, 2013
Company: NBMotor

Quote
Timofey Bugaevsky wrote:
See the attached files for example.
Attached Files
Download test.xls (13.5 KB)
Download 1Cv8.dt (11.84 KB)

Can you please, make same example that will put this information (from the table) to any catalog.
Thanks in advance!

 
#4
People who like this:0Yes/0No
Timofey Bugaevsky
Guest

Joined:
Company:

You simply need to iterate the query result.

Code
   ConnectionParameters = New ExternalDataSourceConnectionParameters; 
   ConnectionParameters.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};"
      + "DBQ=C:\import\test.xls; DriverID=790"; 
   ExternalDataSources.ImportExcel.SetCommonConnectionParameters(ConnectionParameters); 
   ExternalDataSources.ImportExcel.Connect();

   Query = New Query();
   Query.Text = "SELECT
                 |   Test.Name AS Name,
                 |   Test.Value AS Value
                 |FROM
                 |   ExternalDataSource.ImportExcel.Table.Test AS Test";
   QueryResult = Query.Execute();

   SelectionDetailRecords = QueryResult.Choose();

   While SelectionDetailRecords.Next() Do
      NewObject = Catalogs.Items.CreateItem();
      NewObject.Description = SelectionDetailRecords.Name;
      NewObject.Code = SelectionDetailRecords.Value;
      NewObject.Write();
   EndDo;

 
#5
People who like this:0Yes/0No
Interested
Rating: 27
Joined: Apr 5, 2012
Company: 1TÇ Şirketi - Merv Bilgi İşlem Otomasyonu Yazılım Ltd. Şti.

Timofey,

How can we write large amount of rows (ValueTable) load to excel file?

Recognized recordSet in tables of external data source. But it is not working. If it's possible can you provide test excel writing 500.000 rows not one by one?

 
#6
People who like this:0Yes/0No
Timofey Bugaevsky
Guest

Joined:
Company:

Hello, Murat!

Starting from version 8.3.5.1068, you can update data in the external data source.
You can assign values and use Write() operator to update it.

 
Subscribe
Users browsing this topic (guests: 1, registered: 0, hidden: 0)
Be the first to know tips & tricks 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.