July 19, 2010 at 3:37 pm
Hi,
Im using BULK INSERT command to import data into a Table which has IDENTITY Column. I build a format file to skip data import into IDENTITY Column as my data file did not had IDENTITY COLUMN.
But I'm getting the following error:
Msg 4823, Level 16, State 1, Line 1
Cannot bulk load. Invalid column number in the format file "C:\Data\DSA FORMAT FILE.txt".
My Table is:
[CP Code] varchar(50),
[Time] varchar(50),
[Total Pageviews] varchar(50),
[Total Volume in MB] varchar(50),
[Edge Traffic Volume in MB] varchar(50),
[Midgress Traffic Volume in MB] varchar(50),
[Origin Traffic Volume in MB] varchar(50),
[Edge Requests] varchar(50),
[Midgress Requests] varchar(50),
[Origin Requests] varchar(50),
[Total Download Volume in MB] varchar(50),
[Edge Download Response Volume in MB] varchar(50),
[Midgress Download Response Volume in MB] varchar(50),
[Origin Download Response Volume in MB] varchar(50),
[Total Upload Volume in MB] varchar(50),
[Edge Upload Request and Response Volume in MB] varchar(50),
[Midgress Upload Request and Response Volume in MB] varchar(50),
[Origin Upload Request and Response Volume in MB] varchar(50),
[Edge OK Requests 200 206 210] varchar(50),
[Edge 304 Requests] varchar(50),
[Edge Redirect Requests 301 302] varchar(50),
[Edge Permission Requests 401 403 415] varchar(50),
[Edge Server Error Requests 500 501 502 503 504] varchar(50),
[Edge Client Abort Requests 000] varchar(50),
[Edge Other Requests(all other status codes)] varchar(50),
[Edge 403 Requests] varchar(50),
[Edge 404 Requests] varchar(50),
[Origin 404 Requests] varchar(50),
[Origin OK 200 206 210 Requests] varchar(50),
[Origin 304 Requests] varchar(50),
[Origin Redirect 301 302 Requests] varchar(50),
[Origin Permission 401 403 415 Requests] varchar(50),
[Origin Server Error Requests 500 501 502 503 504] varchar(50),
[Origin Other Requests (all other status codes)] varchar(50)
My Format File is:
10.0
35
1 SQLCHAR 0 50 "\t" 1 "CP Code" "SQL_Lat..."
2 SQLCHAR 0 50 "\t" 2 "Time" "SQL_Lat..."
3 SQLCHAR 0 50 "\t" 3 "Total Pageviews" "SQL_Lat..."
4 SQLCHAR 0 50 "\t" 4 "Total Volume in MB" "SQL_Lat..."
5 SQLCHAR 0 50 "\t" 5 "Edge Traffic Volume in MB" "SQL_Lat..."
6 SQLCHAR 0 50 "\t" 6 "Midgress Traffic Volume in MB" "SQL_Lat..."
7 SQLCHAR 0 50 "\t" 7 "Origin Traffic Volume in MB" "SQL_Lat..."
8 SQLCHAR 0 50 "\t" 8 "Edge Requests" "SQL_Lat..."
9 SQLCHAR 0 50 "\t" 9 "Midgress Requests" "SQL_Lat..."
10 SQLCHAR 0 50 "\t" 10 "Origin Requests" "SQL_Lat..."
11 SQLCHAR 0 50 "\t" 11 "Total Download Volume in MB" "SQL_Lat..."
12 SQLCHAR 0 50 "\t" 12 "Edge Download Response Volume in MB" "SQL_Lat..."
13 SQLCHAR 0 50 "\t" 13 "Midgress Download Response Volume in MB" "SQL_Lat..."
14 SQLCHAR 0 50 "\t" 14 "Origin Download Response Volume in MB" "SQL_Lat..."
15 SQLCHAR 0 50 "\t" 15 "Total Upload Volume in MB" "SQL_Lat..."
16 SQLCHAR 0 50 "\t" 16 "Edge Upload Request and Response Volume in MB" "SQL_Lat..."
17 SQLCHAR 0 50 "\t" 17 "Midgress Upload Request and Response Volume in MB" "SQL_Lat..."
18 SQLCHAR 0 50 "\t" 18 "Origin Upload Request and Response Volume in MB" "SQL_Lat..."
19 SQLCHAR 0 50 "\t" 19 "Edge OK Requests 200 206 210" "SQL_Lat..."
20 SQLCHAR 0 50 "\t" 20 "Edge OK Requests" "SQL_Lat..."
21 SQLCHAR 0 50 "\t" 21 "Edge Redirect Requests 301 302" "SQL_Lat..."
22 SQLCHAR 0 50 "\t" 22 "Edge Permission Requests 401 403 415" "SQL_Lat..."
23 SQLCHAR 0 50 "\t" 23 "Edge Server Error Requests 500 501 502 503 504" "SQL_Lat..."
24 SQLCHAR 0 50 "\t" 24 "Edge Client Abort Requests 000" "SQL_Lat..."
25 SQLCHAR 0 50 "\t" 25 "Edge Other Requests(all other status codes)" "SQL_Lat..."
26 SQLCHAR 0 50 "\t" 26 "Edge 403 Requests" "SQL_Lat..."
27 SQLCHAR 0 50 "\t" 27 "Edge 404 Requests" "SQL_Lat..."
28 SQLCHAR 0 50 "\t" 28 "Origin 404 Requests" "SQL_Lat..."
39 SQLCHAR 0 50 "\t" 29 "Origin OK 200 206 210 Requests" "SQL_Lat..."
30 SQLCHAR 0 50 "\t" 30 "Origin 304 Requests" "SQL_Lat..."
31 SQLCHAR 0 50 "\t" 31 "Origin Redirect 301 302 Requests" "SQL_Lat..."
32 SQLCHAR 0 50 "\t" 32 "Origin Permission 401 403 415 Requests" "SQL_Lat..."
33 SQLCHAR 0 50 "\t" 33 "Origin Server Error Requests 500 501 502 503 504" "SQL_Lat..."
34 SQLCHAR 0 50 "\t" 34 "Origin Other Requests (all other status codes)" "SQL_Lat..."
35 SQLCHAR 0 0 "\r" 0 "Id_Num" "SQL_Lat..."
BULK INSERT Statement:
BULK INSERT DSA
FROM 'C:\Data\DSA.txt'
WITH
(
FORMATFILE = 'C:\Data\DSA FORMAT FILE.txt',
FIRSTROW = 2,
FIELDTERMINATOR ='\t',
ROWTERMINATOR = ''
)
Thanks
July 19, 2010 at 5:12 pm
For starters...
1. "SQL Lat..." is not a valid collation.
2. For field 35 (the ID field you're trying to ignore), there must be a length.
3. If you're using a format file (and you are), do not specify a row or field terminator in the bulk insert command.
Is the ID field really the last field in the file?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2010 at 5:23 pm
Hi
1. Its "SQL_Latin1_General_CP1_CI_AS" and not "SQL Lat..." . I just cut it short while posting.
2. The 35th column is the IDENTITY Column that I'm trying to ignore.Can you give me format file line for this Column?
I don't have the ID field in data file.
Thanks
July 25, 2010 at 12:46 pm
Like I said in my previous post, there MUST be a length for the 35th field and you MUST not specify any filed or row delimiters in your bulk insert command....
35 SQLCHAR 0 [font="Arial Black"]50[/font] "\r" 0 "Id_Num" "SQL_Lat..."
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2010 at 6:30 am
You could try bulk inserting into the corresponding VIEW in which case you don't need a format file at all
CREATE VIEW VIEW_DSA
AS
SELECT
[CP Code] ,
[Time] ,
[Total Pageviews] ,
[Total Volume in MB] ,
[Edge Traffic Volume in MB] ,
[Midgress Traffic Volume in MB] ,
[Origin Traffic Volume in MB] ,
[Edge Requests] ,
[Midgress Requests] ,
[Origin Requests] ,
[Total Download Volume in MB] ,
[Edge Download Response Volume in MB] ,
[Midgress Download Response Volume in MB] ,
[Origin Download Response Volume in MB] ,
[Total Upload Volume in MB] ,
[Edge Upload Request and Response Volume in MB] ,
[Midgress Upload Request and Response Volume in MB] ,
[Origin Upload Request and Response Volume in MB] ,
[Edge OK Requests 200 206 210] ,
[Edge 304 Requests] ,
[Edge Redirect Requests 301 302] ,
[Edge Permission Requests 401 403 415] ,
[Edge Server Error Requests 500 501 502 503 504] ,
[Edge Client Abort Requests 000] ,
[Edge Other Requests(all other status codes)] ,
[Edge 403 Requests] ,
[Edge 404 Requests] ,
[Origin 404 Requests] ,
[Origin OK 200 206 210 Requests] ,
[Origin 304 Requests] ,
[Origin Redirect 301 302 Requests] ,
[Origin Permission 401 403 415 Requests] ,
[Origin Server Error Requests 500 501 502 503 504] ,
[Origin Other Requests (all other status codes)]
FROM DSA
GO
BULK INSERT VIEW_DSA
FROM 'C:\Data\DSA.txt'
WITH
(
FIELDTERMINATOR = '\t',
FIRSTROW = 2
)
July 27, 2010 at 1:21 pm
I'm not sure why you don't want to adopt the VIEW approach, but you could try OPENROWSET BULK if you prefer
DSAFormat.fmt
7.0
34
1SQLCHAR050"\t"1CP_Code
2SQLCHAR050"\t"2Time
3SQLCHAR050"\t"3Total_Pageviews
4SQLCHAR050"\t"4Total_Volume_in_MB
5SQLCHAR050"\t"5Edge_Traffic_Volume_in_MB
6SQLCHAR050"\t"6Midgress_Traffic_Volume_in_MB
7SQLCHAR050"\t"7Origin_Traffic_Volume_in_MB
8SQLCHAR050"\t"8Edge_Requests
9SQLCHAR050"\t"9Midgress_Requests
10SQLCHAR050"\t"10Origin_Requests
11SQLCHAR050"\t"11Total_Download_Volume_in_MB
12SQLCHAR050"\t"12Edge_Download_Response_Volume_in_MB
13SQLCHAR050"\t"13Midgress_Download_Response_Volume_in_MB
14SQLCHAR050"\t"14Origin_Download_Response_Volume_in_MB
15SQLCHAR050"\t"15Total_Upload_Volume_in_MB
16SQLCHAR050"\t"16Edge_Upload_Request_and_Response_Volume_in_MB
17SQLCHAR050"\t"17Midgress_Upload_Request_and_Response_Volume_in_MB
18SQLCHAR050"\t"18Origin_Upload_Request_and_Response_Volume_in_MB
19SQLCHAR050"\t"19Edge_OK_Requests_200_206_210
20SQLCHAR050"\t"20Edge_304_Requests
21SQLCHAR050"\t"21Edge_Redirect_Requests_301_302
22SQLCHAR050"\t"22Edge_Permission_Requests_401_403_415
23SQLCHAR050"\t"23Edge_Server_Error_Requests_500_501_502_503_504
24SQLCHAR050"\t"24Edge_Client_Abort_Requests_000
25SQLCHAR050"\t"25Edge_Other_Requests(all_other_status_codes)
26SQLCHAR050"\t"26Edge_403_Requests
27SQLCHAR050"\t"27Edge_404_Requests
28SQLCHAR050"\t"28Origin_404_Requests
29SQLCHAR050"\t"29Origin_OK_200_206_210_Requests
30SQLCHAR050"\t"30Origin_304_Requests
31SQLCHAR050"\t"31Origin_Redirect_301_302_Requests
32SQLCHAR050"\t"32Origin_Permission_401_403_415_Requests
33SQLCHAR050"\t"33Origin_Server_Error_Requests_500_501_502_503_504
34SQLCHAR050"\r\n"34Origin_Other_Requests_(all_other_status_codes)
INSERT DSA
([CP Code],
[Time],
[Total Pageviews],
[Total Volume in MB],
[Edge Traffic Volume in MB],
[Midgress Traffic Volume in MB],
[Origin Traffic Volume in MB],
[Edge Requests],
[Midgress Requests],
[Origin Requests],
[Total Download Volume in MB],
[Edge Download Response Volume in MB],
[Midgress Download Response Volume in MB],
[Origin Download Response Volume in MB],
[Total Upload Volume in MB],
[Edge Upload Request and Response Volume in MB],
[Midgress Upload Request and Response Volume in MB],
[Origin Upload Request and Response Volume in MB],
[Edge OK Requests 200 206 210],
[Edge 304 Requests],
[Edge Redirect Requests 301 302],
[Edge Permission Requests 401 403 415],
[Edge Server Error Requests 500 501 502 503 504],
[Edge Client Abort Requests 000],
[Edge Other Requests(all other status codes)],
[Edge 403 Requests],
[Edge 404 Requests],
[Origin 404 Requests],
[Origin OK 200 206 210 Requests],
[Origin 304 Requests],
[Origin Redirect 301 302 Requests],
[Origin Permission 401 403 415 Requests],
[Origin Server Error Requests 500 501 502 503 504],
[Origin Other Requests (all other status codes)])
SELECT [CP_Code],
[Time],
[Total_Pageviews],
[Total_Volume_in_MB],
[Edge_Traffic_Volume_in_MB],
[Midgress_Traffic_Volume_in_MB],
[Origin_Traffic_Volume_in_MB],
[Edge_Requests],
[Midgress_Requests],
[Origin_Requests],
[Total_Download_Volume_in_MB],
[Edge_Download_Response_Volume_in_MB],
[Midgress_Download_Response_Volume_in_MB],
[Origin_Download_Response_Volume_in_MB],
[Total_Upload_Volume_in_MB],
[Edge_Upload_Request_and_Response_Volume_in_MB],
[Midgress_Upload_Request_and_Response_Volume_in_MB],
[Origin_Upload_Request_and_Response_Volume_in_MB],
[Edge_OK_Requests_200_206_210],
[Edge_304_Requests],
[Edge_Redirect_Requests_301_302],
[Edge_Permission_Requests_401_403_415],
[Edge_Server_Error_Requests_500_501_502_503_504],
[Edge_Client_Abort_Requests_000],
[Edge_Other_Requests(all_other_status_codes)],
[Edge_403_Requests],
[Edge_404_Requests],
[Origin_404_Requests],
[Origin_OK_200_206_210_Requests],
[Origin_304_Requests],
[Origin_Redirect_301_302_Requests],
[Origin_Permission_401_403_415_Requests],
[Origin_Server_Error_Requests_500_501_502_503_504],
[Origin_Other_Requests_(all_other_status_codes)]
FROM OPENROWSET (BULK 'C:\Data\DSA.txt',
FORMATFILE = 'C:\Data\DSAFormat.fmt'
, FIRSTROW = 2) AS Z
July 27, 2010 at 2:00 pm
Hi
I'm using this data for reporting purpose and in my final report I only need few columns. I was using Excel before to do that and now I'm just starting with Reporting Services. I'm imorting all the data into database first and then using views for data manipulation. Also I'm importing data from multiple CSV files so its better for me if I use views after BULK INSERTing data.
I'll ask more questions if I have.
Thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply