July 31, 2014 at 3:30 am
Hi
Using a format file to Bulk insert into a table from a CSV file.
It is doing something funny to my int values. It seems to insert the ASCII value i.e. 1 => 49
CREATE TABLE [dbo].[tBCP](
[f1] [int] NULL,
[f2] [varchar](10) NULL
)
The format file, fmt.fmt is
10.0
2
1 SQLINT 0 4 ",\"" 1 f1 ""
2 SQLCHAR 0 10 "\"\r" 2 f2 Latin1_General_CI_AS
The CSV file tbcp.csv is
1,"one"
2,"two"
BULK INSERT tBCP FROM 'C:\temp\tbcp.csv'
WITH (FORMATFILE='C:\temp\fmt.fmt')
The data I get is
49one
50two
IF I change the format file from SQLINT --> SQLChAR it works. The real world data I'm using has bigger int values and I it turns them into even larger numbers, leading to overflow.
What should I be doing here?
July 31, 2014 at 4:02 am
Quick question, does 7 get translated to 55? Looks like it is reading the character code values as the actual values, change it to SQLCHAR and then convert to int.
😎
July 31, 2014 at 4:31 am
Yes 7 does get converted to 55 its ASCII value.
If I try and import 21 it gets converted to 12594 (2=ASCII 50 and 1=ASCII 49) so how it gets to 12594?
If I change the format file so instead of SQLINT I use SQLCHAR it imports fine i.e. 7 gets imported as 7.
However I guess this might be working by luck, implicit datatype conversions going on.
Should I always use SQLCHAR, what about when I want to do dates?
July 31, 2014 at 4:34 am
BTW the field terminator for the 2nd column is "\"\r" when I post it removes the
July 31, 2014 at 4:35 am
again it has removed the backslash n
July 31, 2014 at 4:55 am
You might want to add -c (character format) as it looks like it is using native (-n) format.
The 21 -> 50 49 -> 12594 is because it translates the character value of 2(50) to the upper byte of an two byte int and 1(49) to the lower byte. This translates to binary 00110001 00110010 or 0x3132 or decimal 12594 (remember Intel uses small endian byte order)
😎
July 31, 2014 at 5:15 am
Thanks
Where do I put the -c I tried
BULK INSERT tBCP FROM 'C:\temp\tbcp.csv'
WITH (FORMATFILE='C:\temp\fmt.fmt', DATAFILETYPE='char')
It doesn't work
July 31, 2014 at 5:29 am
terry999 (7/31/2014)
ThanksWhere do I put the -c I tried
BULK INSERT tBCP FROM 'C:\temp\tbcp.csv'
WITH (FORMATFILE='C:\temp\fmt.fmt', DATAFILETYPE='char')
It doesn't work
Ooops, my bad, I was thinking in bcp:-P
Should be DATAFILETYPE = 'char'
😎
July 31, 2014 at 5:37 am
No prob thanks for helping.
I got it to work with SQLCHAR in format file, Just worried I've got it to work by chance and storing up potential problems.
BTW. (Loved the little endian part)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply