Tuesday, March 30, 2021

MySQL - Export Parts Tables to CSV Files

Use Parts > Tools > Export for MySQL

Or tables can be exported using MySQL Workbench.

Right Click on a table and select the Table Data Export Wizard.


Recommended Options for Parts:  

Set File Path and file name to a location on the local machine.

Select csv

Line Separator:  LF (linefeed)

Enclose Strings in: " (double quote)

Field Separator: | (pipe)

Null: Yes

Check export to local machine.

Select Next > Next > Finish

Related Links:  Export to Excel

That's it !

Saturday, March 27, 2021

MySQL - LOAD DATA to Parts

MySQL Workbench and LOAD DATA can be used to quickly load a large number of records from a  *CSV file to a MySQL database hosted on a Local or Remote Server.

Important !

The CSV file data structure must match the number of fields in the table and the type of data in each field. The data in each column must be in exactly the same order as the fields.

If the file and table structures are not matched data will be placed in the wrong fields.

Using the Parts Frontend application to Load Data

Parts includes options to delete and replace all records in the destination database or to append new records and update records with matching primary keys. 

The Parts Frontend will only Load Data to MySQL if the Parts Table and *.CSV file structures are compatible. 

Parts will create a Heading.txt file that can be used to compare the headings (fields) in the source data and destination table. The first column is the headings (fields) in the source data (*.csv file) and the second column is the destination table's fields.

A side by side comparison of the Imported and Destination fields can be made by selecting Display Unsorted Fields (Parameters) in the Import Dialog.

Tip: Drag and Drop the Headings.txt file in to an open session of Excel.  Add a third column and fill the column with a relative cell formula =IF(A1=B1,"Match","Mismatch") to find the differences between the data structure in the file and the destination table.

=IF(A1=B1,"Match","Mismatch") 


















Use MySQL Workbench to enable Loading Data from a Local *.CSV file.

SHOW VARIABLES LIKE "local_infile";

If the value returns “OFF” then set the value to “ON”.

Example:
SET GLOBAL local_infile = 1;

















After setting local_infile variable you can close the MySQL Workbench and use Parts > Tools > Load Data MySQL or Import Excel to quickly Load Data into MySQL.

Or you can use MySQL Workbench to Load Data . . .

Using the Parts Frontend to LOAD DATA is simpler if GLOBAL local_infile = 1

C:\Program Files\MySQL\MySQL Workbench 8.0 CE\MySQLWorkbench.exe

To LOAD DATA using the MySQL Workbench, edit the connection (click the wrench icon by the MySQL Connections, or right-click on a particular connection and choose Edit Connection...). On the Advanced tab, in the "Others:" box, add the line OPT_LOCAL_INFILE=1


















Use a full path to the *.csv file that is stored on a Local machine.
Use forward slashes ( / ) as shown in the examples below.

Tip: A good way to remember the difference between a backslash and a forward slash is that a backslash leans backwards ( \ ), while a forward slash leans forward ( / )

Example for loading the parts table.

TRUNCATE parts.parts
LOAD DATA LOCAL INFILE "C:/temp/parts.csv" INTO TABLE parts
FIELDS TERMINATED BY '|' 
ENCLOSED BY '"'
ESCAPED BY ''
IGNORE 1 LINES;

Example for loading the mfr_links table.

TRUNCATE mfr_links.mfr_links
LOAD DATA LOCAL INFILE "C:/temp/mfr_links .csv" INTO TABLE mfr_links 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '"'
ESCAPED BY ''
IGNORE 1 LINES;

Example for loading the supplier_links table.

TRUNCATE supplier_links .supplier_links 
LOAD DATA LOCAL INFILE "C:/temp/supplier_links .csv" INTO TABLE supplier_links 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '"'
ESCAPED BY ''
IGNORE 1 LINES;















Tips:

ESCAPE BY '' uses two single quote characters.
ENCLOSED BY ' " '  uses a single, a double and a single quote character.

To permit the Workbench access to Local Files, edit the connection (click the wrench icon by the MySQL Connections, or right-click on a particular connection and choose Edit Connection...). On the Advanced tab, in the "Others:" box, add the line OPT_LOCAL_INFILE=1

Source:

Useful Links:

LOAD DATA Statement - MySQL 8.0 Reference Manual
Connect-with-local-infile-option-in-mysql-workbench - Stack Overflow

That's it !

Wednesday, March 24, 2021

Parts Features and Configuration Options

The Parts download includes a demo Access database (Parts.accdb) and a compiled Access Client application (Parts_Frontend.accde).

Backend options - not required

Users can set Default inputs for new records.
Users can rename the Parts Database Parts.accdb

Note, that if Parts.accdb is renamed the Parts.DbLib needs to be opened in Altium and updated to point to the renamed Parts database.

Users can rename the Parts.DbLib to associate the Parts Library with a company name.
Example: Totum.DbLib.

Parts Frontend - Configuration Features Include:

Compatible with MySQL Local and Remote databases.
Access to MySQL database Migration Tools (Export *csv, LOAD DATA)
Drag and Drop Supplier parametric data from Digi-Key or Mouser.
Make Like can be used to make a similar part.
View / Edit mode for Displayed Records
Intelligent Cascaded Search Filter Dialog
Data entry with automated Trim and Clean 
Verify Library Files and Library Refs
Librarian Tools to Import Excel, Export Excel and Compare Data
Optional Daily Backups

Default Comment (User Defined)
Default Description (User Defined)
The MCN Field Name can be changed to the Company Name
RoHS Dropdown List
Lifecycle (Parts Status)
15 User Defined Fields (and 2 smart fields)
Preferred Manufacturer and Supplier Names (User Option)
Dynamic dropdown lists for Part Types and Sub Types
Bookmark with Jump to Record ID

URL Links to Manufacturers and Suppliers (User Option)
URL Link to Octopart and Supplier Part Details
URL Link to Datasheet and CAD Models

GUI options, Dark Theme, Show Access Ribbon (User options)

Optional Altium Scripts To:

Place currently selected part in Altium schematic.
Cross selection from Altium schematic to the Parts database.
Refresh Library (Altium Component panel)

Discussion Points:
Make Like (ROI)
Using Supplier Solutions - Performance Trade Offs

For Online Technical Support Contact Parts

That's it !

Friday, March 19, 2021

Migrate to Parts Using Tools > Import Excel

Take a Project Snapshot 

Backup (Zip the Project)
Output the Schematic and BOM (save for reference and change reports)
Create Project SCHLIB and PCBLIB Libraries.

Altium Parameter Manager:

Optional (not required)  - To identify Library Ref for parts placed from DBLIBs
Using the Parameter Manager create a parameter named Symbol Ref for all parts in the project schematic and copy the LIbrary Ref to the Symbol Ref parameter. Or run the Altium Script "MakeSymbolRefs" to create Symbol Ref from placed parts. The "MakeSymbolRefs" script is included in the Parts Download.

Altium Reports > Bill of Materials

Export Project BOM, group by Mfr P/N, include Footprint and Library Ref .

Optional Parts Application - User Defined Fields
Select Configuration > User Defined Fields
Add user defined fields:  RefDes, Qty

Required:
Place copies of the project libs in the DBLib working folders (footprints and symbols)

Parts FE > Import Project BOM
Parts FE > Set Library and Footprint Paths to saved Project Libs

Altium Components Panel:
Uninstall All Database Libraries, except for your DBLIB database.

Altium DBLink:
Open or Create a DBLink to your Parts DBLIB database

DbLink > Options > Do Not Update (Global)
DbLink > Options > Do Not Add (Global)
DbLink > Options > Do Remove (Global)

DbLink > Single Key Lookup = Mfr Part Number || Mfr Part Number 

DbLink > Options > ID Update
DbLink > Options > ID Add only if not blank in database
DbLink > Options > ID Remove only if blank in database

Tools > Update Parameters from Database

Altium > Remove Parts DbLink from the Project.

Altium > Components Panel > Install or Activate Parts DbLib Database Library.

Altium > Close and Open (Restart) Altium to clear Cached Libraries.

Altium > Schematic > Use Find Similar > Select All Components > Properties Panel > Source > Part.DbLib - Parts

Altium > Tools > Parameter Manager > Copy ID Column to Library Reference Column > Accept Changes

Save Schematics > Close Schematics > Open Schematics.

Tools > Update From Libraries

Review Schematic and BOM, fix as needed.

That's it !