July 26, 2017 at 7:26 am
Hi,
My table is getting loaded with 0 rows .i am using the below format file to load.
Table DDL
CREATE TABLE [dbo].[TEST](
[VERSION] [nvarchar](10) NULL,
[ELEMENT_ID] [decimal](18, 0) NULL,
[LOCNO] [nvarchar](20) NULL,
[MATNR] [nvarchar](40) NULL,
[MNG01] [decimal](13, 3) NULL
)
now the data file which i want to load
VERSION;ELEMENT_ID;LOCNO;MATNR;MNG010;
"PV001";19007237;"MSP1";"ZMSROHIBP01";10
"PV001";19005163;"MKP1";"MK-FRAME";5
"PV001";19005085;"DC1";"MGO_2_PHONE_A";100
"PV001";19006428;"L722";"TS3_PHONE_D";60
The format file i am using
11.0
6
1 SQLCHAR 0 0 "\"" 0 FIRST_QUOTE SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 10 "\";\"" 1 VERSION SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 18 "\";\"" 2 ELEMENT_ID ""
4 SQLCHAR 0 20 "\";\"" 3 LOCNO SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 40 "\";\"" 4 MATNR SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 13 ""\"\n" 5 MNG01 ""
there something need to change in format file.
The code i am using
BULK INSERT TEST
FROM 'E:\TEST_OUT.csv' WITH
(FIELDTERMINATOR=';',
ROWTERMINATOR ='0x0a',
FIRSTROW = 2, FORMATFILE = 'E:\TEST_FMT_OUT.fmt' )
let me know where i m doing wrong......my data's few columns are with double qoute
July 26, 2017 at 7:40 am
What is the error you are getting?
😎
July 26, 2017 at 7:46 am
Hi,
it is not giving any error but the data is not getting loaded.
0 rows processed.
Assuming i am doing some thing wrong with format files.
July 26, 2017 at 7:48 am
Your format does not match the data, not all the data is quote delimited
try this
1 SQLCHAR 0 0 "\"" 0 FIRST_QUOTE SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 10 "\";" 1 VERSION SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 18 ";\"" 2 ELEMENT_ID ""
4 SQLCHAR 0 20 "\";\"" 3 LOCNO SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 40 "\";" 4 MATNR SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 13 "\n" 5 MNG01 ""
p.s. also check your line termination
\n = LF
\r\n = CR/LF
Far away is close at hand in the images of elsewhere.
Anon.
July 26, 2017 at 7:50 am
skmoh2 - Wednesday, July 26, 2017 7:46 AMHi,it is not giving any error but the data is not getting loaded.
0 rows processed.
Assuming i am doing some thing wrong with format files.
Add an error file clause to the format file to capture the error(s)
😎
July 27, 2017 at 9:15 am
Hi ,
Thanks David for the quick reply and it helped.But what if i have data with double qoute in the middle and will it start from "FIRST QOUTE"
Exp:
empname,city
abc,"usa" CRLF
xyz,"uk" CRLF
Thanks
July 27, 2017 at 9:26 am
skmoh2 - Thursday, July 27, 2017 9:15 AMHi ,
Thanks David for the quick reply and it helped.But what if i have data with double qoute in the middle and will it start from "FIRST QOUTE"Exp:
empname,city
abc,"usa" CRLF
xyz,"uk" CRLF
Thanks
Not quite sure what you mean, based on the above the two delimiters would be
",\""
and
"\"\r\n"
and this assumes there really is not a space before CRLF
Far away is close at hand in the images of elsewhere.
Anon.
July 27, 2017 at 10:13 am
H David,
But that code is not working.i tried but it is not loading.its giving 0 rows processed but with no error
11.0
2
1 SQLCHAR 0 128 ",\"" 1 COMPONENT SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 128 "\"\r\n" 2 VERSION SQL_Latin1_General_CP1_CI_AS
Data
COMPONENT,VERSION
OUTBOUND ANALYTICAL DATA MODEL,"1708.0.0"
QUERY
BULK INSERT TIBP_VERSION
FROM 'E:\apps\abc.csv' WITH
(FIELDTERMINATOR=',',
ROWTERMINATOR ='\r\n',
FIRSTROW = 2, FORMATFILE = 'E:\apps\aabc_VERSION.fmt' )
July 27, 2017 at 10:27 am
Hey David,
i got the answer,its due to the space between the words in 1st column.Any idea how to consider those space as well
July 28, 2017 at 4:27 am
skmoh2 - Thursday, July 27, 2017 10:27 AMHey David,i got the answer,its due to the space between the words in 1st column.Any idea how to consider those space as well
Why would the spaces be a problem?
Firstly you do not need to specify FIELDTERMINATOR and ROWTERMINATOR when using a FORMATFILE
Secondly ignored rows using FIRSTROW still have to conform to the format file, i.e. BCP ignores rows AFTER parsing the file
Your heading VERSION is not quote delimited
This
COMPONENT,"VERSION"
OUTBOUND ANALYTICAL DATA MODEL,"1708.0.0"
will work correctly
If you have optional leading/trailing spaces in your data you need to process them post load
If your data does not contain commas within the data (even within quotes) then load the data into a staging table and process accordingly
Or you can do it using OPENROWSET with the following format file
11.0
2
1 SQLCHAR 0 128 "," 1 COMPONENT SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 128 "\r\n" 2 VERSION SQL_Latin1_General_CP1_CI_AS
SELECT LTRIM(COMPONENT) AS [COMPONENT],
REPLACE([VERSION],'"','') AS [VERSION]
FROM OPENROWSET(BULK 'C:\TEMP\test.txt',
FIRSTROW = 2,
FORMATFILE = 'C:\TEMP\test.fmt'
) x;
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply