How to Keep Description Formatting When Uploading to Magento

Using the File Uploader

Magento BI is powerful not just because of its visualization features, but because it gives you the power to put all of your data into a unmarried data warehouse. Even information that lives outside your databases and integrations can exist brought into Magento BI by using the File Upload tool in the Data Warehouse Manager. Note that yous need Admin permissions to access employ the File Uploader.

Let's employ ad campaigns as an case. If y'all're running both online and offline campaigns, you can't become the whole moving picture if you're merely analyzing data from an online integration. Uploading a spreadsheet with the offline campaign data allows you to analyze both sets of data and become a more robust understanding of your campaign performance.

Restrictions and requirements

The but supported format for file uploads is CSV, or comma separated values. If you lot're working in Excel, yous can use the Salve As role to salvage the file in CSV format.
CSV files must employ UTF-8 encoding. The majority of the time, this will not be an upshot. If you encounter this error while uploading a file, consult this Support article.
Files can't be larger than 100MB. If the file is larger than this, separate the table into chunks and save them as individual files. You can use append the data afterwards the initial file is loaded.
All tables must have a primary key. There needs to exist at least one cavalcade in your table that can be used every bit a master key, or a unique identifier for each row in the table. Whatever column designated as a primary key tin can never be nothing. A master key can be as uncomplicated every bit adding a column that gives a number to each row, or tin can be two columns concatenated to brand a cavalcade of unique values (e.g., campaign name + date).

If a column (or columns) are designated as unique but there are duplicates, the duplicate rows won't exist imported.

Formatting information for upload

Before you tin upload your information into Magento BI, cheque that it's formatted according to the guidelines in this section.

To ensure that columns are labeled and imported properly, brand sure the offset row of your spreadsheet is a header that describes the data in each cavalcade.

Cavalcade names must be unique and contain just letters, numbers, spaces and these symbols: $ % # /. If a column name contains a comma, information technology will be split into two columns when the file uploads. Additionally, we recommend that in that location be less than 85 columns in the file to optimize update speed.

Data with commas

Because files have to be in CSV format, the use of commas tin can cause bug with uploading data. CSV files employ commas to indicate new values, therefore a column with a name like Campaigns, August volition be read every bit two columns (Campaigns and August) instead of one, shifting all of your data over one row. We recommend avoiding commas wherever possible. You tin can apply Data Preview to see if your information is displaying correctly one time an update completes.

Dates

Whatever dataset that includes dates must use the standard date format (YYYY-MM-DD HH:MM:SS) or MM/DD/YYYY.

Special Characters

Some special characters are not accustomed. For example, the piping symbol "|" is interpreted as creating a new column and volition cause errors when uploading a file.

Decimal Numbers

Currency values should have the datatype Decimal Number selected, and these columns will automatically round to two decimal places in your data warehouse. If you do not desire to round your decimal numbers or have a degree of precision greater than this, you should select the Non-Currency Decimal Number datatype.

Percentages

Percentages must be entered as decimals. For instance:

Right:	Wrong:
.05	5%
.23	23

Values with leading and/or abaft zeroes

Some values in your file - similar Goose egg codes and IDs - may brainstorm or end with zeroes. To ensure the zeroes are properly retained and uploaded, y'all tin change the formatting type (i.e., from number to text) or enforce number formatting.

Permit's use United states of america Goose egg codes every bit an example of how to change number formatting. In Excel, highlight the column containing Null codes and change the number format to "Naught code". You can as well select a custom number format, and in the Type window, enter "00000". Keep in listen this method could present problems if some codes are formatted as 00000 and others are 00000-0000.

The Type can be formatted differently to accommodate other data types, such as IDs. If an ID is nine digits long, for example, the Type could be "000000000" or "000-000-000". This would alter "123456" to "000-123-456".

For Google Docs and Apple tree Numbers resource, refer to the Related list at the bottom of this page.

Uploading data

Now that your spreadsheet is correctly formatted and Magento BI-friendly, let's add it to your data warehouse.

To become started, get to Data > File Uploads.
Click the Upload to New Table tab.
Click the Choose File button and select the file. Click Open to start the upload.

After the upload completes, y'all'll see a list of the columns Magento BI found in your file.
Check that the column names and datatypes are correct. Specifically, check that any date columns are being read as dates and not numbers.

The datatype is extremely of import, and so don't skip this footstep!
Select the cavalcade (or columns) that will make upwards the primary key for the table by using the checkboxes under the key icon.
Name the table.
Click Save Table.

A Success! message appears at the top of the screen after your table is saved.

If yous need a visual, here'south a look at the whole process:

Uploaded tables display under the File Uploads section of the tabular array listing (in both the All Tables and Synced Tables options) in the Data Warehouse Manager:

Updating or appending data to an existing table

Got some new data to add to a file y'all've already uploaded? No problem - yous can hands update and append data in Magento BI.

To get started, go to Manage Data > File Uploads.
Click the Edit/Upload CSV to Existing Tables tab.
In the driblet-down menu, click the proper name of the table you want to update or append.

Utilize the drop-down to select the option for treatment duplicate rows:

`Overwrite old row with new row`	This will overwrite existing data with new data if a row has the same primary key in both the existing table and the new file. This is the method to utilize for columns with values that change over time - for case, a Status column. Existing data will be overwritten and updated with the new data. Rows with primary keys not in the existing tabular array will exist added equally new rows.
`Retain onetime row; discard new row`	This will cause new information to be ignored if a row has the same principal fundamental in both the existing tabular array and the new file.
`Purge all existing rows first and ignore duplicate keys within the file`	This volition delete whatever existing data and replace it with the new information from the file. Y'all should merely apply this option if y'all don't demand any of the data in the existing table.

Click Choose File and select the file.
Click Open to get-go the upload.

Afterward the upload completes, Magento BI will validate the data structure in the file. A Success! message appears at the top of the screen later on your table is saved.

Data availability

Just like calculated columns, data from file uploads is available after the side by side update cycle completes. If an update was in progress during the file upload, the data won't be available until after the side by side update. In one case an update cycle is completed, you can navigate to the Data Preview tab in your data warehouse to ensure that the file uploaded correctly and data is displaying equally expected.

Wrapping up

This commodity covered only the basics for using importing information, simply we're betting y'all want to practise something a picayune more advanced. Check out the Related articles for guidance on formatting and importing financial, eCommerce, ad spend, and other types of data.

Additionally, file upload isn't the only style to go your data into Magento BI. The Information Import API allows you to push arbitrary data into your Magento BI data warehouse.

Formatting and importing fiscal data
Importing offline / other ad spend data
Expected Google ECommerce information

Third Party Resources

Numbers Data Formatting Guide
Google Docs Information Formatting Guide

wrightlattens83.blogspot.com

Source: https://docs.magento.com/mbi/data-analyst/importing-data/connecting-data/using-file-uploader.html