Jul 20, 2005
Hi There,I am looking for information on how to import the txt or csv file tothe multiple table in sql 2000. If you have any kind of inf. pleaselet me know wheather we can do this and how.below is the detail information.I received txt file every day which contain the information from 3different related table in my sql 2000 database. Right now we arekeyin the information from the web site (which is link to the txtfile) to our database, but i am wondering if we can import thoserecord in the tables.the header of the file goes to table1 and when we insert the record intable1, it should generate the autoidentityrecord (PK), and that PK islink to other table2 and table3 where rest of the information from txtfile goes. For table2 and table3 there are multiple record per txtfiles.in our txt file each row is separated with row header, like HTC100WITH ROW NO. 1,2,3.., which indecate this information goes to tableand 1,2....are the different row number.Please let me know whether we can achive this task or not.Thanks for all you help in advance.Indra.I have pasted my txt file below:========"FHS000",20041029,0900,,"10",1,"TRAILB10_20041029_1B",3,"2.20","Pason-DataHub",,"#Well 1098831406 Tour 2004/10/29 Trailblazer 10 148",1,"EDR_3-0-10_HF2ETS 2.2""CON000",1,0000,0759"CON000",2,0800,1559"CON000",3,1600,2359"HWI010","0312857","COMPTON BRANT 15-7-18-24","COMPTON PETROLEUMCORP.","TRAILBLAZER DRILLINGCORP.","15-07-018-24W4","100/15-07-018-24W4/00","HANKPARANYCH","CURTIS FIESEL",20041029,,,"10",20041027,0600,,,"148","DD04485","VERT.","NO",,"HCO030",1,"Daily Walk Around Inspection","HP","CF""HCO030",2,"Detailed Inspection - Weekly (using checklist)","HP","CF""HCO030",3,"H2S Signs Posted (if required)",,"HCO030",4,"Well License & Stick Diagram Posted","HP","CF""HCO030",5,"Flare Lines Staked","HP","CF""HCO030",6,"BOP Drills Performed","HP","CF""HCO030",7,"Visually Inspect BOP's - Flarelines and DegasserLines","HP","CF""HDC040",1,"Rig Site Health and Safety Meeting (one/crew/month)","CF""HDC040",2,"C.A.O.D.C. Rig Safety Inspection Checklist(one/rig/month)","CF""HDC040",3,"Mast Inspection Before Raising or Lowering","CF""HDC040",4,"Crown Saver Checked","CF""HDC040",5,"Motor Kills Checked","CF""HFU050",2300,2100,,"HWE060",-5,"Deg C","COOL","WEST","SLIPPERY",,"HCS070",1,177.8,,"mm",25.3,"STELCO","J-55",8,108.44,3.84,108.44,"HCS070",2,114.3,,"mm",14.14,"STELCO","J-55",72,979.50,3.84,979.0,"HDP080",1,127,79.4,"kg/m","E",57,127,"mm","3 1/2 IF",10,"DC","HDP080",2,89,19.7,"kg/m","E",68,120,"mm","3 1/2 IF",15,"DP","HPU090",1,"F-800","EMSCO",254,"mm",,,,"HPU090",2,"F-800","EMSCO",254,"mm",,,,"HTC100",1,"Rig up and tear down""HTC100",2,"Drill Actual""HTC100",3,"Reaming""HTC100",4,"Coring""HTC100",5,"Condition Mud & Circulate""HTC100",6,"Trips""HTC100",7,"Rig Service""HTC100",8,"Repair Rig""HTC100",9,"Cut off drilling line""HTC100",10,"Deviation Survey""HTC100",11,"Wire Line Logs""HTC100",12,"Run Case & Cement""HTC100",13,"Wait on Cement""HTC100",14,"Nipple up B.O.P.""HTC100",15,"Test B.O.P.""HTC100",16,"Drill Stem Test""HTC100",17,"Plug Back""HTC100",18,"Squeeze Cement""HTC100",19,"Fishing""HTC100",20,"Directional Work""HTC100",21,"Safety Meeting""HTC100",24,"WOD""HSS110",1,1,"SWACO","N","110",,"84",,"HPA130","COMPTON BRANT 15-7-18-24",20041029,"COMPTON PETROLEUMCORP.","TRAILBLAZER DRILLING CORP.","CURTISFIESEL","10","ALBERTA","N",253"TCP130",1,,,,"kPa",140,,,,"mm",,"TCP130",2,,,,"kPa",140,,,,"mm",,"TCP130",3,,,,"kPa",140,,,,"mm",,"TTL160",1,1,0.00,0.25,0.25,21,"SAFETY MEETING WITH TONG HAND""TTL160",1,2,0.25,1.75,1.50,12,"RIG TO AND RUN CASING""TTL160",1,3,1.75,2.00,0.25,7,"RIG SERVICE""TTL160",1,4,2.00,2.50,0.50,5,"CONDITION MUD & CIRC.""TTL160",1,5,2.50,2.75,0.25,21,"SAFETY MEETING WITH CEMENTERS""TTL160",1,6,2.75,3.50,0.75,12,"RIG TO AND CEMENT CASING""TTL160",1,7,3.50,6.00,2.50,1,"SET SLIPS, TEAR OUT RIG, CLEAN TANKS""TTL160",1,8,6.00,8.00,2.00,24,"WAIT ON DAYLIGHT/TRUCKS""TTL160",1,9,,,,,"CEMENT WITH BJ USING 13 TONNES OF BVF-1500 NP + .7%FL-5,GIVING 15.5 m3 OF GOOD""TTL160",1,10,,,,,"SLURRY @ 1718 kg/m3,PLUG BUMPED & HELD @ 03:30 HRSOCT 29/04.""TTL160",1,11,,,,,"RIG RELEASED @ 08:00 HRS OCT 29/04""TTL160",1,12,,,,,"MOVE TO 12-3-18-25W4""TDI170",1,"JEFF CASE",8,10,475,"Deg C",,,"RUNNING CASING",,,,,"TLN175",1,"VISUALLY INSPECT PINS, RAMS AND STOOLS PRIOR TO LAYINGOVER DERRICK""TPA180",1,1,"DRILLER",647172865,"JEFF CASE",8,,,"JC""TPA180",1,2,"DERRICK HAND",648519056,"BRYAN VANHAM",8,,,"BV""TPA180",1,3,"MOTOR HAND",651056533,"NEIL WILLIAMS",8,,,"NW""TPA180",1,4,"FLOOR HAND",640352662,"TARAS WOITAS",8,,,"TW""TPI190",1,"REG",25,,,,,,"TPI190",2,"REG",25,,,,,,"TPI190",3,"REG",25,,,,,,=====
View 4 Replies
View Related
Feb 7, 2008
Hi,
In my project i have to import csv file into DB, i have written a C# windows application as below...
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;namespace FedExDataAnalysis
{
public partial class Form1 : Form
{public Form1()
{
InitializeComponent();
}protected string[] arrayValues;
//string row = "";
// int cnt = 0;private void Form1_Load(object sender, EventArgs e)
{string path = "c:\2008-01-09_01-00_FedExInv_232316837.csv";
importCSV(path);string path1 = "c:\2008-01-10_05-18_FedExInv_232316837.csv";
importCSV(path1);
}public void importCSV(string path)
{string row = "";int cnt = 0;
using (StreamReader sr = new StreamReader(path))
{while (sr.Peek() >= 0)
{
cnt = cnt + 1;
row = sr.ReadLine();arrayValues = row.Split(',');if (cnt > 2)
{if (arrayValues.Length > 0)
{
insertrecord();
}
}
}
}
}public void insertrecord()
{string str;
int Recordaffected;string inserted = "no";
string constr;SqlConnection conn;
SqlCommand cmd;
constr = "Data Source=I07;Initial Catalog=JuiceApp;User ID=sa;Password=dj$ql1736ju1c30ca1a";conn = new SqlConnection(constr);
conn.Open();
try
{str = "insert into xt_importcsv (Bill_to_accountno,Invoice_date,invoice_number,Original_ammountDue,Current_Balance,express_or_ground_trackingid,";
str = str + "transportation_charge_ammount,net_charge_ammount,shipment_date,actual_weight_amount,rated_weight_ammount,recipient_name,recipient_company,";str = str + "recipientaddress_line1,recipient_city,recipient_state,recipient_zipcode,recipient_country,shipper_name,trackingid_charge_description,trackingid_charge_ammount,";
str = str + "trackingid_charge_description1,trackingid_charge_amount1,trackingid_charge_description2,trackingid_charge_ammount2,trackingid_charge_description3,trackingid_charge_ammount3,";str = str + "trackingid_charge_description4,trackingid_charge_ammount4) values ('" + arrayValues[0].ToString().Replace(""", "") + "','" + arrayValues[1].ToString().Replace(""", "") + "','" + arrayValues[2].ToString().Replace(""", "") + "','" + arrayValues[4].ToString().Replace(""", "") + "','" + arrayValues[5].ToString().Replace(""", "") + "','" + arrayValues[8].ToString().Replace(""", "") + "','" + arrayValues[9].ToString().Replace(""", "") + "','" + arrayValues[10].ToString().Replace(""", "") + "','" + arrayValues[13].ToString().Replace(""", "") + "','" + arrayValues[18].ToString().Replace(""", "") + "','" + arrayValues[20].ToString().Replace(""", "") + "','" + arrayValues[25].ToString().Replace(""", "") + "','" + arrayValues[26].ToString().Replace(""", "") + "','" + arrayValues[27].ToString().Replace(""", "") + "','" + arrayValues[29].ToString().Replace(""", "") + "','" + arrayValues[30].ToString().Replace(""", "") + "','" + arrayValues[31].ToString().Replace(""", "") + "','" + arrayValues[32].ToString().Replace(""", "") + "','" + arrayValues[34].ToString().Replace(""", "") + "','" + arrayValues[80].ToString().Replace(""", "") + "','" + arrayValues[81].ToString().Replace(""", "") + "','" + arrayValues[82].ToString().Replace(""", "") + "','" + arrayValues[83].ToString().Replace(""", "") + "','" + arrayValues[84].ToString().Replace(""", "") + "','" + arrayValues[85].ToString().Replace(""", "") + "','" + arrayValues[86].ToString().Replace(""", "") + "','" + arrayValues[87].ToString().Replace(""", "") + "','" + arrayValues[88].ToString().Replace(""", "") + "','" + arrayValues[89].ToString().Replace(""", "") + "')";cmd = new SqlCommand(str, conn);
Recordaffected = cmd.ExecuteNonQuery();if (Recordaffected == 1)
{inserted = "yes";
}
}catch (System.Exception ex)
{Console.WriteLine(ex);
}
finally
{
conn.Close();
}
}
}
}
It is working fine.. It is importing data into DB. But the problem what m facing is , in the code above m using the split function and assigning the row of the CSV file into arrayValue. the prob is if the arrayValue has two entries lk: Name: "john,Mikel " it will split it separately and insert into the next field in the DB... M not getting how resolve this... Please help...
Thanks,
Chai.NV.
View 3 Replies
View Related
