Unable to get format file to work

Question

Unable to get format file to work

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

Jeff Moden - Sunday, April 22, 2018 9:08 PM
ron.abbott - Sunday, April 22, 2018 8:25 PM
The txt file was generated by a software package that I am unfamiliar with.
Not sure why the format file suggests lengths of 400, and the create table says 200 for all bu one field.
Good grief. If the problem was a snake, it would have bitten me. All the slashes need to be changed to backslashes in the format file.
Also, the reason they all suggest 400 is so that the allowable size of the field in the file is bigger than the allowable size in the table so that if the file field data is larger than what the table will handle, it'll pop up an error instead of artificially truncating it.

And, yeah... you still need the \ r \ n field terminatior. I also suggest that you add -CRAW to your BCP command line just in case someone sends you ASCII characters above 127. That way you can see the actual untranslated values if they ever do and you want to tell them what to look for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ron.abbott SSC Veteran Points: 264 More actions · Answer 2

ron.abbott - Sunday, April 22, 2018 9:13 PM
Jeff Moden - Sunday, April 22, 2018 9:08 PM
ron.abbott - Sunday, April 22, 2018 8:25 PM
The txt file was generated by a software package that I am unfamiliar with.
Not sure why the format file suggests lengths of 400, and the create table says 200 for all bu one field.
Good grief. If the problem was a snake, it would have bitten me. All the slashes need to be changed to backslashes in the format file.
Also, the reason they all suggest 400 is so that the allowable size of the field in the file is bigger than the allowable size in the table so that if the file field data is larger than what the table will handle, it'll pop up an error instead of artificially truncating it.
It worked ! - Thanks so much.
Any idea why would the format file be generated with / instead of \ ?

Because I am not in USA ? Am in New Zealand. International format ?

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

ron.abbott - Sunday, April 22, 2018 9:13 PM
Jeff Moden - Sunday, April 22, 2018 9:08 PM
ron.abbott - Sunday, April 22, 2018 8:25 PM
The txt file was generated by a software package that I am unfamiliar with.
Not sure why the format file suggests lengths of 400, and the create table says 200 for all bu one field.
Good grief. If the problem was a snake, it would have bitten me. All the slashes need to be changed to backslashes in the format file.
Also, the reason they all suggest 400 is so that the allowable size of the field in the file is bigger than the allowable size in the table so that if the file field data is larger than what the table will handle, it'll pop up an error instead of artificially truncating it.
It worked ! - Thanks so much.
Any idea why would the format file be generated with / instead of \ ?

Yes... you told it to. Go back to that command and have a look at what you gave it for both column and row terminators. You told it to use forward slashes instead of backslashes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ron.abbott SSC Veteran Points: 264 More actions · Answer 4

Jeff Moden - Sunday, April 22, 2018 9:15 PM
ron.abbott - Sunday, April 22, 2018 9:13 PM
Jeff Moden - Sunday, April 22, 2018 9:08 PM
ron.abbott - Sunday, April 22, 2018 8:25 PM
The txt file was generated by a software package that I am unfamiliar with.
Not sure why the format file suggests lengths of 400, and the create table says 200 for all bu one field.
Good grief. If the problem was a snake, it would have bitten me. All the slashes need to be changed to backslashes in the format file.
Also, the reason they all suggest 400 is so that the allowable size of the field in the file is bigger than the allowable size in the table so that if the file field data is larger than what the table will handle, it'll pop up an error instead of artificially truncating it.
It worked ! - Thanks so much.
Any idea why would the format file be generated with / instead of \ ?
Yes... you told it to. Go back to that command and have a look at what you gave it for both column and row terminators. You told it to use forward slashes instead of backslashes.

Thanks so much for your help - now to try excluding some fields..

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

No problem. Thank you for the feed back. Excluding fields in the file is pretty easy... assign them a column number of 0 (zero) and give them a unique name. Since your files don't have headers, it would be handy to assign them a meaningful name so that someone in the future would know what they are without having to dig up the record layout for the file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

p.s. Almost without exception, I load files to a staging table first so that I can validate data, check for duplicates, etc, yada, yada. With that in mind, I also usually just load all the columns in the file so that I don't have to mess with anything if someone suddenly wakes up and wants to use previously excluded fields. It also makes it so I usually don't need format files but, when I do, I can also write a little ditty that reads the first line as a whole row, does some analysis as to what the delimiters are, and then generate a BCP format file and the command to do the import just by running a proc with the file path provided.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ron.abbott SSC Veteran Points: 264 More actions · Answer 7

Jeff Moden - Sunday, April 22, 2018 9:47 PM
p.s. Almost without exception, I load files to a staging table first so that I can validate data, check for duplicates, etc, yada, yada. With that in mind, I also usually just load all the columns in the file so that I don't have to mess with anything if someone suddenly wakes up and wants to use previously excluded fields. It also makes it so I usually don't need format files but, when I do, I can also write a little ditty that reads the first line as a whole row, does some analysis as to what the delimiters are, and then generate a BCP format file and the command to do the import just by running a proc with the file path provided.

Sounds good. My tables will be holding files for further validation. But some of them have over 200 fields, and I only need about 50 of them, so didnt want to manually enter all the fields into the table design.
My files actually do have a header (I had stripped them off for the test). Is there any way I can use the header field names to generate column names in a table easily ? (I would be happy with all varchar field types)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 8

ron.abbott - Sunday, April 22, 2018 10:44 PM
Jeff Moden - Sunday, April 22, 2018 9:47 PM
p.s. Almost without exception, I load files to a staging table first so that I can validate data, check for duplicates, etc, yada, yada. With that in mind, I also usually just load all the columns in the file so that I don't have to mess with anything if someone suddenly wakes up and wants to use previously excluded fields. It also makes it so I usually don't need format files but, when I do, I can also write a little ditty that reads the first line as a whole row, does some analysis as to what the delimiters are, and then generate a BCP format file and the command to do the import just by running a proc with the file path provided.
Sounds good. My tables will be holding files for further validation. But some of them have over 200 fields, and I only need about 50 of them, so didnt want to manually enter all the fields into the table design.
My files actually do have a header (I had stripped them off for the test). Is there any way I can use the header field names to generate column names in a table easily ? (I would be happy with all varchar field types)

Yes. Just use a string splitter to do so once you've loaded the header row. Just hope that the header row has precisely the same delimiters as the body of the file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)