0

I have an Excel table (can be converted to XML or CSV for manipulating) of this structure:

| License-plate | Parking | Fuel | Cleaning |
---------------------------------------------
| 1111AAA       | 234     | 21   | 1244     |
| 2222AAA       | 22      | 12   | 644      |
| 3333BBB       | 523     | 123  | 123      |

Which is a monthly spending for parking, fuel, etc. per car/month.

License plate is unique value in the table.

I need to convert this table in this to import it to MySQL, but I don't know how to do that and which tool is good for it:

| License-plate | Concept  | Amount |
-------------------------------------
| 1111AAA       | Parking  | 234    |
| 1111AAA       | Fuel     | 21     |
| 1111AAA       | Cleaning | 1244   |
| 2222AAA       | Parking  | 22     |
| 2222AAA       | Fuel     | 12     |
| 2222AAA       | Cleaning | 644    |
| .......       | ........ | .....  |

In the result table License-plate is not the unique value, and it's repeated for the number of concepts it have.

UPD: Just discovered that it can be called denormalized data (maybe not exactly).

burdiyan
  • 315
  • 2
  • 12

2 Answers2

0

I would do it with MySQL, the following way:

Import the table (after converting it to CSV) into MySQL. Let's call it source

CREATE TABLE source (
  License_Plate char(7) primary key,
  Parking int(8) unsigned,
  Fuel int(8) unsigned,
  Cleaning int(8) unsigned
);

LOAD DATA INFILE 'path/to/file' INTO TABLE source FIELDS TERMINATED BY ',';

Create another table with the desired final structure, let's call it destination

CREATE TABLE destination (
  License_Plate char(7),
  Concept varchar(10),
  Amount int(8) unsigned
);

Perform the following queries

INSERT INTO destination
SELECT License_Plate, 'Parking' as Concept, Parking as Amount
FROM source

INSERT INTO destination
SELECT License_Plate, 'Fuel' as Concept, Fuel as Amount
FROM source

INSERT INTO destination
SELECT License_Plate, 'Cleaning' as Concept, Cleaning as Amount
FROM source

Things to consider:

  1. I declared License_Plate as a primary key, just based on your example. This might not be the case if it repeats in real data. Also, if you have more than one row on the source table for the same license plate, you probably need to adjust my 3 queries to aggregate values.
  2. Also, the datatypes are adjusted to the sample data, you might need to change it if you have values with more than 8 digits, for instance.
  3. LOAD DATA is one way in which you can upload your CSV. It has many options, you should check them out. You can also do it with some tools, so as not to write that statement.
  4. Last, those table names were chosen as an example, you should come up with better ones, that represent your problem domain.

Hope this helps you.

Mariano D'Ascanio
  • 1,202
  • 2
  • 16
  • 17
  • Thank you for you advice. It looks like it should work, but may be I'm doing something wrong. I've tried to run only one part of the query first, and get `"Column count doesn't match value count at row 1"` error. – burdiyan Aug 01 '14 at 11:07
  • @user3830159 I've expanded my answer to include table creation and a possible way of uploading the CSV. If that error happens when you try to upload the file, check whether Excel has added a comma after every line. MySQL could interpret it as another (non-existent) field. – Mariano D'Ascanio Aug 01 '14 at 12:41
  • thank you! I knew how to create tables, and I have inserted data to test manually. Finally I could get it working. The error was because of id field I suppose. I've deleted it. And it works. Thank you so much! – burdiyan Aug 01 '14 at 13:11
0

The comment of @pnuts helped me. It is very easy solution and can be done in Excel. Thank you!

The solution is: Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')

Community
  • 1
  • 1
burdiyan
  • 315
  • 2
  • 12