October 7, 2009 at 3:26 pm
I am importing a .fmt for a bulk insert, but, it is causing the data to be incorrect. here is the .fmt file:
8.0
12
1SQLCHAR0255"\t"1Allied ""
2 SQLCHAR 0 255 "\t" 2 Mfr ""
3 SQLCHAR 0 255 "\t" 3 Mfr ""
4 SQLCHAR 0 255 "\t" 4 Product ""
5 SQLINT 0 4 "\t" 5 Price ""
6 SQLCHAR 0 255 "\t" 6 UOM ""
7 SQLINT 0 4 "\t" 7 Lead ""
8 SQLINT 0 4 "\t" 8 Sells ""
9 SQLINT 0 4 "\t" 9 Available ""
10 SQLINT 0 4 "\t" 10 Min ""
11 SQLINT 0 4 "\t" 11 Catalog ""
12 SQLCHAR 0 255 "\r" 12 URL ""
this is coming from a tab deliminated text file. I read somewhere that everything should be SQLCHAR for text files; however, when I change them, then I get an invalid
When I run it as SQLINT, I get no erros; however, I get bad data. Example:
What should be data of 1, results on the import of 153104672.
Any ideas?
October 7, 2009 at 3:44 pm
dzirkelb (10/7/2009)
I am importing a .fmt for a bulk insert, but, it is causing the data to be incorrect. here is the .fmt file:8.0
12
1SQLCHAR0255"\t"1Allied ""
2 SQLCHAR 0 255 "\t" 2 Mfr ""
3 SQLCHAR 0 255 "\t" 3 Mfr ""
4 SQLCHAR 0 255 "\t" 4 Product ""
5 SQLINT 0 4 "\t" 5 Price ""
6 SQLCHAR 0 255 "\t" 6 UOM ""
7 SQLINT 0 4 "\t" 7 Lead ""
8 SQLINT 0 4 "\t" 8 Sells ""
9 SQLINT 0 4 "\t" 9 Available ""
10 SQLINT 0 4 "\t" 10 Min ""
11 SQLINT 0 4 "\t" 11 Catalog ""
12 SQLCHAR 0 255 "\r" 12 URL ""
this is coming from a tab deliminated text file. I read somewhere that everything should be SQLCHAR for text files; however, when I change them, then I get an invalid
When I run it as SQLINT, I get no erros; however, I get bad data. Example:
What should be data of 1, results on the import of 153104672.
Any ideas?
Since your not skipping columns and all of the delimiters are TAB (ie, the same), you don't need a format file. Just do the BULK INSERT using the defaults.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2009 at 7:14 am
Actually, I am skipping a column that is in the table, but not listed on the .fmt file.
Here are the field types:
1: varchar 255
2: varchar 255
3: varchar 255
4: varchar 255
5: float 8
6: varchar 255
7: int 4
8: int 4
9: int 4
10: int 4
11: varchar 255
12: int 4
October 20, 2009 at 3:31 pm
dzirkelb, your format file is set up to insert column 1 of the source file into column 1 of the destination table, column 2 of the source file into column 2 of the destination table, etc. If you are skipping a column in the destination table, then someone needs to tell your format file. Below is a snippet of a format file where I am skipping column 71 of the destination table. I hope this helps.
67 SQLCHAR 0 250 "\t" 67 VALID_ADDR_CT SQL_Latin1_General_CP1_CI_AS
68 SQLCHAR 0 250 "\t" 68 MDU_VALID_ADDR_CT SQL_Latin1_General_CP1_CI_AS
69 SQLCHAR 0 250 "\t" 69 CXM_RDLN_CMP SQL_Latin1_General_CP1_CI_AS
70 SQLCHAR 0 250 "\t" 70 VAL_DTSQL_Latin1_General_CP1_CI_AS
71 SQLCHAR 0 250 "\t" 72 RTOS_COORDINATOR SQL_Latin1_General_CP1_CI_AS
72 SQLCHAR 0 250 "\t" 73 MDU_VALID_ADDR_CT SQL_Latin1_General_CP1_CI_AS
73 SQLCHAR 0 250 "\t" 74 MDU_VALID_ADDR_CT SQL_Latin1_General_CP1_CI_AS
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 21, 2009 at 6:42 am
The skipped column is the last one in the sql table, and does not show up in the text file I receive.
October 21, 2009 at 8:06 pm
dzirkelb (10/7/2009)
When I run it as SQLINT, I get no erros; however, I get bad data. Example:What should be data of 1, results on the import of 153104672.
Any ideas?
Hmmm...not sure what that could be. Is the "wrong" data wrong in that it is in the wrong column? For example, you say that where you should be seeing 1 you are seeing 153104672. Should you be seeing 153104672 on the same row but in another column? Also, is the data wrong in all columns?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 22, 2009 at 6:40 am
The data that is being inserted is nowhere on the text file I receive, i have no idea where it is coming from. All of the number fields are incorrect during the process, text field are fine.
October 22, 2009 at 8:20 am
Could you post the target table structure, and mabye the first 100 or so lines of the data file as an attachment?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 22, 2009 at 10:23 am
I'm not sure if there is a way to export the table structure in a query, so I'll just copy / paste the data in here. Here is the table called AlliedInventoryMaster:
[Allied Stk #] varchar 255
[Mfr Name] varchar 255
[Mfr Part #] varchar 255
[Product Desc] varchar 255
Price float 8
[UOM and Qty] varchar 255
[Lead Days] int 4
[Sells in mults of] int 4
[Available Stock] int 4
[Min Qty] int 4
[Catalog Page] int 4
URL varchar 255
ID int 4 (primary key, identity, increment 1)
ImportDateTime smalldatetime 4 (default value is getdate())
They all allow nulls minus the ID
Here is the stored procedure that runs:
CREATE PROCEDURE AlliedInventoryDelete AS
TRUNCATE TABLE AlliedInventoryMaster
BULK
INSERT AlliedInventorymaster
FROM '\\........-elect-part-file.txt'
WITH
(
FORMATFILE = '\\......AlliedInventoryMaster.fmt'
)
GO
Here is the .fmt file:
8.0
12
1 SQLCHAR 0 255 "\t" 1 Allied ""
2 SQLCHAR 0 255 "\t" 2 Mfr ""
3 SQLCHAR 0 255 "\t" 3 Mfr ""
4 SQLCHAR 0 255 "\t" 4 Product ""
5 SQLINT 0 4 "\t" 5 Price ""
6 SQLCHAR 0 255 "\t" 6 UOM ""
7 SQLINT 0 4 "\t" 7 Lead ""
8 SQLINT 0 4 "\t" 8 Sells ""
9 SQLINT 0 4 "\t" 9 Available ""
10 SQLINT 0 4 "\t" 10 Min ""
11 SQLINT 0 4 "\t" 11 Catalog ""
12 SQLCHAR 0 255 "\r" 12 URL ""
One thing I do remember doing during my testing was I moved the ImportDateTime field from somewhere in the middle to the end in design view. I am not sure if that would mess anything up or not.
Attached is a portion of the text file
October 22, 2009 at 7:22 pm
I'd recommend that you take another look at Books Online for how to skip columns. You can't just skip them by leaving them out no matter where they are in the table.
Since all of your delimiters are the same, you could use an alternative... create a view for the table minus the IDENTITY and the ImportDateTime columns and do the BULK INSERT on the view instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2009 at 7:24 pm
Also, the book is right... change everything in the format file to SQLSCHAR and ad more column width... anything that's an INT should be set to at least 10, not 4.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2009 at 4:52 pm
dzirkelb, sorry, flu. I'll try to mess with it this week, but in the mean time, I would do what Jeff said if I were you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply