Blog

Create a MySQL table from spreadsheet columns

Turn supplier price list headings into MySQL columns, choose suitable data types and keys, and build a CREATE TABLE script to review before importing.

Megan FosterTools and utilities Tools 5 min read

You have a supplier price list with columns such as item code, description, price, currency, and effective date. To load it into MySQL, first create a table whose columns match the data and use types that preserve it correctly.

A spreadsheet converter can help with the file and its rows, but a column heading alone does not tell MySQL what kind of data belongs there. Use the steps below to make those decisions and write a CREATE TABLE statement you can check before importing prices.

Build the table definition

  1. Check the source columns. Open the CSV or Excel file in the Data Converter if you need to convert the file or its rows to SQL. Converting spreadsheet data to SQL statements is separate from designing the table: you still need to choose column names, types, and rules. Files opened in the browser are processed on your device and are not uploaded.
  2. Write down the headers and sample values. Include a few examples from each column, including blanks, decimal prices, dates, and codes with leading zeros. Sample values reveal what the column contains, while the supplier’s documentation or business rules tell you what it is supposed to contain.
  3. Choose SQL-friendly column names. Use short, clear names such as supplier_sku, unit_price, and effective_date. Avoid spaces and punctuation. If you keep a name that conflicts with a MySQL keyword or includes special characters, put it in backticks, for example `order`.
  4. Choose a data type for every column. Use the guide below, then check that it fits your actual values and rules. Do not choose a type just because the spreadsheet displays a value in a certain format.
  5. Decide how rows will be identified. Add a primary key, or use an appropriate combination of columns if the source already has a stable unique identifier. Do not assume an item code is unique across all suppliers.
  6. Write the CREATE TABLE statement. Put each column definition inside parentheses, separate definitions with commas, and add the table options after the closing parenthesis.

Choose types that preserve the data

Item codes and identifiers

Use a text type such as VARCHAR(80) for supplier item codes, account numbers, or other identifiers. A value like 000184 is a code, not a quantity. Storing it as a number can remove its leading zeros. Pick a length that accommodates the longest valid value, including letters or punctuation if the supplier uses them.

Prices and currencies

Use DECIMAL for prices, not FLOAT. Decimal types store a fixed number of decimal places, which is important for money calculations. For example, DECIMAL(12,2) allows up to 12 digits in total, with 2 after the decimal point. Change the number of decimal places if your price rules require it. Store the currency separately, for example in a CHAR(3) column containing a three-letter code.

Dates, quantities, and descriptions

Use DATE for dates without a time, and DATETIME if the source needs both date and time. Use an integer type for quantities that are whole numbers. Use VARCHAR for short descriptions with a practical maximum length, or TEXT for longer notes. Set a column to NOT NULL only when every valid row must have a value.

Example MySQL script

This example assumes a price list with supplier name, supplier item code, description, unit price, currency, effective date, and minimum order quantity. It adds its own numeric row ID. Adjust names, lengths, decimal places, and required fields to match your supplier’s data and rules.

CREATE TABLE `supplier_prices` (`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `supplier_name` VARCHAR(150) NOT NULL, `supplier_sku` VARCHAR(80) NOT NULL, `description` VARCHAR(255) NULL, `unit_price` DECIMAL(12,2) NOT NULL, `currency` CHAR(3) NOT NULL, `effective_date` DATE NULL, `minimum_order_qty` INT UNSIGNED NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

AUTO_INCREMENT gives each inserted row a new ID. PRIMARY KEY makes that ID unique and required. InnoDB is a common MySQL table engine, and utf8mb4 supports a wide range of characters in supplier names and descriptions.

Choose keys and constraints carefully

Use a generated ID when the source has no dependable key

A generated ID is useful when rows need a stable identifier but the spreadsheet does not contain one. It does not make duplicate supplier entries impossible. If duplicates matter, define which combination of fields should be unique according to your business rules.

Use a composite unique key only when it matches the rules

If each supplier can use the same item code as another supplier, a unique rule might apply to the pair of supplier and item code, rather than to the item code alone. Add a constraint such as UNIQUE (`supplier_name`, `supplier_sku`) only if that pair must never repeat. If the list contains separate price tiers, regions, or effective periods, those fields may also affect what counts as a distinct row.

Allow blanks only when they are meaningful

Use NULL for information that may genuinely be unknown or absent. Do not make a required price nullable just to get a spreadsheet import through. Find blank or invalid cells first, then correct them or decide how your database should represent missing values.

Review the script before importing

  1. Match every heading to a column. Check for omitted columns, duplicated headers, and accidental changes to names.
  2. Check values against their types. Confirm codes retain leading zeros, prices fit the chosen decimal places, quantities are whole numbers, and dates use valid values.
  3. Check the key rule against real rows. If you plan to add a unique constraint, look for repeated combinations in the supplier file first. A rule that is too strict can reject legitimate price tiers or date ranges.
  4. Test on a new table or test database. Run the CREATE TABLE statement in your MySQL client before loading the full file. If MySQL reports an error, review the column syntax, commas, parentheses, and any reserved words in your names.
  5. Load the rows separately. A CREATE TABLE statement defines the table structure; it does not add the spreadsheet’s prices. If you convert rows to SQL with the Data Converter, check that the resulting statements target the table you created and preserve codes, dates, and decimal values.

Keep a copy of the original price list. Before using the table for purchasing decisions, compare a sample of imported rows with the source, especially item codes, currency, and price decimals.

More articles