Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jai2808
Starting Member
27 Posts |
 Posted - 2009-06-15 : 08:30:06
|
| Hi,We get data in csv file which will have data around 15 lacs(150000).this data we need to import it into Sql Server.We tried using DTSWIzard, but the problem is there are complete blank rows inbetween the data eg.,1,1521,xxxx2,5154,yyyyDue to this DTSWizard is not uploading data.So we want to try with Openrowset from query analyser.Can we do it using openrowset my csv file doesnot have header.I plan to use by import all into temp table and give where condition.Can some one help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-15 : 14:35:33
|
| does that mean delimiter is inconsistent? |
 |
|
|
jai2808
Starting Member
27 Posts |
Posted - 2009-06-16 : 07:50:47
|
| Demiliter is not incosistent, but only there are blank rows |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-16 : 08:33:32
|
| is this a one off or are you going to need to do this automated?If it's a one off then you can just remove the blank lines from the file.If you need to do this repeatedly but can't pre process the file you could=== Staging table =========1) upload the file to a staging table -- in this case uploaded each line as 1 column of data NVARCHAR(MAX) with a row delimiter of (newline) -- in windows encoding that is CHAR(13)CHAR(10)2) Make a sp to get this data to where you need it. You would implement a string splitting procedure or function to return a nice table from this data.=== Pre Processing ========Pre process the file first before uploading it. A tool like awk would probably be your friend here.http://www.sap-basis-abap.com/unix/deleting-blank-lines-using-awk.htmNB: Tools like GAWK are readily available for windows and are open source and completely free.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
Import Data