0

I have text file with structure like this:

    123456789 , 1111111111 , 2222222222222 , xxxxxxx , zzzzzzz ,
    987654321 , 22222222222222 , 3333333333333333 , hhhhhhhhh , bbbbbbbbb ,
...

As you can see, delimiters are not at same place in second row, and rows bellow.

I need to use flat file as source, and fixed width format.

Fixed width format

How can i format columns to fixed width by delimiter for large file (>1GB)?

Preview should look like this (delimiters in same column):

123456789 , 1111111111     , 2222222222222    , xxxxxxx   , zzzzzzz   ,
987654321 , 22222222222222 , 3333333333333333 , hhhhhhhhh , bbbbbbbbb ,

With Notepad++ i can modify file to fixed width with TestFX -> TestFX Edit -> Line up multiple lines by (,). But Notepad++ cannot process large files.

Help, please.

Olinad
  • 187
  • 1
  • 1
  • 13

1 Answers1

1

Why not use a delimited format instead? Since your file is clearly not fixed width.

You can use a delimter of ',' and then trim the spaces off or use a delimiter of ' , '

Personally I would find the first to be less likely to cause problems later on.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • When i use delimited format for flat file, and try to cast it to int for example (DT_I4) or float (DT_R8) i get cast errors in derived column... Why is this? And there is no difference between other data rows. But when i use fixed width, i explicitly say column length on input and output. – Olinad Nov 04 '13 at 21:37
  • 1
    You cannot use fixed widtha s you do not have a fixed width file. AS to the other error, likely you need to put everything into a staging table that is all varchar and look at the data. It sounds as if some fields that should be all numbers are not which not an uncommon scenario when the data comes from a poorly designed database. You may need to write some dat cleaning routines. And NEVER ever ever import data directly from a file to a production table. – HLGEM Nov 04 '13 at 21:58
  • @Olinad so you can take data but got error in Derived Column, try to solve this problem. In your file you got strings... so if you convert them to int or a float you will get error... – Justin Nov 05 '13 at 07:13
  • In the end, i used delimited format and surprisingly everything went well. Thx to everybody. – Olinad Nov 06 '13 at 13:10