mysql trying to import an odd format csv

Question

I have an excel CSV file where the customer was recording invoices. The made a new column for each vendor and put the amounts under each column.

Like this:

ABC Company      Jacks Garage    XYZ Company
123.45            223.22          123.11
423.11             10.22           11.21

Etc. I am trying to guess how to get that into two columns (Vendor, Amount) so I can import that data into the actual table. There are about 200 vendors so doing this manually cut and paste would work be take forever.

Can I do this with a loop somehow and insert the info into the 2 column table?

Answer 1

I would do this by writing a simple script written in just about any language, e.g. Python, PHP, Ruby, or even Perl. Any of those languages make it easy to read a text file, split the fields into an array, and post the fields into a database in whatever manner you want.

Alternatively, you could do this without writing code, but in the following steps:

1. Load the CSV file as-is into a table.

   create table invoices_asis (
     rownum serial primary key,
     abc_company_amount numeric(9,2),
     jacks_garage_amount numeric(9,2),
     xyz_company_amount numeric(9,2)
   );
   
   load data infile 'invoices.csv' into table invoices_asis ignore 1 lines
   (abc_company_amount, jacks_garage_amount, xyz_company_amount);
   

2. Then copy all data for each given vendor to your (vendor, amount) table.

   create table invoices (
     invoice_id serial primary key,
     vendor varchar(20),
     amount numeric(9,2)
   );
   
   insert into invoices (vendor, amount)
   select 'ABC Company', abc_company_amount from invoices_asis;
   
   insert into invoices (vendor, amount)
   select 'Jacks Garage', jacks_garage_amount from invoices_asis;
   
   insert into invoices (vendor, amount)
   select 'XYZ Company', xyz_commpany_amount from invoices_asis;
   

3. Finally, drop the as-is table.

   drop table invoices_asis;
   

Answer 2

I think what you want is to 'unpivot', for which there re many options (eg for Excel).

Insert a blank column at the extreme left then include that with your data at 4. in the example. At 6. move Column into ROWS above Row. Double-click the Grand Total and remove left-hand column.