uma_
asked on
mssql 2008 query
We need a SQL script to upload a tab separated file into a mssql server 2008 database.
Put together the script sample below - example only, it doesn't work but you get the idea
Bulk insert Mobile_Phones_and_Accessor
from 'H:\STWDATA\FTPExtracted\f
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
CODEPAGE='TEXT',
DATAFILETYPE='char',
FIRSTROW = 2,
KEEPNULLS
)
Put together the script sample below - example only, it doesn't work but you get the idea
Bulk insert Mobile_Phones_and_Accessor
from 'H:\STWDATA\FTPExtracted\f
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
CODEPAGE='TEXT',
DATAFILETYPE='char',
FIRSTROW = 2,
KEEPNULLS
)
James Murrell
have you read this http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/ great blog about how to do it
>> it doesn't work but you get the idea
it's working and am able to BULK INSERT with your query. Not sure what do you mean by doesn't work.
Your syntax is looking good.
http://msdn.microsoft.com/en-us/library/ms188365.aspx
it's working and am able to BULK INSERT with your query. Not sure what do you mean by doesn't work.
Your syntax is looking good.
http://msdn.microsoft.com/en-us/library/ms188365.aspx
Hi uma,
First of all you need to specify the codepage. 'TEXT' is no good there. It has to match coding of your file.
just in case here is more information about bulk insert
http://msdn.microsoft.com/en-us/library/ms188365.aspx
First of all you need to specify the codepage. 'TEXT' is no good there. It has to match coding of your file.
just in case here is more information about bulk insert
http://msdn.microsoft.com/en-us/library/ms188365.aspx
ASKER
shall i need to change the line
CODEPAGE='TEXT',
CODEPAGE='TEXT',
it depends on your file and system settings. explicit codepage should be better.
But if Sharath says he/she was able to insert the data with your query than maybe there is something wrong with your data file?
Could you provide any error messages?
But if Sharath says he/she was able to insert the data with your query than maybe there is something wrong with your data file?
Could you provide any error messages?
ASKER
sorry need to go ,i will get back to u soon
Thanks for ur help..
Thanks for ur help..
ASKER
The query in trying is
Bulk insert SDCData
from 'C:\WORKDRIVE\SHOPTHEWEB_D
Dropbox\ShopTheWeb\bulkupl
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
CODEPAGE='TEXT',
DATAFILETYPE='char',
FIRSTROW = 2,
KEEPNULLS
)
Table structure:
CREATE TABLE [dbo].[SDCData](
[OFFER_ID] [varchar](25) NULL,
[OFFER_TITLE] [varchar](96) NULL,
[PRICE] [varchar](50) NULL,
[OFFER_DESCRIPTION] [varchar](1024) NULL,
[MERCHANT_ID] [varchar](50) NULL,
[MERCHANT_NAME] [varchar](18) NULL,
[MERCHANT_SKU_NUMBER] [varchar](21) NULL,
[SDC_PRODUCT_ID] [varchar](50) NULL,
[MPN] [varchar](14) NULL,
[CATEGORY_NAME] [varchar](24) NULL,
[STOCK] [varchar](8) NULL,
[CATEGORY_ID] [varchar](50) NULL,
[STOCK_DESCRIPTION] [varchar](76) NULL,
[IMAGE_URL] [varchar](97) NULL,
[MAX_IMG_WIDTH] [varchar](50) NULL,
[MAX_IMG_HEIGHT] [varchar](50) NULL,
[SHIPPING_RATE] [varchar](50) NULL,
[SHIPPING_WEIGHT] [varchar](50) NULL,
[ZIP_CODE] [varchar](20) NULL,
[MANUFACTURER] [varchar](14) NULL,
[ATTRIBUTES] [varchar](136) NULL,
[MIN_CATEGORY_BID] [varchar](50) NULL,
[MERCHANT_CATEGORY_BID] [varchar](50) NULL,
[OFFER_URL_MIN_CATEGORY_BI
[OFFER_URL_MERCHANT_CATEGO
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Mobile-Phones-and-Accessories.zip
Bulk insert SDCData
from 'C:\WORKDRIVE\SHOPTHEWEB_D
Dropbox\ShopTheWeb\bulkupl
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
CODEPAGE='TEXT',
DATAFILETYPE='char',
FIRSTROW = 2,
KEEPNULLS
)
Table structure:
CREATE TABLE [dbo].[SDCData](
[OFFER_ID] [varchar](25) NULL,
[OFFER_TITLE] [varchar](96) NULL,
[PRICE] [varchar](50) NULL,
[OFFER_DESCRIPTION] [varchar](1024) NULL,
[MERCHANT_ID] [varchar](50) NULL,
[MERCHANT_NAME] [varchar](18) NULL,
[MERCHANT_SKU_NUMBER] [varchar](21) NULL,
[SDC_PRODUCT_ID] [varchar](50) NULL,
[MPN] [varchar](14) NULL,
[CATEGORY_NAME] [varchar](24) NULL,
[STOCK] [varchar](8) NULL,
[CATEGORY_ID] [varchar](50) NULL,
[STOCK_DESCRIPTION] [varchar](76) NULL,
[IMAGE_URL] [varchar](97) NULL,
[MAX_IMG_WIDTH] [varchar](50) NULL,
[MAX_IMG_HEIGHT] [varchar](50) NULL,
[SHIPPING_RATE] [varchar](50) NULL,
[SHIPPING_WEIGHT] [varchar](50) NULL,
[ZIP_CODE] [varchar](20) NULL,
[MANUFACTURER] [varchar](14) NULL,
[ATTRIBUTES] [varchar](136) NULL,
[MIN_CATEGORY_BID] [varchar](50) NULL,
[MERCHANT_CATEGORY_BID] [varchar](50) NULL,
[OFFER_URL_MIN_CATEGORY_BI
[OFFER_URL_MERCHANT_CATEGO
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Mobile-Phones-and-Accessories.zip
The problem is not within the query but within the file.
In your file at the end of each line you have Line Feed character only (U+000A)
In your query you are using '\n' which is interpreted as native new line sequence on Windows OS which is CR+LF (U+000D U+000A).
The simplest solution is to open the file and replace all LF with CRLF then the query will work as expected. Because the file is rather big I recommend using something like Notepad++ (that is what I used to solve the problem on my local machine) In search string you need to put
\n
and replace it with
\r\n
Regards
Lof
In your file at the end of each line you have Line Feed character only (U+000A)
In your query you are using '\n' which is interpreted as native new line sequence on Windows OS which is CR+LF (U+000D U+000A).
The simplest solution is to open the file and replace all LF with CRLF then the query will work as expected. Because the file is rather big I recommend using something like Notepad++ (that is what I used to solve the problem on my local machine) In search string you need to put
\n
and replace it with
\r\n
Regards
Lof
ASKER
Hi ,
I opened the file .WHen i serach for the word /n it throws no match found
Please advice
I opened the file .WHen i serach for the word /n it throws no match found
Please advice
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried ur method still not working...
Could you be more specific? What is not working?
I took your file and tried your query. It didn't work. Then I replaced all \n with \r\n and it worked.
Now I have uploaded the file I managed to import for you to try.
Please give some more details about 'not working...'
Is the file I uploaded not working?
Do you have problems converting \n to \r\n
Can you verify if proper new line characters are in the file you are trying to upload?
Have you installed Notepad++ or do you have any other text editor capable of doing more advanced things than Notepad.exe?
I took your file and tried your query. It didn't work. Then I replaced all \n with \r\n and it worked.
Now I have uploaded the file I managed to import for you to try.
Please give some more details about 'not working...'
Is the file I uploaded not working?
Do you have problems converting \n to \r\n
Can you verify if proper new line characters are in the file you are trying to upload?
Have you installed Notepad++ or do you have any other text editor capable of doing more advanced things than Notepad.exe?
ASKER
I didnt refresh my page before ur post...working on ur solution
ASKER
I saved ur file and tried the query
Bulk insert SDCData
from 'K:\mssql$10\Mobile-Phones
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
FIRSTROW = 2,
KEEPNULLS
)
Not working...
Bulk insert SDCData
from 'K:\mssql$10\Mobile-Phones
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
FIRSTROW = 2,
KEEPNULLS
)
Not working...
What is not working
Please give some more details about 'not working...'
Is the file I uploaded not working?
Do you have problems converting \n to \r\n
Can you verify if proper new line characters are in the file you are trying to upload?
Have you installed Notepad++ or do you have any other text editor capable of doing more advanced things than Notepad.exe?
Please give some more details about 'not working...'
Is the file I uploaded not working?
Do you have problems converting \n to \r\n
Can you verify if proper new line characters are in the file you are trying to upload?
Have you installed Notepad++ or do you have any other text editor capable of doing more advanced things than Notepad.exe?
ASKER
When i tried ur file i am getting this error.Did u import it successfuly?
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 157, column 2 (OFFER_TITLE).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 167, column 2 (OFFER_TITLE).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 177, column 2 (OFFER_TITLE).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 181, column 20 (MANUFACTURER).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 201, column 20 (MANUFACTURER).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 202, column 2 (OFFER_TITLE).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 261, column 2 (OFFER_TITLE).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 265, column 2 (OFFER_TITLE).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 299, column 2 (OFFER_TITLE).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 316, column 2 (OFFER_TITLE).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 327, column 21 (ATTRIBUTES).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 157, column 2 (OFFER_TITLE).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 167, column 2 (OFFER_TITLE).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 177, column 2 (OFFER_TITLE).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 181, column 20 (MANUFACTURER).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 201, column 20 (MANUFACTURER).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 202, column 2 (OFFER_TITLE).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 261, column 2 (OFFER_TITLE).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 265, column 2 (OFFER_TITLE).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 299, column 2 (OFFER_TITLE).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 316, column 2 (OFFER_TITLE).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 327, column 21 (ATTRIBUTES).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Ok,
First try adding
LASTROW = 100,
just below FIRSTROW
that will show you that the data can be imported that way. That is how I tested it, not to import all the data into my database. Now when I tried importing more I have errors. The data is not well formated, the data in file.
the errors are as follow
Bulk load data conversion error (truncation) for row 157, column 2 (OFFER_TITLE).
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 167, column 2 (OFFER_TITLE).
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 177, column 2 (OFFER_TITLE).
Bulk load data conversion error (truncation) for row 181, column 20 (MANUFACTURER).
Bulk load data conversion error (truncation) for row 201, column 20 (MANUFACTURER).
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 202, column 2 (OFFER_TITLE).
Bulk load data conversion error (truncation) for row 261, column 2 (OFFER_TITLE).
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 265, column 2 (OFFER_TITLE).
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 299, column 2 (OFFER_TITLE).
Bulk load data conversion error (truncation) for row 316, column 2 (OFFER_TITLE).
Bulk load data conversion error (truncation) for row 327, column 21 (ATTRIBUTES).
You may check them all one by one and you will see where the problem is.
for example: Firs error in line 157, second column in the file has 105 characters and the column in the table has only 96
First try adding
LASTROW = 100,
just below FIRSTROW
that will show you that the data can be imported that way. That is how I tested it, not to import all the data into my database. Now when I tried importing more I have errors. The data is not well formated, the data in file.
the errors are as follow
Bulk load data conversion error (truncation) for row 157, column 2 (OFFER_TITLE).
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 167, column 2 (OFFER_TITLE).
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 177, column 2 (OFFER_TITLE).
Bulk load data conversion error (truncation) for row 181, column 20 (MANUFACTURER).
Bulk load data conversion error (truncation) for row 201, column 20 (MANUFACTURER).
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 202, column 2 (OFFER_TITLE).
Bulk load data conversion error (truncation) for row 261, column 2 (OFFER_TITLE).
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 265, column 2 (OFFER_TITLE).
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 299, column 2 (OFFER_TITLE).
Bulk load data conversion error (truncation) for row 316, column 2 (OFFER_TITLE).
Bulk load data conversion error (truncation) for row 327, column 21 (ATTRIBUTES).
You may check them all one by one and you will see where the problem is.
for example: Firs error in line 157, second column in the file has 105 characters and the column in the table has only 96
Now I cross-posted you.
As I said for testing bulk insert I was using first 100 rows as I was not testing data correctness.
The first problem was that the file had wrong new line character thus SQL server was not able to read it at all. but now as you can see you have problems in few lines. If this is one off import you may want just remove the few lines from the file and then process them separately.
As I said for testing bulk insert I was using first 100 rows as I was not testing data correctness.
The first problem was that the file had wrong new line character thus SQL server was not able to read it at all. but now as you can see you have problems in few lines. If this is one off import you may want just remove the few lines from the file and then process them separately.
ASKER
Thanks a lot
Did it finally work for you?
ASKER
Excellent!!!Thanks a lot