Mark Worsnop Mark Worsnop - 6 months ago 23
MySQL Question

mysql trying to import an odd format csv

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?


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;