Hi All

This Bala,

I need fmt(format ) file for below values

Can you please provide me any one.

Thanks

Venkadesan.E

10.0
6
1       SQLCHAR             0       12      "\","     1     Stuid              ""
2       SQLCHAR             0       20      "\","     2     Stuname            Latin1_General_CI_AI
3       SQLCHAR             0       100     "\","     4     DOJ                Latin1_General_CI_AI
4       SQLCHAR             0       12      ","       5     English            ""
5       SQLCHAR             0       12      ","       6    Math               ""
6       SQLCHAR             0       12      "\r\n"    7     Science            ""

Hi thanks for care

i need format file only because my table structure will change every time.

thanks

bala

• Edited by venkatflower87 Wednesday, May 16, 2012 2:47 PM

Should be something like this:

9.0       
8       
1    SQLCHAR    0   1     "\"" 0   Unwanted   ""
2  SQLCHAR   0 3  "\","\" 1 Stuid  Latin1_General_CI_AS
3  SQLCHAR   0 100  "\","\" 2 Stuname  Latin1_General_CI_AS
4  SQLCHAR   0 50  "\","\" 3 Class  Latin1_General_CI_AS
5  SQLCHAR   0 50  "\"," 4 DOJ   Latin1_General_CI_AS
6  SQLCHAR   0 100  ","  5 english  ""
7  SQLCHAR   0 100  ","  6 Math  ""
8  SQLCHAR   0 100  "\r\n"  7 Sience  ""

Kalman, if you have nothing to help with the question, keep your mouth shut. You may have a crush on SSIS, but SSIS may have a different geometry than the application. Or be part of the developer's arsenal.

As it stands, this file is an easy match for BULK INSERT, and it would take me far long time to import it with SSIS - since I would first have to learn it.

So here is a format file. The trick is to add an extra column for the empty space before the first quote:

9.0
7
1 SQLCHAR 0 0 "\""    0 ""       ""
2 SQLCHAR 0 0 "\",\"" 1 Stuid    Latin1_General_CI_AS
3 SQLCHAR 0 0 "\",\"" 2 Stuname  Latin1_General_CI_AS
4 SQLCHAR 0 0 "\","   3 DOJ      Latin1_General_CI_AS
5 SQLCHAR 0 0 ","     4 English  ""
6 SQLCHAR 0 0 ","     5 Math     ""
7 SQLCHAR 0 0 "\r\n"  6 Science  ""

First line is version number for the format, 9.0 = SQL 2005.
Next line is the number of fields in the file. Third line and on describes the fields in the file.

First column is field number. Next is the data type, which is always SQLCHAR for a text file. (SQLNCHAR for a Unicode file.) Third column is prefix length, applies to binary files to carry on. Fourth field is length for fixed-length format. Fifth is the terminator. The key is to include the quote as part of the terminator.

Sixth column is column number in the target table, first column is 1. 0 means "ignore". You need to review these numbers, since I don't know your table. Seventh column is column name, but this is ignored by BULK INSERT. Last column is collation.

Note that the header row in the file has one more field than the data. It seems to be Class that is missing.

Hi Erland Sommarskog,

It is relay helpful for me and it is working fine .

I have few more question to you.

Please tell me how to below data format file look .

Stuid,Stuname,Class,DOJ,English,Math,Science,"name1"
"S1","Ram","10/31/2011,Monday",40,32,50,"Jon1"
"S2","Bala","10/31/2011,Monday",50,45,69,"Jon1"
"S3","Sam","10/31/2011,Monday",74,78,79,"Jon1"
"S4","Jon","10/31/2011,Monday",65,58,89,"Jon1"
"S5","Jos","10/31/2011,Monday",41,25,69,"Jon1"
"S6","Jim","10/31/2011,Monday",74,41,41,"Jon1"
"S7","Jack","10/31/2011,Monday",98,57,47,"Jon1"
"S8","Sate","10/31/2011,Monday",87,73,45,"Jon1"
"S9","Brb","10/31/2011,Monday",47,89,65,"Jon1"
"S10","Jom","10/31/2011,Monday",14,100,47,"Jon1"

2)And important to me how you writing column terminator .

For first row --- "\",\"".

second row-- "\",\"" 

Third column--"\","

How to defined last column terminator .

This above terminator  how you defined.what logic we need use in order write terminator . Now i have file text qualifier (")  in feature i have for (||)this simple also.how to change (") to (||) simple

Thanks

Bala

The terminator for the last column in your last example wouldbe "\"\r\n".

The column terminator is enclosed in double quotes. To inculde the double quote in the terminator you need to escape it. \r\n is Carriage Return-Line Feed, the standard line-ending for Windows.

In another post, you said: i need format file only because my table structure will change every time. Beware that this also means that you need a new format file every time.

Hi Erland Sommarskog

Thanks for your replay.

When i used  last column terminator this  "\"\r\n" i am the below result.

How to remove last column (") simple.

Format file look like i used

9.0
8
1 SQLCHAR 0 0 "\""    0 ""       ""
2 SQLCHAR 0 0 "\",\"" 1 Stuid    Latin1_General_CI_AS
3 SQLCHAR 0 0 "\",\"" 2 Stuname  Latin1_General_CI_AS
4 SQLCHAR 0 0 "\","   3 DOJ      Latin1_General_CI_AS
5 SQLCHAR 0 0 ","     4 English  ""
6 SQLCHAR 0 0 ","     5 Math     ""
7 SQLCHAR 0 0 ","     6 Science  ""
8 SQLCHAR 0 0 "\"\r\n" 7 Test1 Latin1_General_CI_AS

Thanks

Venkadesan.E

• Edited by venkatflower87 Thursday, May 17, 2012 8:28 AM format

Hi,

From MSDN..

------------------------------

To use bcp with the format option to create this format file, at the Windows command prompt, enter:

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

---------------------------------

you can use this command to generate the format file incase your table structure changes every time

Regards
Satheesh

• Edited by Satheesh VariathMicrosoft community contributor Thursday, May 17, 2012 8:48 AM

Please go back and read my first post where I gave a short explanation of the format file. From that you should be able to determine what is the approrpiate terminator for the seventh field.

You see, I am not only in these forum to spoonfeed solutions when you have a particular problem. My ambition is that you also will learn so that you are able to handle similar situations on your own in the future.

Kalman, if you have nothing to help with the question, keep your mouth shut. You may have a crush on SSIS, but SSIS may have a different geometry than the application. Or be part of the developer's arsenal.

As it stands, this file is an easy match for BULK INSERT, and it would take me far long time to import it with SSIS - since I would first have to learn it.

So here is a format file. The trick is to add an extra column for the empty space before the first quote:

9.0
7
1 SQLCHAR 0 0 "\""    0 ""       ""
2 SQLCHAR 0 0 "\",\"" 1 Stuid    Latin1_General_CI_AS
3 SQLCHAR 0 0 "\",\"" 2 Stuname  Latin1_General_CI_AS
4 SQLCHAR 0 0 "\","   3 DOJ      Latin1_General_CI_AS
5 SQLCHAR 0 0 ","     4 English  ""
6 SQLCHAR 0 0 ","     5 Math     ""
7 SQLCHAR 0 0 "\r\n"  6 Science  ""

First line is version number for the format, 9.0 = SQL 2005.
Next line is the number of fields in the file. Third line and on describes the fields in the file.

First column is field number. Next is the data type, which is always SQLCHAR for a text file. (SQLNCHAR for a Unicode file.) Third column is prefix length, applies to binary files to carry on. Fourth field is length for fixed-length format. Fifth is the terminator. The key is to include the quote as part of the terminator.

Sixth column is column number in the target table, first column is 1. 0 means "ignore". You need to review these numbers, since I don't know your table. Seventh column is column name, but this is ignored by BULK INSERT. Last column is collation.

Note that the header row in the file has one more field than the data. It seems to be Class that is missing.

Is anyone able to help me with the following case:

I have a UNICODE .csv file with the following contents. What should my .fmt file look like? especially what field delimiter should I use??

"123","abc","245","",""

"123","abc","245","",""

"456","bcd","678","",""

I know that I should use SQLNCHAR  instead of SQLCHAR, but what else?

If someone could please help me, I'd be really grateful!

Next time, start a new thread and don't piggyback on an old one.

Here is a format file should look for your data file:

10.0
6
1 SQLNCHAR  0 0 "\"\0"          0 ""    ""
2 SQLNCHAR  0 0 "\"\0,\0\"\0"   1 col1  ""
3 SQLNCHAR  0 0 "\"\0,\0\"\0"   2 col2  ""
4 SQLNCHAR  0 0 "\"\0,\0\"\0"   3 col3  ""
5 SQLNCHAR  0 0 "\"\0,\0\"\0"   4 col4  ""
6 SQLNCHAR  0 0 "\"\0\r\0\n\0"  5 col5  ""

Or at least I hope this is correct...

The format file itself is strictly ANSI, so you need to specify the bytes. \0 is 0x00. For the double quote that would be \0\", but since Wintel is a little-endian environment, the bytes should be swapped, whence \"\0.

Your file seems to have five field, but the format file has six. There is an empty field which is terminated by the first double quote and this file is not imported.

Abusive and helpful. Unusual.

This reply would have been so much nicer without "shut your mouth".

I was thinking the same thing Brian.
Moreover, Really unexpected and unusual when it comes from Erland.