BULK INSERT Problem with Extended ASCII characters

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 2005 Forums

SSIS and Import/Export (2005)

BULK INSERT Problem with Extended ASCII characters

Author

Topic

santoshmshinde
Starting Member

7 Posts

Posted - 2009-08-10 : 09:21:22

Hi All,

I am facing an error while inserting data from text file to sqlserver 2005 using format file when i am trying with some Extended ASCII characters like (?,?,?,¦,-).for other it is working fine

only for these characters it is giving me an error.

i am using bulk insert for this,

eg.

CREATE TABLE #tmpImport
(
ImportID INT IDENTITY PRIMARY KEY CLUSTERED,
ISAID INT NULL,
Separator CHAR(1) NULL,
TextData nvarchar(max) NULL
)

bulk insert #tmpImport from 'c:\ABC_NoCRLF.20090822_split310.txt'
with(CODEPAGE='RAW',FORMATFILE='C:\Import8230.fmt',TABLOCK)

SELECT * FROM #tmpImport

The sample format file is

1 SQLCHAR 0 0 "" 0 ImportID
2 SQLCHAR 0 0 "" 0 ISAID
3 SQLCHAR 0 0 "" 0 Separator
4 SQLCHAR 0 1000 "¦" 4 TextData

when i am trying to execute this it is giving me an
error Msg 9422, Level 16, State 48, Line 13
XML parsing: line 2, character 0, incorrect document syntax

Any help greatly appreciate.

Thanks and Regard's
Santosh Shinde

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 13:18:39

BULK INSERT translates a text file into a resultset.

What you need to import complete file into TextData column, is OPENROWSET with BULK option.
See
http://weblogs.sqlteam.com/peterl/archive/2007/09/26/Insert-binary-data-like-images-into-SQL-Server-without-front-end.aspx

N 56°04'39.26"
E 12°55'05.63"

santoshmshinde
Starting Member

7 Posts

Posted - 2009-08-11 : 01:32:44

Hi All,

Just correcting previous post..

I am facing an error while inserting data from text file to sqlserver 2005 using format file. when i am trying with some Extended ASCII characters like (ALT+10,ALT+13,ALT+28,ALT+177,ALT+209). for other it is working fine

only for these characters it is giving me an error.

i am using bulk insert for this,

eg.

CREATE TABLE #tmpImport
(
ImportID INT IDENTITY PRIMARY KEY CLUSTERED,
ISAID INT NULL,
Separator CHAR(1) NULL,
TextData nvarchar(max) NULL
)

bulk insert #tmpImport from 'c:\ABC_NoCRLF.20090822_split310.txt'
with(CODEPAGE='RAW',FORMATFILE='C:\Import8230.fmt',TABLOCK)

SELECT * FROM #tmpImport

The sample format file is

1 SQLCHAR 0 0 "" 0 ImportID
2 SQLCHAR 0 0 "" 0 ISAID
3 SQLCHAR 0 0 "" 0 Separator
4 SQLCHAR 0 1000 "¦" 4 TextData

when i am trying to execute this it is giving me an
error Msg 9422, Level 16, State 48, Line 13
XML parsing: line 2, character 0, incorrect document syntax

Any help greatly appreciate.

Thanks and Regard's
Santosh Shinde

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-11 : 02:54:35

Try using 0x7C as delimeter. 0x7C is the same as ASCII 124, the pipe character.

N 56°04'39.26"
E 12°55'05.63"

santoshmshinde
Starting Member

7 Posts

Posted - 2009-08-11 : 06:35:47

I need to do bulk insert or BCP with this characters only...

santoshmshinde
Starting Member

7 Posts

Posted - 2009-08-12 : 01:06:41

I need to do bulk insert or BCP with Extended ASCII characters like (ALT+10,ALT+13,ALT+28,ALT+177,ALT+209) characters only..

santoshmshinde
Starting Member

7 Posts

Posted - 2009-08-17 : 05:36:57

Is anybody there who can help me?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-17 : 05:52:36

Can you post a couple lines of the data file?
Need to see how that relates to the format file.

santoshmshinde
Starting Member

7 Posts

Posted - 2009-08-19 : 08:51:35

Below mentioned is a data file

ABC|00| |00| |ZZ|LMNF LMNF002M|12|U86613023453550000|070810|1204|U|00305|023430351|0|P|>? LS|MN|086613|ZZIS|070810|1204|254695|X|0034450? ST|824|000473230? LKN|06|25853|070810|120401|LT|0? N1|15|SDFF? SDF|FDS|15|0001? SFD|ZZZ|NO ERRORS FOUND FOR SDFF SD? NTE|ZZZ|ALL SDFHHJ SDFSDF ? SDF|7|4564564? GE|1|045465? ABD|1|000000351?

I am saving this file and format file as UNCODE.

where ? ASCCI value is 63. this symbol might not be displayed after posting this message that's why i am giving this value. please mention this value in format file too..

Thanks
Santosh Shinde.

santoshmshinde
Starting Member

7 Posts

Posted - 2009-08-24 : 00:38:59

Is anybody there who is having any suggession.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-24 : 02:46:36

You are missing the first two lines in the format file.
See here
http://msdn.microsoft.com/en-us/library/aa173859(SQL.80).aspx

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources