August 4, 2017 at 7:21 am
Hi Guys,
I an trying to do a simple upload from a txt file to a table. I know it should be a simple bcp command but if i have to load with a where clause how can we do it. Take any small text file as an example.
August 4, 2017 at 7:27 am
Use OPENROWSET with BULK option
Far away is close at hand in the images of elsewhere.
Anon.
August 7, 2017 at 4:23 am
Hi David. That's a wonderful solution to get results with where clause. However i an stuck at a situation. The number of columns in data file and in format file are same. Still while running the following cmd, it gives error "Msg 4823, Level 16, State 1, Line 1
Cannot bulk load. Invalid column number in the format file "\\serverlocation\formatfile.fmt":
Selext * from openrowset(bulk '\\serverlocation\filename.txt', format file = '\\serverlocation\formatfile.fmt', firstrow = 2) tbl
File name is tab delimited and at the of line is CRLF. Format file is like:
10.0
24
1 SQLNCHAR 0 510 "\t" 1 [column 1 country name] SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 510 "\t" 1 [column 2 country name] SQL_Latin1_General_CP1_CI_AS
And so on... Till 24 columns list.
August 7, 2017 at 4:39 am
As a guess, if you posted part of the file you are using, I would say it is your server column order begin duplicated.
Far away is close at hand in the images of elsewhere.
Anon.
August 7, 2017 at 5:31 am
David Burrows - Monday, August 7, 2017 4:39 AMAs a guess, if you posted part of the file you are using, I would say it is your server column order begin duplicated.
Hi David, that was a typo. Please ignore that repeat of 1 in the format file. The column numbers are same in both host file field order and server column order. It matches with that in file as well. One point here is that column names have space in between but separated by each other with tab delimiter.
August 7, 2017 at 5:46 am
That is what is causing the error, either remove the spaces or put quotes around the column names
1 SQLNCHAR 0 510 "\t" 1 "column 1" SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 510 "\r" 2 "column 2" SQL_Latin1_General_CP1_CI_AS
Far away is close at hand in the images of elsewhere.
Anon.
August 7, 2017 at 7:20 am
David Burrows - Monday, August 7, 2017 5:46 AMThat is what is causing the error, either remove the spaces or put quotes around the column names
1 SQLNCHAR 0 510 "\t" 1 "column 1" SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 510 "\r" 2 "column 2" SQL_Latin1_General_CP1_CI_AS
It worked David. But here are few more. I tried solving them by myself but in vain so came back. Here are the three errors i am getting:
Bulk load data conversion error (truncation) for row 2, column 1 (column name).
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null").
August 7, 2017 at 7:42 am
Make sure line 24 in your format file has "\r\n" as a terminator
Check your input file is tab delimited and all the rows have 24 columns
Far away is close at hand in the images of elsewhere.
Anon.
August 7, 2017 at 8:07 am
David Burrows - Monday, August 7, 2017 7:42 AMMake sure line 24 in your format file has "\r\n" as a terminator
Check your input file is tab delimited and all the rows have 24 columns
Yes i already cross verified all these points.
August 7, 2017 at 8:25 am
SQLNCHAR indicates Unicode (2 bytes per char)
Therefore the host file data length need to be twice the table column size
If your column is nvarchar(510) then the format file should have 1020
1 SQLNCHAR 0 1020 "\t" 1 "column 1" SQL_Latin1_General_CP1_CI_AS
Far away is close at hand in the images of elsewhere.
Anon.
August 7, 2017 at 11:12 am
David Burrows - Monday, August 7, 2017 8:25 AMSQLNCHAR indicates Unicode (2 bytes per char)
Therefore the host file data length need to be twice the table column size
If your column is nvarchar(510) then the format file should have 1020
1 SQLNCHAR 0 1020 "\t" 1 "column 1" SQL_Latin1_General_CP1_CI_AS
Tried David. It also doesn't work.
August 7, 2017 at 11:43 pm
Anyone any insight on this please?
August 8, 2017 at 1:39 am
I tested your format file on some test data to replicate your error and then my solution which worked without error.
Without seeing the query you are running and the format file and data file I cannot tell why you are getting the error.
I will seek some help from others.
Far away is close at hand in the images of elsewhere.
Anon.
August 8, 2017 at 5:37 am
This isn't a solution to your current issue, but more of a work around.
Have you tried uploading the file via the Import/Export Wizard in a non-prod environment and saving (in addition to or rather than executing) the resulting SSIS package? Then see if the SSIS package works for the import more consistently?
August 8, 2017 at 5:39 am
Check your field terminators.
Field 2 in the example above has a terminator "/r" - is it how it is in the actual file?
"/r" would be expected for the last column, not the 2nd one.
And if your data file originated from a Windows application - it must be "/r/n", as it's already has been pointed out.
Can you post the whole format file, as it is?
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply