Showing posts with label import. Show all posts
Showing posts with label import. Show all posts

Apr 20, 2011

Export XML file directly into MySQL

CREATE SCHEMA xmltest;
CREATE TABLE cities (   
    name CHAR(35) NOT NULL DEFAULT '',
    country CHAR(52) NOT NULL DEFAULT '',
    population int(11) NOT NULL DEFAULT '0'
);

====================================================

INSERT INTO cities VALUES ('Mumbai (Bombay)','India',10500000);
INSERT INTO cities VALUES ('Seoul','South Korea',9981619);
INSERT INTO cities VALUES ('São Paulo','Brazil',9968485);
INSERT INTO cities VALUES ('Shanghai','China',9696300);
INSERT INTO cities VALUES ('Jakarta','Indonesia',9604900);
INSERT INTO cities VALUES ('Karachi','Pakistan',9269265);
INSERT INTO cities VALUES ('Istanbul','Turkey',8787958);
INSERT INTO cities VALUES ('Ciudad de México','Mexico',8591309);
INSERT INTO cities VALUES ('Moscow','Russian Federation',8389200);
INSERT INTO cities VALUES ('New York','United States',8008278);
=====================================================================
SELECT * FROM cities c;
----------------------------------------------------------------------
E:\Program Files\\products\core{db}\run\mysql\MySQL Ser
ver 5.1\bin>mysqldump -u root --xml -p  xmtest cities>xx.xml
Enter password: ********
xx.xml file is created in bin folder
==============================================================================
the data of xml file is
===================================================================================
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="xmtest">
 <table_structure name="cities">
  <field Field="name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
  <field Field="country" Type="char(52)" Null="NO" Key="" Default="" Extra="" />
  <field Field="population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
  <options Name="cities" Engine="InnoDB" Version="10" Row_format="Compact" Rows="10" Avg_row_length="1638" Data_length="16384" Max_data_length="0" Index_length="0" Data_free="4194304" Create_time="2011-04-14 14:17:59" Collation="utf8_general_ci" Create_options="" Comment="" />
 </table_structure>
 <table_data name="cities">
 <row>
  <field name="name">New York</field>
  <field name="country">United States</field>
  <field name="population">8008278</field>
 </row>
 <row>
  <field name="name">Moscow</field>
  <field name="country">Russian Federation</field>
  <field name="population">8389200</field>
 </row>
 <row>
  <field name="name">Ciudad de México</field>
  <field name="country">Mexico</field>
  <field name="population">8591309</field>
 </row>
 <row>
  <field name="name">Istanbul</field>
  <field name="country">Turkey</field>
  <field name="population">8787958</field>
 </row>
 <row>
  <field name="name">Karachi</field>
  <field name="country">Pakistan</field>
  <field name="population">9269265</field>
 </row>
 <row>
  <field name="name">Jakarta</field>
  <field name="country">Indonesia</field>
  <field name="population">9604900</field>
 </row>
 <row>
  <field name="name">Shanghai</field>
  <field name="country">China</field>
  <field name="population">9696300</field>
 </row>
 <row>
  <field name="name">São Paulo</field>
  <field name="country">Brazil</field>
  <field name="population">9968485</field>
 </row>
 <row>
  <field name="name">Seoul</field>
  <field name="country">South Korea</field>
  <field name="population">9981619</field>
 </row>
 <row>
  <field name="name">Mumbai (Bombay)</field>
  <field name="country">India</field>
  <field name="population">10500000</field>
 </row>
 </table_data>
</database>
</mysqldump>
================================================================================================================

Apr 13, 2010

Upload and import CSV file

 Select File:

<div>
        &nbsp;Select File:
        <asp:FileUpload ID="FileUploader" runat="server" />
        <br />
        <br />
        <asp:Button ID="UploadButton" runat="server" Text="Upload" OnClick="UploadButton_Click" />
        <asp:Button ID="btnsave" runat="server" onclick="btnsave_Click" Text="Save" />
        <br />
        <br />
        <asp:Label ID="Label1" runat="server"></asp:Label></div>

using System; using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq; using System.Data.SqlClient;
using System.IO;
namespace import { public partial class importcontacts : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=localhost;User id=sa;password=root;database=test");
}
protected void UploadButton_Click(object sender, EventArgs e)
{
if (FileUploader.HasFile)
try {
FileUploader.SaveAs(Server.MapPath("confirm//") + FileUploader.FileName); Label1.Text = "File name: " + FileUploader.PostedFile.FileName + "
" + FileUploader.PostedFile.ContentLength + " kb
" + "Content type: " + FileUploader.PostedFile.ContentType + "
Uploaded Successfully"; 
} 
catch (Exception ex)
{ 
Label1.Text = "ERROR: " + ex.Message.ToString(); 
} else 
{
Label1.Text = "You have not specified a file."; 
} 
} 
private void ReadRec() 
{
StreamReader Sr = new StreamReader(Server.MapPath("confirm/") + "params.txt"); System.Text.StringBuilder sb = new System.Text.StringBuilder(); SqlCommand cmd; string s=string.Empty; 
SqlConnection con = new SqlConnection("Data Source=localhost;User id=sa;password=root;database=throwsms");
// do your connection stuff here:
while(!Sr.EndOfStream)
{
s = Sr.ReadLine();
string s1 = "INSERT INTO tblcontact (contact_name,contact_no,client_id) VALUES('" + s.Split(',')[1] + "', '" + s.Split(',')[2] + "',3)"; 
con.Open();
cmd = new SqlCommand(s1, con);
int s2 = cmd.ExecuteNonQuery();
con.Close();
} 
Sr.Close();
} 
protected void btnsave_Click(object sender, EventArgs e) 
{
ReadRec(); 
}
} 
}