Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to import .csv file to a table?

Re: How to import .csv file to a table?

From: Chuck Conover <cconover_at_commspeed.net>
Date: Wed, 10 Mar 2004 08:12:54 -0700
Message-ID: <1078932231.489503@news.commspeed.net>


Gexin,

    A DTS package is a great way to do this. It's also really fast to create when you know how. Also, you should import your data into a non-production "working" table first, then run a stored procedure to loop through your data, check it and insert/update to your production table. This way, you protect your production data, and it usually takes a few tries to get the settings right to import your data as expected. You don't want to mess with a production table while doing that.

    You can have an expert show you how to create a DTS package with our SQL Server video set at www.TechnicalVideos.net. There are 3 videos on DTS that show you all the steps, with hints and tricks on how to do it the best way. Hope this helps.
Best Regards,
Chuck Conover
www.TechnicalVideos.net

"gexin" <gexin.chen_at_ncr.com> wrote in message news:404f01bc_at_rpc1284.daytonoh.ncr.com...
> I have experience to import below kinds of text file into a table, but how
> can I import .csv file in table?
>
> Thanks
>
>
> '
> '
> 'Purchase Line
> Scheduled Received DELJIT Ship Supplier
> ' Order Number Supplier Buyer Part Number Quantity Quantity
Number
> Open Code Seq Num Dock Date
>

'-------- ------ -------- ----- ---------------- --------- -------- --------

> ---- ---- -------- ----------
> ' 107422 1 28 5 4450610237 50 0
00010503
> Yes 0 08/11/2002
>

'123456789012345678901234567890123456789012345678901234567890123456789012345
> 67890123456789012345678901234567890
> ' 1 2 3 4 5 6 7
> 8 9 10
>
> Open "c:\ao.txt" For Input As f1
>
> CurrentDb.Execute ("delete * from AO")
> Set rst = CurrentDb.OpenRecordset("select * from AO")
> CurrentDb.Execute ("delete * from overdue")
> Set rst1 = CurrentDb.OpenRecordset("select * from overdue")
> Do Until EOF(f1)
> Line Input #f1, textline
> If Mid(textline, 51, 26) = "Scheduled Receipts Summary" Then
> sdate = CDate(Left(textline, 2) & "/" & Mid(textline, 4, 2) & "/" &
> Mid(textline, 7, 4))
> Else
> End If
>
>
> If Trim(Mid(textline, 32, 10)) <> "" And Left(textline, 4) <> "----" And
> Len(Trim(textline)) <> 0 And Left(textline, 8) <> "Purchase" And
> Mid(textline, 4, 5) <> "Order" Then
>
>
> rst.AddNew
> rst("report_date") = sdate
> rst("purchase order") = Val(Left(textline, 8))
> rst("line number") = Val(Mid(textline, 10, 6))
> rst("supplier") = Val(Mid(textline, 17, 8))
> rst("buyer") = Val(Mid(textline, 26, 5))
> rst("part number") = Mid(textline, 32, 15)
>
> rst("scheduled qty") = Val(Mid(textline, 50, 8))
>
> rst("received qty") = Val(Mid(textline, 59, 8))
>
> rst("deljit number") = Val(Mid(textline, 68, 8))
>
> rst("open") = Mid(textline, 77, 3)
>
> rst("ship code") = Mid(textline, 82, 2)
>
> rst("supplier seq num") = Mid(textline, 87, 8)
>
>
> If Len(Trim(Mid(textline, 96, 10))) <> 0 Then
>
> rst("dock date") = CDate(Mid(textline, 96, 2) & "/" &
Mid(textline,
> 99, 2) & "/" & Mid(textline, 102, 4))
> dt = rst("dock date")
>
> rst("week") = Val(Format((Mid(textline, 96, 2) & "/" &
Mid(textline,
> 99, 2) & "/" & Mid(textline, 102, 4)), "ww"))
>
>
> Else
> End If
>
> ' If Val(Mid(textline, 26, 5)) = 8 Or Val(Mid(textline, 26, 5)) = 58
Or
> Val(Mid(textline, 26, 5)) = 68 Or Val(Mid(textline, 26, 5)) = 38 Then
> rst.Update
> ' Else
> ' End If
>
>
Received on Wed Mar 10 2004 - 09:12:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US