BCP simple text file help - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

BCP simple text file help

Author

Topic

DeanaMiller
Starting Member

12 Posts

Posted - 2006-09-05 : 12:50:18

I have used BCP in the past with success but need to know how I can import a new line in the text file to a new column into SQL.
Text file contains only this data.

805 Park Ave
Denver
Colorado
80621

And the SQL table 'Homes' contain these columns:

Address City State Zip

I keep getting EOF error. Have tried importing using an Excel file of the same data and still get EOF error. Any ideas would be appreciated.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 12:56:20

Try to add an extra empty line at the end before importing.

Peter Larsson
Helsingborg, Sweden

DeanaMiller
Starting Member

12 Posts

Posted - 2006-09-05 : 14:29:37

I did try adding an emply line in both the .txt file and the .xls file and still same error.

Thanks

kevindockerty
Starting Member

27 Posts

Posted - 2006-09-05 : 16:39:33

try splitting the file in half with an editor - and load the first part - what happens ?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-09-05 : 17:13:29

Does your text file data look like this (4 lines):

805 Park Ave
Denver
Colorado
80621

or like this (1 line, with each "<tab>" is a tabulation between data elements, not the actual text "<tab>"):

805 Park Ave<tab>Denver<tab>Colorado<tab>80621

Your data should look like the second example, not the first (assuming you have designated the tab to be the column delimiter). Sometimes you can get an EOF error, but still get the data imported anyway. What is the exact syntax you are using for your bcp?

DeanaMiller
Starting Member

12 Posts

Posted - 2006-09-05 : 18:23:12

The first set of data is correct.
The data is output from a batch file so adding a comma or tab deliter is unavailable since I cannot modify the batch file. I've tried so many commands with different syntax but this is basically it. I have a query I run to drop and create table with values: Address NVARCHAR(65) NULL, and so on....

BCP in Database..homes C:\119-1.txt /r \n -Usa
BCP in Database..homes C:\119-1.txt -c -Usa
BCP in Database..homes C:\119-1.txt -r -Usa
BCP in Database..homes C:\119-1.txt -Usa

I use BCP with other projects but that particular data is tab delimited where this seems to be new line or carriage return delimited I believe.

Thanks

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-09-05 : 19:33:41

If your data is "delimited" by carriage returns and is exactly as your indicated in your first post, then you won't be able to BCP the data verbatim into your table. BCP works by simply looking at column and row delimiters. With the data as you have it, the BCP program (and also the BULK INSERT command) will see the data as a single column, 4 row set of data. You could theoretically create a one-column staging table, BCP the data into this staging table and then pivot the data into your 4-column table. Problem is that there is no guarantee that the BCP program will insert the data in the exact same order as you have it in the text file. Most likely it will for a small amount of data, but for larger sets it tends to insert chunks of data wherever it feels like.

Therefore, in these kinds of situations, here's what I would do. If it's possible for you to do so, add line numbers to your text file, like this (from the command line):

findstr /N [a-z0-9] c:\119-1.txt > c:\119-1_NEW.txt

Then you create a staging table like this one:

CREATE TABLE tmp_addresses (
line_no int NULL,
address_part varchar(100) NULL)

Then BCP your data into this staging table:

bcp database..tmp_addresses in c:\119-1_NEW.txt -c -Usa -Ppassword -Sserver

If this is done right, then the records with line numbers 1, 5, 9, 13, etc. are the street addresses. Line numbers 2, 6, 10, 14, etc. are the cities, and so on.

DeanaMiller
Starting Member

12 Posts

Posted - 2006-09-06 : 10:53:13

Well I created the staging table and was able to import but after about row 50 or so the data is very innacurate. Although I thought it would work too.

Thanks

DeanaMiller
Starting Member

12 Posts

Posted - 2006-09-07 : 09:23:09

When I create any table with a line_no or identifier BCP tries to import into that column first then fails. Is there any way to tell BCP to start import at column 2 or such? But even then I'm still trying to find a way to insert NEW LINE into NEW COLUMN via BCP, or any way for that matter.

-Deana

marat
Yak Posting Veteran

85 Posts

Posted - 2006-09-07 : 10:36:09

Not sure that this approach with staging table will work.
Imagine if there is a missing address element, say State.
In that case all address elements in a table will be shifted and it could happen multiple times within a dataset.

DeanaMiller
Starting Member

12 Posts

Posted - 2006-09-07 : 12:20:58

Good point marat. I am in the process of changing 56 batch files to include the exact count of 11 lines. If the batch returns less than 11 lines it will fail and will not be imported. But when I do get all 56 updated I am still stuck with "How in the heck do I import the data into a multi column SQL table". I have kicked around the idea of using COALESCE but even then that would be a huge query but may be my only option. I am open to Oracle, MSSQL, Sybase, MySQL, the dreaded Access if any of those would work.

-Deana

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2006-09-07 : 12:35:53

gawk is your friend.
Convert the multi line files in to | delimited records in one file

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

marat
Yak Posting Veteran

85 Posts

Posted - 2006-09-08 : 03:08:37

Try to import into a single column and then:
--generating sample table
select * into #t
from
(select
1 as id, '2/3 Oz Av' as address
union all
select 2, 'Oz East'
union all
select 3, 'VIC'
union all
select 4, '3333'
union all
select 5, 'AUS'
union all
select 6, '1/3 Oz Av'
union all
select 7, 'Oz East'
union all
select 8, 'VIC'
union all
select 9, '3333'
union all
select 10, 'AUS'

) t
select * from #t
--extract components
select t1.address as Street, t2.address as Suburb,
t3.address as State, t4.address as Postcode,
t5.address as Country
from #t t1
left join #t t2 on t2.id = t1.id + 1
left join #t t3 on t3.id = t1.id + 2
left join #t t4 on t4.id = t1.id + 3
left join #t t5 on t5.id = t1.id + 4
where t1.id%5 = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources