Databases
StackOverflow2013
Postsdbo
POCSV to MYsql with PHP
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCSV to MYsql with PHP
    primarykey
    data
    text
    <p>Please don't close this question, I know there are lots of solutions already buy none of them seem to work or at least isn't working for me. I've csv file from google affiliate network product feed, it comes in zip, .txt (csv) tab delimiter. I've so far unziped file and read file, but what I want to do is dump that data to mysql database, 1st row of csv are headers, I want to create field names according to csv file and put data in database respectively, some fields in csv file are empty. I also want capability to run it again (possibly I'll run cron job for that) and insert only new data in there. example of my csv file:</p> <pre><code>ProductID ProductName ProductURL BuyURL ImageURL Category CategoryID PFXCategory BriefDesc ShortDesc IntermDesc LongDesc ProductKeyword Brand Manufacturer ManfID ManufacturerModel UPC Platform MediaTypeDesc MerchandiseType Price SalePrice VariableCommission SubFeedID InStock Inventory RemoveDate RewPoints PartnerSpecific ShipAvail ShipCost ShippingIsAbsolut ShippingWeight ShipNeeds ShipPromoText ProductPromoText DailySpecialsInd GiftBoxing GiftWrapping GiftMessaging ProductContainerName CrossSellRef AltImagePrompt AltImageURL AgeRangeMin AgeRangeMax ISBN Title Publisher Author Genre Media Material PermuColor PermuSize PermuWeight PermuItemPrice PermuSalePrice PermuInventorySta Permutation PermutationSKU BaseProductID Option1 Option2 Option3 Option4 Option5 Option6 Option7 Option8 Option9 Option10 Option11 Option12 Option13 Option14 Option15 Option16 Option17 Option18 Option19 Option20 4181615950845mkTWIN~TWIN Brylanehome Jasmine Quilt Set (Sea Green,Twin) http://gan.doubleclick.net/gan_click?lid=41000613802463546&amp;pid=4181615950845mkTWIN~TWIN&amp;adurl=http%3A%2F%2Fwww.brylanehome.com%2FProduct.aspx%3FPfId%3D20653%26ProductTypeId%3D2%26affiliate_id%3D017%26mr%3AtrackingCode%3D8C875316-BB51-E211-9A4A-90E2BA0278A8%26mr%3AreferralID%3DNA&amp;usg=AFHzDLtiRj_wSyAQFEPxMBFVo4HjTeHMVA&amp;pubid=21000000000568460 http://gan.doubleclick.net/gan_click?lid=41000613802463546&amp;pid=4181615950845mkTWIN~TWIN&amp;adurl=http%3A%2F%2Fwww.brylanehome.com%2FProduct.aspx%3FPfId%3D20653%26ProductTypeId%3D2%26affiliate_id%3D017%26mr%3AtrackingCode%3D8C875316-BB51-E211-9A4A-90E2BA0278A8%26mr%3AreferralID%3DNA&amp;usg=AFHzDLtiRj_wSyAQFEPxMBFVo4HjTeHMVA&amp;pubid=21000000000568460 http://media.redcatsecom.com/brylanehome/mc/1595_41816_mc_0845.jpg?wid=230&amp;hei=331&amp;qlt=95&amp;op_sharpen=1 Bedding &gt; Quilts Picture perfect on any bed, this pieced and stitched quilt set is the ideal choice for spring bedding. &amp;#160; &amp;#8226; A BrylaneHome&amp;#174; Exclusive!&amp;#160; &amp;#8226; all-over floral print quilt reverses to a stripe &amp;#8226; scalloped edges &amp;#8226; 100% cotton Picture perfect on any bed, this pieced and stitched quilt set is the ideal choice for spring bedding. &amp;#160; &amp;#8226; A BrylaneHome&amp;#174; Exclusive!&amp;#160; &amp;#8226; all-over floral print quilt reverses to a stripe &amp;#8226; scalloped edges &amp;#8226; 100% cotton face/cotton/poly fill &amp;#8226; available in 3 sizes: Twin 2-Pc. Set 68" x 90", Full/Queen 3-Pc. 86" x 86", and King 3-Pc. 100" x 90" &amp;#8226; machine wash/dry &amp;#8226; imported &amp;#8226; pair this quilt with any of our colorful and elegant sheets to create a dynamic layered look &amp;#8226; and shop our selection of total bed sets to reinvent your d&amp;#233;cor overnight &amp;#160; This vibrant quilt set includes: 1 quilt and 2 standard shams (1 with twin).&amp;#160; &amp;#160; Why Buy?&amp;#160; Our customers agree that our patchwork bedding shows off their vibrant and fun d&amp;#233;cors while offering them incredible comfort at great values.&amp;#160; BrylaneHome Brylane Home 4181615950845mkTWIN~TWIN new 114.99 59.99 in stock 47 adult Brylanehome Jasmine Quilt Set (Sea Green,Twin) SEA GREEN TWIN Home &amp; Garden &gt; Linens &amp; Bedding &gt; Bedding &gt; Quilts &amp; Quilt Sets 4181615950845mkTWIN~TWIN 1595-41816 </code></pre> <p>Script I used to unzip:</p> <pre><code>&lt;?php $zip = new ZipArchive; $res = $zip-&gt;open('K9349.zip'); if ($res === TRUE) { $zip-&gt;extractTo('extracted/'); $zip-&gt;close(); echo 'Unzip was successful'; } else { echo 'Unzip was not successful'; } ?&gt; </code></pre> <p>Below are two scripts that I tried without any luck. script 1:</p> <pre><code>&lt;?php $row = 1; if (($handle = fopen("extracted/K9349.txt", "r")) !== FALSE) { while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $num = count($data); echo "&lt;p&gt; $num fields in line $row: &lt;br /&gt;&lt;/p&gt;\n"; $row++; for ($c=0; $c &lt; $num; $c++) { echo $data[$c] . "&lt;br /&gt;\n"; } } fclose($handle); } ?&gt; </code></pre> <p>script 2:</p> <pre><code>&lt;?php $host = '****'; $user = '****'; $pass = '****'; $database = '****'; $db = mysql_connect($host, $user, $pass); mysql_query("use $database", $db); /********************************************************************************/ // Parameters: filename.csv table_name $file = 'extracted/K9349.txt'; $argv = $_SERVER[argv]; if($argv[1]) { $file = $argv[1]; } else { echo "Please provide a file name\n"; exit; } if($argv[2]) { $table = $argv[2]; } else { $table = pathinfo($file); $table = $table['filename']; } /********************************************************************************/ // Get the first row to create the column headings $fp = fopen($file, 'r'); $frow = fgetcsv($fp); $ccount = 0; foreach($frow as $column) { $ccount++; if($columns) $columns .= ', '; $columns .= "$column varchar(250)"; } $create = "create table if not exists $table ($columns);"; mysql_query($create, $db); /********************************************************************************/ // Import the data into the newly created table. $file = $_SERVER['PWD'].'/'.$file; $q = "load data infile '$file' into table $table fields terminated by ',' ignore 1 lines"; mysql_query($q, $db); ?&gt; </code></pre> <p>I'll be really thankful if you can help. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USKhushwant Singh
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCSV to MYsql with PHP
      1. USJocelyn
      1. VTFavorite
    1. COIf your CSV file is tab delimited why are using `,` as delimiter in script 1. Try using `while(($data = fgetcsv($handle, 1000, "\t")) !== FALSE)`.
      singulars
      1. POCSV to MYsql with PHP
      1. USuser1190992
    2. COscript 1 is at no use, I've done it is just funny output there, even if it's not funny how can I send this data to database table. here is output for [script1](http://www.lostknowledge.org/google/readcsv.php) it's just not reading it right because there are six thoudans some record in csv file. here is output for [script2](http://www.lostknowledge.org/google/savedata.php) here is the csv [file](http://www.lostknowledge.org/google/extracted/K9349.txt)
      singulars
      1. POCSV to MYsql with PHP
      1. USKhushwant Singh
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload