SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 BCP Utility and BULKS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ocean
Starting Member

22 Posts

Posted - 05/02/2012 :  09:54:06  Show Profile  Reply with Quote
Hi there,

Im trying yo import some data .txt. The data that I have it comes without delimiters, so I need to use a Bulk in order to import my data, to my new table in the specify columns.

Data:

s 12 d3 Robert 121212 010062010 Paterson Ln43r UK (...)

The total is 15 columns.

I had create my table with 15 columns (dbo.RD)
The database called (ABC)
Path is C:\Data\Myfile.txt

And the code that im using is the following:


[ ABC.[dbo.RD_Construction]
from \\ C:\Data\WAST01.txt

[with(
[[,] codepage = {'raw'}]
[[,] datafiletype = {'native'}]
[[,] fieldterminator = ' ']
[[,] firstrow = 1]
[[,] formatfile = 'format_file_path']
[[,] keepidentity ]
[[,] keepnulls ]
[[,] maxerrors = 500 ]
[[,] rows_per_batch = >0]
[[,] rowterminator = 'row_terminator']
[[,] tablock ]
)]


Is not working

Any idea or any different way to import my data in SQL organizing it in 15 columns?

Thanks

vijays3
Constraint Violating Yak Guru

India
343 Posts

Posted - 05/02/2012 :  10:05:37  Show Profile  Reply with Quote
quote:
Originally posted by ocean

Hi there,

Im trying yo import some data .txt. The data that I have it comes without delimiters, so I need to use a Bulk in order to import my data, to my new table in the specify columns.

Data:

s 12 d3 Robert 121212 010062010 Paterson Ln43r UK (...)

The total is 15 columns.

I had create my table with 15 columns (dbo.RD)
The database called (ABC)
Path is C:\Data\Myfile.txt

And the code that im using is the following:


[ ABC.[dbo.RD_Construction]
from \\ C:\Data\WAST01.txt

[with(
[[,] codepage = {'raw'}]
[[,] datafiletype = {'native'}]
[[,] fieldterminator = ' ']
[[,] firstrow = 1]
[[,] formatfile = 'format_file_path']
[[,] keepidentity ]
[[,] keepnulls ]
[[,] maxerrors = 500 ]
[[,] rows_per_batch = >0]
[[,] rowterminator = 'row_terminator']
[[,] tablock ]
)]


Is not working

Any idea or any different way to import my data in SQL organizing it in 15 columns?

Thanks



Try this




Bulk Insert ABC.[dbo.RD_Construction]
from  "C:\Data\WAST01.txt"
with( datafiletype = 'char',
      fieldterminator = ' ' 
      rowterminator = '\n'  )
Go to Top of Page

ocean
Starting Member

22 Posts

Posted - 05/02/2012 :  10:25:27  Show Profile  Reply with Quote
Try this




Bulk Insert ABC.[dbo.RD_Construction]
from  "C:\Data\WAST01.txt"
with( datafiletype = 'char',
      fieldterminator = ' ' 
      rowterminator = '\n'  )

[/quote]
------------------------------------------------------------------
Thanks,

But I tried that one, but I have this issue all the time:

msg 208, level 16, state 82, line 3 "invalid object name'

I did a research and I discovered the limitation that you can't BULK INSERT to a four-part name (a remote server)...
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
343 Posts

Posted - 05/02/2012 :  11:15:17  Show Profile  Reply with Quote
quote:
Originally posted by ocean

Try this




Bulk Insert ABC.[dbo.RD_Construction]
from  "C:\Data\WAST01.txt"
with( datafiletype = 'char',
      fieldterminator = ' ' 
      rowterminator = '\n'  )



------------------------------------------------------------------
Thanks,

But I tried that one, but I have this issue all the time:

msg 208, level 16, state 82, line 3 "invalid object name'

I did a research and I discovered the limitation that you can't BULK INSERT to a four-part name (a remote server)...
[/quote]


Problem with brackets .Now try this.

Bulk Insert ABC.dbo.RD_Construction
from  "C:\Data\WAST01.txt"
with( datafiletype = 'char',
      fieldterminator = ' ' 
      rowterminator = '\n'  )
Go to Top of Page

ocean
Starting Member

22 Posts

Posted - 05/02/2012 :  11:35:28  Show Profile  Reply with Quote
Problem with brackets .Now try this.

Bulk Insert ABC.dbo.RD_Construction
from "C:\Data\WAST01.txt"
with( datafiletype = 'char',
fieldterminator = ' '
rowterminator = '\n' )
---------------------------------------------------------------------------

msg 4861, level 16, state 1, line 3
"the system cannot find the path specified" is the second time that happens to me using two different queries.

Im really lost here, thanks
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
343 Posts

Posted - 05/02/2012 :  11:52:56  Show Profile  Reply with Quote
quote:
Originally posted by ocean

Problem with brackets .Now try this.

Bulk Insert ABC.dbo.RD_Construction
from "C:\Data\WAST01.txt"
with( datafiletype = 'char',
fieldterminator = ' '
rowterminator = '\n' )
---------------------------------------------------------------------------

msg 4861, level 16, state 1, line 3
"the system cannot find the path specified" is the second time that happens to me using two different queries.

Im really lost here, thanks


Tell Me few question :
1 .where have you placed the file means what is the location of file
Is it remote server or local server.
2

Go to Top of Page

ocean
Starting Member

22 Posts

Posted - 05/02/2012 :  12:01:25  Show Profile  Reply with Quote
Remote server
Go to Top of Page

ocean
Starting Member

22 Posts

Posted - 05/02/2012 :  12:03:21  Show Profile  Reply with Quote
But I can use a local too...
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
343 Posts

Posted - 05/02/2012 :  12:14:01  Show Profile  Reply with Quote
quote:
Originally posted by ocean

But I can use a local too...


Try doing this on your Local marchin.

Create simple table on your local system . And import the file from your local system like from C:\data\yourFIle.txt.

So you will get to know how it works


Go to Top of Page

ocean
Starting Member

22 Posts

Posted - 05/03/2012 :  05:10:11  Show Profile  Reply with Quote
Hi,

So the matter is that it might take info from other 'columns' because in some cases the info is continuous (without a blank space between them)

In excel there is a tool, that helps you, to delimiter the info manually, so you go to your info

201010acvb1201 and manually you split 201010/acvb/1201 for all the file, in my case I only have to split the whole info by the number of columns that I need (15)

There is any tool or query in SQL like that? Is the same as the column delimiter (options available are: comma, semicolon, colon, tab and vertical bar) from the SQL server import wizard, but using a manual tool to delimiter columns, as the excel tool

I cant use excel in this case, because I excess the capacity of excel...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000