CW provides a .zip file containing several files. Usually 27 files. These are:

You can open these files using any preferred text editor app to see their content. Usually, you will only need the files with the following extensions:

• .N12 and .U12 – car models
• .RNG – prices
• .REC – old and new glass codes

.N12 and .U12 looks like below:

.RNG looks like below:

.REC looks like below:

PREPARING FILES BEFORE IMPORT

To map data to their respective columns in the database, open Microsoft Excel or any equivalent app and open the files with the extensions mentioned above. It should prompt you with the same screen below:

On that prompt screen you can find the field “Start import at row”, which has a value of “1”. Change that “1” to “4” because we will only get the data from row 4 downwards. Then, hit NEXT where you can see:

From the second screen, you will see the instructions:

• To CREATE a break line, click at the desired position.
• To DELETE a break line, double click on the line.

These instructions are important to note because we will notice some of the columns have multiple break lines. These break lines when opened to excel forms a column. For example, the TRANSMISSION column has 5 break lines, which should only have 2 lines [the start and end marks of the column]:

Browse all columns because there are still others to correct. Then, hit NEXT again.

Just click “FINISH” on the last screen. You should have like this:

Insert 1 COLUMN to the left of the existing columns. It will look like this:

These blank columns will be filled with quotes and commas. For COLUMN A, insert “(‘” [double quotes not included. It’s only OPEN PARENTHESIS/SINGLE QUOTE], for columns B to AI, insert “‘, ‘” [double quotes not included. It’s only SINGLE QUOTE/COMMA/SPACE/SINGLE QUOTE]. And on the last column AK, insert “’),” [double quotes not included. It’s only SINGLE QUOTE/CLOSE PARENTHESIS/COMMA]. But on the last cell at the bottom of the COLUMN AK, insert “’);” [double quotes not included. It’s only SINGLE QUOTE/CLOSE PARENTHESIS/SEMICOLON].

The semicolon at the last cell is very important because it closes the statement. Failure here will break the import and produces error.

Then, select all cells of that excel spreadsheet and “copy” and paste onto a text editor app. Pasted content looks like this:

Remove all blank spaces between TEXT and SINGLE QUOTE, for example “(‘ TEXTHERE” [double quotes not included. It’s only OPEN PARENTHESIS/SINGLE QUOTE/SPACE/TEXT]. After removing, it will be like OPEN PARENTHESIS/SINGLE QUOTE/TEXT. Also, do this to TEXTHERE/SPACE/SINGLE QUOTE/COMMA, which will be like TEXTHERE/SINGLE QUOTE/COMMA after removing blank spaces. The result will be like below:

Then, SELECT ALL the text and copy and paste them into the exported .sql described below [bottom of this guide]. You will find the same structure in the .sql file. Replace them with this new one. Replace only the text having the same format/structure and leave all others as-is.  “carswantedcom_wp3_carmodels_table_only.sql” and “carswantedcom_wp3_prices_table_only.sql” are included in the zip file as sample.

ACTUAL UPDATE PROCESS [Either insert through a command or Empty/Import new content]

Open CW database using PHPMyAdmin or any preferred MySQL client. cPanel has PHPMyAdmin. You can update database tables’ “prices” and “carmodels” by either directly inputting commands in PHPMyAdmin or just empty or drop them and import the new one. If you choose the first, insert rows to carmodels by using “INSERT IGNORE INTO”. We are using IGNORE because there are duplicates most of the time. Inserting rows to “carmodels” table goes like this:

INSERT IGNORE INTO `carmodels` (`CODE`, `MTH`, `MAKE`, `FAMILY`, `VARIANT`, `SERIES`, `STYLE`, `ENGINE`, `CC`, `SIZE`, `TRANSMISSION`, `CYL`, `WIDTH`, `NVIC`, `YEAR`, `BT`, `ET`, `TT`) VALUES
(‘AUDA6-AL4930J7R2009C’, ‘Mar’, ‘AUDI’, ‘A6’, ‘ALLROAD QUATTRO’, ‘4F MY09’, ‘4D WAGON’, ‘DIESEL TURBO F/INJ’, ‘2967’, ‘3.0L’, ‘6 SP AUTOMATIC TIPTR’, ‘DTV6’, ‘8’, ‘J7R09C’, ‘2009’, ’11’, ‘V’, ‘6T’),
(‘AUDA6-AL4930J7R2010A’, ‘Jan’, ‘AUDI’, ‘A6’, ‘ALLROAD QUATTRO’, ‘4F MY09’, ‘4D WAGON’, ‘DIESEL TURBO F/INJ’, ‘2967’, ‘3.0L’, ‘6 SP AUTOMATIC TIPTR’, ‘DTV6’, ‘8’, ‘J7R10A’, ‘2010’, ’11’, ‘V’, ‘6T’);

Inserting rows to “prices” table goes like this:

INSERT IGNORE INTO `prices` (`CODE`, `NVIC`, `AVE`, `PRIVATE`, `TRADE`, `RETAIL`) VALUES
(‘ABA500ES–14LVX2011C’, ‘LVX11C’, ’79’, ‘18370-20880’, ‘14400-16700’, ‘19520-23210’),
(‘ABA500ES–14LVX2012A’, ‘LVX12A’, ’68’, ‘19580-22250’, ‘15400-17800’, ‘20810-24750’),
(‘ABA500ES–14LVX2013A’, ‘LVX13A’, ’54’, ‘21120-24000’, ‘16600-19200’, ‘22480-26730’),
(‘ABA500ES–14LVX2014A’, ‘LVX14A’, ’40’, ‘22880-26000’, ‘18000-20800’, ‘23770-28270’),
(‘ABA500ES–14N4J2011K’, ‘N4J11K’, ’70’, ‘20460-23250’, ‘16100-18600’, ‘21740-25850’),
(‘ABA500ES–14N4J2012A’, ‘N4J12A’, ’68’, ‘22330-25380’, ‘17600-20300’, ‘23220-27610’);

If you prefer the second option [empty “carmodels” and “prices” table and import the new one], just export these tables and use the exported .sql file as a template. That template contains the same INSERT statement plus extra database stuff. Since it has no IGNORE, modify and make it INSERT IGNORE INTO and just replace the content of the rows to reflect the new values. Then, save and import that modified .sql file into CW database using PHPMyAdmin or any preferred client.

THAT’S IT!

NOTE: The imported data into “carmodels” table contains EARLY CAR MODELS like year 2004 and earlier. If the client wants them deleted, you can delete it inside PHPMyAdmin. For example:

DELETE FROM `carmodels` WHERE `YEAR` < ‘2005’