Need to import from CSV to MySQL

Would like to use PHP, but could be a command line

[mikel]

I need to import a MSN AdCenter Report file that looks like the following, into a MySQL database. I would also like to grab the date listed next to Report View to insert into my table. Is this possible?

Report: Daily_Emailed_Ad_group_performance
Report View: 10/2/2007
Include rows where: Account: test.net;
Rows: 3

Account Name,Campaign,Order name,Medium,Imp,Clicks,CTR,Avg CPC,Total Cost,Avg Pos
test.net,Core Keywords,$10 CPA Target,Search,21,0,0.0000,.00,.00,5.48
test.net,Core Keywords,$10 CPA Target,Content,153,0,0.0000,.00,.00,2.95
test.net,Core Keywords,$15 CPA Target,Search,4783,20,0.4181,2.82,56.35,5.78

©2007 Microsoft Corporation. All rights reserved.

[phparion]

you can do it with mysqlimport or simply use phpmyadmin

[Habtom]

I don't believe you can just import the file.

The data somehow need to be rearranged.

You may perhaps write a small PHP script which can grab the 4 lines on the top as a table columns on the right side of the rest of the list.

If you try doing it this way, post any problems you face on the way.

Habtom

[dldeskins]

You can import this data from command line. You can do it from phpMyAdmin, but you are limited on the file size.

It has been awhile since I have done it, but I believe you have to single quote (') the text fields (varchar, char, etc.) before you can import. I have imported megs of data in just a few minutes. See documentation on mysqlimport (command line utility) on the MySQL site.

[hughie]

using phpmyadmin is the easiest option if the file is <30mb or so, make sure you use the "LOAD DATA" option as it's far far faster. I do mine locally and then just export the SQL file.

[jatar_k]

Welcome to WebmasterWorld mikel,

you could also try this thread
[webmasterworld.com...]

though instead of writing to another file you would be building an insert query

[mikel]

Thanks for all the responses. I am trying to automate this as much as possible, and like Habtom described, I can't just use a standard import on this. The file has some header and footer information that I need to strip out, and also take a value from (the date).

Report: Daily_Emailed_Ad_group_performance
Report View: 10/2/2007
Include rows where: Account: test.net;
Rows: 3

Account Name,Campaign,Order name,Medium,Imp,Clicks,CTR,Avg CPC,Total Cost,Avg Pos
test.net,Core Keywords,$10 CPA Target,Search,21,0,0.0000,.00,.00,5.48
test.net,Core Keywords,$10 CPA Target,Content,153,0,0.0000,.00,.00,2.95

I can find PHP Code Examples to do an import using an Array, however, I can't figure out how to get past the header information. It always seems to try to get imported.

I will post any progress I make.

Thanks.

[jatar_k]

if you use a loop to import it and those header lines always have some recognizable pattern then just use an if statement to skip them

if you're using something like this to get the lines

while ($row = fgetcsv($sfp,10000,",","")) {

then use an if at the beginning of the loop. fgetcsv [php.net] returns an array. It splits the lines on , where your first few lines have no comma, therefore you should be able to test for the number of elements in the array

if only one element in the array then do nothing and go to next iteration

[mikel]

Thanks to everyone for the help here. I figured out a way to do it with Navicat and some changes to the report source. Thanks.