We help IT Professionals succeed at work.

Importing a pipe delimited text file into  table using sql not wizard

Fordraiders
Fordraiders asked
on
12 Comments
1 Solution
66 Views
Last Modified: 2018-12-29
Sql server 2008r2

Can anyone point me to a good example of
importing(insert) a pipe delimited text file in to a table.

Not using the import wizard please.
and i cant use Bulk insert
approx 12 million records

Thanks
fordraiderds
Comment
Watch Question

View Solution Only

D BDatabase Developer

Commented:
Why not the wizard or bulk insert. They certainly can handle 12M rows. You can use SSIS, which is basically what the wizard does (it creates an SSIS package). I suppose the only other option is using bcp.
Fordraiders

Author

Commented:
because i do not have rights to use Bulk insert..
D BDatabase Developer

Commented:
Can you use bcp? It is a command line utility for importing data.
Fordraiders

Author

Commented:
No, i need just to use a INSERT text file from  a location on c: drive.
D BDatabase Developer

Commented:
We'll if you can't it's bcp, bulk insert, the wizard (which means you can't use ssis), is say your only option is to make the file available to a DBA who has access and have them in the file for you.
If they have your access that locked down there isn't anything you can really do.
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
You would need external tools (Database Browser), for example) or scripts (e.g. PowerShell code) to go without. Both are implementing single row INSERT statements, and hence are slower than the bulk tools.
D BDatabase Developer

Commented:
My thoughts are, if they don't have access to bcp, bulk insert, the import wizard, they are probably locked out of being able to access the server/database through PowerShell or other 3rd party tools.
I've worked as a database developer in shops where I've needed to get DBAs involved to do one-time imports into a production environment. Most production ETL environments run under a service account that has access to tools to do the import and export of files.
Fordraiders

Author

Commented:
bulk insert [dbo].[CRS_Header_Detail_Current_Staging_024]
from 'C:\Users\Public\Documents\Data dump Performance Report 10DEC2018.txt'
with (fieldterminator = '|', rowterminator = '\n',KEEPNULLS)
go

got bulk insert rights..
but now error:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (PROJECT_SUBMIT_DATE).
Database Developer
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
D BDatabase Developer

Commented:
Also, it appears that the row terminator is actually CR/LF which should be \r\n instead of just \n. It will work with just the \n but the carriage return would be imported in as part of UPDATEDBY, so instead of br1tim, it would actually come in as br1tm\r.
Fordraiders

Author

Commented:
thanks doug/all  very much!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions