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.
ABC Company Jacks Garage XYZ Company
123.45 223.22 123.11
423.11 10.22 11.21
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:
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);
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;
Finally, drop the as-is table.
drop table invoices_asis;