August 18, 2009 at 2:11 am
Help Please! I need to pull in a fixed length text file into a table in SQL Server 2008. I can pull in delimited files with ease but I get multiple errors trying to pull in a fixed length file. The trick is that I need to pull the text file via MSAccess by using a statement. I have a MSAccess database which generates reports based on the data in the MySQL database and I need to now move this data to the SQL Server database.
In MySQL I used the following statement and it works like a charm:
(These filenames are just examples)
load data infile 'C:/155170Inp.txt' into table batch.input
fields terminated by '' enclosed by '' escaped by ''
lines terminated by '\r';
But in SQL Server, it does not want to work…
In SQL Server, to load a delimited file I used:
(These filenames are just examples)
BULK INSERT dbo.tblpersonal
FROM 'c:\172851Inp.txt'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '')
Can anyone please assist me with this issue? I cannot get it to work with a FIXED length text file...
August 18, 2009 at 2:46 am
I don't think you can have a ROWTERMINATOR = '', shouldn't it be ''? Or, maybe ROWTERMINATOR = '''+CHAR(10)+'''? BOL, has a pretty comprehensive page of info on BULK INSERT.
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply