Local File

1. Overview

1.1. Function Description

Importing data from files means that Guandata supports importing data from Excel, CSV, and other files in the Data Processing Center, enabling data processing services.

1.2. Application Scenarios

1. Rapid business growth, Excel cannot handle enterprise data volume

As the enterprise expands, departments increase, and business grows rapidly, a large amount of data is accumulated in various aspects such as product sales, supply chain management, and financial management. Especially when the data volume in sales and supply chain links is too large, Excel can no longer support business analysis needs, such as when the underlying wide table exceeds 100 million rows and cannot be loaded into Excel for analysis. Importing data from Excel and other files into the Guandata one-stop intelligent analysis platform can solve this problem, integrating and cleaning scattered departmental data to form the enterprise's data asset pool.

2. Data silos between departments, difficult to perform association analysis

To have a comprehensive grasp of the company's business, management needs to integrate and associate statistics from different departments, identify overall growth points and issues, and adjust business strategies accordingly. Importing departmental file data into a unified data analysis platform helps management integrate data and perform data pivoting, conduct multidimensional analysis, and further use visual dashboards for dimension combination, summary calculation, slicing, and drilling to gain insights.

2. User Guide

Operation Steps

1. Enter the "Data Preparation" page and click the "Dataset" module in the left navigation;

2. Click "New Dataset", select File > Local File;

3. Select the corresponding file type: Excel or CSV file;

4. After successfully uploading the data, manage the file data.

2.1. Select Connector

Enter the "Data Preparation" page, click the "Dataset" button on the left, and select New Dataset > File > Local File. Currently supported file types include: Excel and CSV. CSV supports uploading compressed packages (zip files) and automatic parsing. When a txt file meets the CSV format, you can upload it by selecting "CSV".

2.2. Select Data Table

Local files support uploading both Excel and CSV files.

Note:
1. There are system restrictions on file size during upload.
   (1) CSV files (can be compressed into zip) must not exceed 500MB;
   (2) Excel files must not exceed 500MB, and old version Excel files such as xls must not exceed 5MB.
2. The system automatically creates datasets by sheet.

2.3. Confirm Data Table Information

Excel Files

1. After uploading the specified file, fill in the dataset name, storage path, description, and confirm. The default storage path is the system root directory.

2. Support marking the current dataset as a "Sensitive Dataset". Once enabled, sensitive datasets cannot be directly used to create cards. You need to go to the dataset details - Data Security page to configure sensitive fields. See Data Masking.

If you are unsure about the sensitive fields in the current dataset, you can click the "Smart Detection" button for automatic detection. The system will identify sensitive fields based on built-in and user-defined detection templates. See Detection Rule Configuration.

3. Users are allowed to upload data from specified rows and columns by configuring the format in advance, making data upload more precise.

• Set header: Users can set the dataset header position. The system only extracts data from the header to the last non-empty row. The default is the first row;

• Select column data: By default, data is uploaded from column A to the last non-empty column in the header. Custom column range settings are also supported.

Note: When uploading multiple sheets with the same header and column data range, you can use the "Sync Configuration" function. The system will synchronize the current configuration scheme to all other datasets.

4. Confirm whether you need to modify the field names and types of the current dataset.

• Field name: Click the field position to rename the field;

• Field type: Click the down arrow next to the field type to adjust the field type.

Note:

1. You can preview 30 rows of data in the data preview section.

2. When switching field types, you will directly see the converted data content. If the two do not match, Null values will be displayed.

5. Configure the deduplication primary key as needed. During data initialization, the system will deduplicate data according to the set primary key.

CSV Files

CSV files have the same upload process as Excel files, with the only difference being the confirmation of parsing results.

Confirm parsing results: The system will automatically parse the uploaded CSV file and display the results. Users can set the corresponding parsing parameters, including:

• File encoding: Provides 3 character encoding methods for parsing files, such as UTF-8, UTF-16, GB18030, etc.

• Delimiter: Provides 5 mainstream delimiter options, and also supports user-defined delimiters.

• Enclosure character: Enclosure characters are used to mark the start and end of text or data. In programming and data formats, enclosure characters help parsers and compilers understand the structure and boundaries of data. Common enclosure characters include quotes, parentheses, braces, etc.

• Escape character: Escape characters are used to insert special characters into strings, which are usually not directly included in strings. Escape characters usually start with a backslash (\\), followed by one or more characters representing a specific character or operation.

2.4. Complete Dataset Creation

After completing the above steps, click the "Confirm New" button to complete the creation of the Excel dataset. You can find the corresponding dataset in the corresponding folder directory.

2.5. Append/Replace Data

Click a dataset in the folder directory to enter the dataset overview page. Click "Append Data" or "Replace Data" in the upper right corner and follow the page prompts to append/replace data.

When appending/replacing, if the uploaded dataset and the original dataset fields are inconsistent, you can associate the fields yourself and upload the data. When multiple datasets with the same header are uploaded to the same dataset, multiple files can be appended/replaced to this dataset at once.

Difference between manual table data append and replace?

When appending, field deduplication is supported. Click "Enable Primary Key Deduplication" to select the fields to deduplicate. When there are duplicate fields, the data will be replaced with new data.

A primary key refers to one or more fields in a table whose values are used to uniquely identify a record in the table, such as member number, product number, sales document number, etc.

When appending data to a manual table, why does the data volume expand if you use exactly the same dataset and select deduplication primary key?