February 8, 2016 at 1:41 pm
i would like to load attached flat file(loadfile.txt) to two tables
First table is #ShipmentTracking where first 6 lines need to loaded
Line 1 -ShimpmentID
Line 2- ShippedDate
Line 4- ShipmentAddress
Second table #ShipmentContents is where contents to be loaded
--ShipmentTrackingID need to added from previous step to this table
--I don't need columns Who will prep?,Prep Type,Who will label?
DDL
IF OBJECT_ID('tempdb..#ShipmentTracking','U') IS NOT NULL
DROP TABLE #ShipmentTracking
IF OBJECT_ID('tempdb..#ShipmentContents','U') IS NOT NULL
DROP TABLE #ShipmentContents
Create table #ShipmentTracking
(
ShipmentTrackingID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
ShimpmentID VARCHAR(50) NOT NULL,
ShippedDate datetime,
ShipmentAddress varchar(2000)
)
Create table #ShipmentContents
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[MerchantSKU] [varchar](50) NULL,
[Title] [varchar](8000) NULL,
[ASIN] [varchar](50) NULL,
[FNSKU] [varchar](50) NULL,
[externalid] [varchar](50) NULL,
[Condition] [varchar](50) NULL,
[Shipped] [varchar](500) NULL,
ShipmentTrackingID int NOT NULL
)
I have created format file but not able to load, any help would be appreciated
February 8, 2016 at 2:28 pm
Your format file is wrong. You should define the format file according to the data file and map those columns to the table, not the other way around.
10.0
10
1 SQLCHAR 0 8000 "\t" 2 "Merchant SKU" ""
2 SQLCHAR 0 8000 "\t" 3 Title ""
3 SQLCHAR 0 8000 "\t" 4 ASIN ""
4 SQLCHAR 0 8000 "\t" 5 FNSKU ""
5 SQLCHAR 0 8000 "\t" 6 "external id" ""
6 SQLCHAR 0 8000 "\t" 7 Condition ""
7 SQLCHAR 0 8000 "\t" 0 NotUsed ""
8 SQLCHAR 0 8000 "\t" 0 NotUsed ""
9 SQLCHAR 0 8000 "\t" 0 NotUsed ""
10 SQLCHAR 0 8000 "\r" 8 Shipped ""
If you don't have the ShipmentTrackingID in the file, you need to make it nullable to be able to load the file into the table. This shouldn't be a problem as this should only be a staging table.
Other than that, the code is pretty simple for ShipmentContents. You can add other options if you want, such as an error file in case you want to log them.
BULK INSERT ShipmentContents
FROM 'C:\Users\Public\Documents\Loadfile.txt'
WITH
(
FIRSTROW = 2
, FORMATFILE = 'C:\Users\Public\Documents\Format.fmt'
, TABLOCK
)
For the ShipmentTracking you could either create a new format file with some imagination or load the first rows as a single column and parse them using SQL.
February 8, 2016 at 2:57 pm
thanks for your response. looks like you removed first set of data and loaded. when i try to load with first row as 9 table is not getting populated.
BULK INSERT ShipmentContents
FROM 'D:\loadfile.txt'
WITH
(
FIRSTROW = 9
, FORMATFILE = 'D:\Format.fmt'
, TABLOCK
)
I copied format file you provided, it is not inserting any records either doesnt throw any error? I a doing something wrong. I updated format file last line as below
10 SQLCHAR 0 8000 "\r\ n" 8 Shipped "" --space removed after \ in file
February 8, 2016 at 3:10 pm
Trust me, it works the way I posted it with the data file as you posted it.
The first row option doesn't really count rows as you might expect, it counts rows as defined by the format file.
February 8, 2016 at 3:17 pm
Thanks it worked let me work on first set of data in the file.
Thanks again for your time.
February 8, 2016 at 4:29 pm
mxy (2/8/2016)
Thanks it worked let me work on first set of data in the file.Thanks again for your time.
How? When I try to load the file with Luis' code, I get the following...
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ShipmentTrackingID', table 'tempdb.dbo.#ShipmentContents___________________________________________________________________________________________________000000000006'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The header information in the file is the problem and the "first row" of 2 doesn't contain enough columns of data to offset things.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2016 at 4:37 pm
Jeff Moden (2/8/2016)
mxy (2/8/2016)
Thanks it worked let me work on first set of data in the file.Thanks again for your time.
How? When I try to load the file with Luis' code, I get the following...
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ShipmentTrackingID', table 'tempdb.dbo.#ShipmentContents___________________________________________________________________________________________________000000000006'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The header information in the file is the problem and the "first row" of 2 doesn't contain enough columns of data to offset things.
Jeff, the DDL needs to be changed to make ShipmentTrackingID nullable as this is not available in the file. Making that change, the header information causes no problem.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply