I am trying to import data from the text file using BULK INSERT command.
BULK INSERT test.s1_test
FROM 'D:s1.txt'
WITH
(
CODEPAGE = 1250,
FIELDTERMINATOR = '|',
FIRSTROW = 2,
ROWTERMINATOR = '|
'
)
In the text file in the end of each row is symbol which looks like a square - you can see it here
If I use rowterminator '|
' of '|
', 0 row(s) affected.
Please, can you help me?
We have created CSV files on HPUX 11.0 and transferred them via ASCII ftpto our SQL Server machine file store to load large amounts for data usingthe BULK INSERT command. This is the command:BULK INSERT db..table FROM 'S:pathfilename.csv'WITH (DATAFILETYPE = 'char',FIELDTERMINATOR = ',',ROWTERMINATOR = '')Now these files are written on Linux and there seems to be a linefeedproblem when loadingthe data. This is the error:/Server: Msg 4866, Level 17, State 66, Line 1Bulk Insert fails. Column is too long in the data file for row 1, column31. Make sure the field terminator and row terminator are specifiedcorrectly./Column 31 is our last column in the CSV file.I've tried '
', '
' for ROWTERMINATOR and cannot get it execute pastthe 1st line.If one opens the CSV file in Wordpad and saves it, then executes theabove statement,the ROWTERMINATOR = '' suffices but this is an unreasonable solution.Any help on this is greatly appreciated.Michael Husler
I need to import a CSV file to a table and the CSV has an Address field that has a carriage return in it.
Example:
123 Main St.
Anywhere, CO, 99999
I'm working in Windows with SQL Server 2008. What can I do to the CSV file or from within SQL Managment Studio to get the BULK INSERT to work?
Here's my query:
BULK INSERT Contact
From 'C:UsersBrianDownloadsImport-FilteredContact9c.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '
'
)