How to import text file(no space, no symbol, no column) into mssql
the text file format use the length of character to define the field
for example,
0001130130HAUT BAGES AVEROUS 03
9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field
one record by one record store in db
no space, no symbol, no line break between each record
-I try bcp method, but some error happens. Please give me suggestions. thx
I run the following code in query analyzer.
BULK INSERT Chain.dbo.POLL59
FROM 'D:\POLL59.DWN'
WITH (FORMATFILE = 'D:\bcp.fmt')
?
it shows the error
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 4 in format file 'D:\bcp.fmt'.
***
For your information
data file
000000011301220051222000192000000000011301320051222000030000000000019067420051222000000001<there are many space >
bcp.fmt file <I use tab to separate and use ascii>
8.0
4
1 SQLCHAR 0 4 "" 1 PLUEVT ""
2 SQLCHAR 0 9 "" 2 PLUSKU ""
3 SQLCHAR 0 8 "" 3 PLUFRD ""
4 SQLCHAR 0 9 "" 4 PLUPRC ""
!!!!
I try to edit collation name in Chinese_Taiwan_Stroke_CI_AS or others, but the error also happens.
[1392 byte] By [
nkh121] at [2007-12-18]
I am getting the 'Cannot perform bulk insert. Invalid collation name for source column 1 in format file '\\192.168.241.10\ISBN 13 Setup\share\test.fmt'.
' error.
I also tried to insert CR at last line of format file then I am getting following error.
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.
Can you guide me how to solve this.
>no space, no symbol, no line break between each record
Seems like this is the problem? The rows show up like one giant single row?
If this is the case, it can happen for some various reasons. (I'll just assume that there is a rowterminator in there, if not, it won't work regardless) Anyway, windows want ascii files with \r\n as rowterminators, but sometimes files only have \r or \n - you then need to say which in the formatfile. Files that does not have a 'proper' EOL marker (\r\n) usually shows up like a single row a mile wide in notepad. (but it *is* there, only you can't see it)
/Kenneth
