Cleaning flat file

Question

Hi,

I got problem, because i really don't know how to clean my file. My file looks like :

2014-01-01 00:05:00;;;1;1361;9511,4;15
2014-01-01 00:05:00;;;2;1814;19000;15
2014-01-01 00:05:00;;;3;2267;28500;15
2014-01-01 00:05:00;;;4;2721;38000;15
2014-01-01 00:10:00;;;1;1361;9511,4;15
2014-01-01 00:10:00;;;2;1814;19000;15

Where first two is date stamp. I need to extract 9 columns from this file. Can you help me ? 

Answer 1

Hi Meganee,

What are the 9 columns? Would you please share the result that you would like to get?

I notice that some of rows contain blank, semicolon and comma. However some of rows only contain blank and semicolon, right?

I assume you would like to get the 9 columns look like below:

Personally, you could try to read the every row in one column first, then convert the several special delimiters(e.g. blank, semicolon ) to uniform delimiter (e.g. comma) with Derived Column Transformation in SSIS package or replace feature in notepad++, then export it to flat file.

The new flat file should look like below:

2014-01-01,00:05:00,,,1,1361,9511,4,15

Then you could use Flat File source gain for this new file to extract 9 columns with comma delimiter.

If I misunderstand your requirement, please correct me.

Best Regards,

Pirlo Zhang

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Even using semicolons as delimiters, it looks like you only have 7 columns.  There's commas only on two lines, are those supposed to represent another delimiter?

The best thing you can do is go to whoever is providing the source file and ask questions about the format, and also to see if changes can (or should) be made.  What columns are supposed to be in the file?  Are semicolons the correct delimiter?  What's going on with the comma on the two rows?  If it's supposed to be a delimiter then why not use a semicolon, and why isn't it on all rows?