November 20, 2017 at 9:21 am
Hello,
I am working on a project that requires I insert white space columns in a file that will be exported as a fixed length text file.
I am importing the data from a csv file that does not contain the white space columns.
I have imported this csv file to a table, performed a few calculations and exported to a fixed length text file. I am having trouble creating the white space columns in the export file. Can someone assist me?
November 20, 2017 at 9:38 am
Barbara Ollivier - Monday, November 20, 2017 9:21 AMHello,
I am working on a project that requires I insert white space columns in a file that will be exported as a fixed length text file.
I am importing the data from a csv file that does not contain the white space columns.
I have imported this csv file to a table, performed a few calculations and exported to a fixed length text file. I am having trouble creating the white space columns in the export file. Can someone assist me?
How are you creating the file? SSIS? Some other means?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 20, 2017 at 9:43 am
Is it just a simple case of adding a blank string to your export?
SELECT col1, col2, col3,' ' as white_space FROM table1
How is the export done? can you share more background on how the export file is generated?
November 20, 2017 at 9:45 am
Phil Parkin - Monday, November 20, 2017 9:38 AMBarbara Ollivier - Monday, November 20, 2017 9:21 AMHello,
I am working on a project that requires I insert white space columns in a file that will be exported as a fixed length text file.
I am importing the data from a csv file that does not contain the white space columns.
I have imported this csv file to a table, performed a few calculations and exported to a fixed length text file. I am having trouble creating the white space columns in the export file. Can someone assist me?How are you creating the file? SSIS? Some other means?
Ultimately, yes, it will be part of an SSIS package. The client didn't get us their information (the csv file) until late Friday night, and of course the flat file is due to the state tomorrow. 🙂 Getting the file created is the priority today, and then I can build a package to handle future files. This process will be done quarterly, at a minimum.
November 20, 2017 at 9:51 am
anthony.green - Monday, November 20, 2017 9:43 AMIs it just a simple case of adding a blank string to your export?
SELECT col1, col2, col3,' ' as white_space FROM table1How is the export done? can you share more background on how the export file is generated?
It will be an SSIS package. Because of time constraints, I just need a file generated today. I could create the columns like you've suggested, thank you! I was wondering if there's a way to code it so that I can just type in the number of spaces needed, rather than hitting the space bar 7, 12, 18 times. There are several white space columns in each file, my the largest one 28.
November 20, 2017 at 9:56 am
Barbara Ollivier - Monday, November 20, 2017 9:51 AManthony.green - Monday, November 20, 2017 9:43 AMIs it just a simple case of adding a blank string to your export?
SELECT col1, col2, col3,' ' as white_space FROM table1How is the export done? can you share more background on how the export file is generated?
It will be an SSIS package. Because of time constraints, I just need a file generated today. I could create the columns like you've suggested, thank you! I was wondering if there's a way to code it so that I can just type in the number of spaces needed, rather than hitting the space bar 7, 12, 18 times. There are several white space columns in each file, my the largest one 28.
Use Use REPLICATE(' ',[MyWhiteSpaceLength])
😎
November 20, 2017 at 9:57 am
Eirikur Eiriksson - Monday, November 20, 2017 9:56 AMBarbara Ollivier - Monday, November 20, 2017 9:51 AManthony.green - Monday, November 20, 2017 9:43 AMIs it just a simple case of adding a blank string to your export?
SELECT col1, col2, col3,' ' as white_space FROM table1How is the export done? can you share more background on how the export file is generated?
It will be an SSIS package. Because of time constraints, I just need a file generated today. I could create the columns like you've suggested, thank you! I was wondering if there's a way to code it so that I can just type in the number of spaces needed, rather than hitting the space bar 7, 12, 18 times. There are several white space columns in each file, my the largest one 28.
Use REPLICATE(' ',[MyWhiteSpaceLength])
😎
Thank you!!!!
November 20, 2017 at 10:07 am
Barbara Ollivier - Monday, November 20, 2017 9:57 AMEirikur Eiriksson - Monday, November 20, 2017 9:56 AMBarbara Ollivier - Monday, November 20, 2017 9:51 AManthony.green - Monday, November 20, 2017 9:43 AMIs it just a simple case of adding a blank string to your export?
SELECT col1, col2, col3,' ' as white_space FROM table1How is the export done? can you share more background on how the export file is generated?
It will be an SSIS package. Because of time constraints, I just need a file generated today. I could create the columns like you've suggested, thank you! I was wondering if there's a way to code it so that I can just type in the number of spaces needed, rather than hitting the space bar 7, 12, 18 times. There are several white space columns in each file, my the largest one 28.
Use REPLICATE(' ',[MyWhiteSpaceLength])
😎Thank you!!!!
You are welcome
😎
November 20, 2017 at 10:51 am
Are you using a Fixed Width flat file destination in SSIS? You should be able to define all of your column widths there, without the need for any fancy REPLICATE T-SQL.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 20, 2017 at 11:46 am
No, the flat file that goes in to SSIS won't be fixed width, but it will have one fixed width column which will contain the white spaces along with other information.
November 20, 2017 at 12:07 pm
Barbara Ollivier - Monday, November 20, 2017 11:46 AMNo, the flat file that goes in to SSIS won't be fixed width, but it will have one fixed width column which will contain the white spaces along with other information.
This statement seems to conflict with this, from your original post:
I insert white space columns in a file that will be exported as a fixed length text file.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply