Alex A
asked on
SSIS: loading vertical-bar delimited files
Hi,
I am loading pipe (vertical bar) delimeted text files into SQL Server tables.
The actual data shouldn't have | character , however, it happens from time to time. And the file cannot be loaded unless we manually correct the data.
I would appreciate any ideas on how to improve loading process.
Thank you in advance.
I am loading pipe (vertical bar) delimeted text files into SQL Server tables.
The actual data shouldn't have | character , however, it happens from time to time. And the file cannot be loaded unless we manually correct the data.
I would appreciate any ideas on how to improve loading process.
Thank you in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<Somewhat redundant with the above posts>
I had that happen on a project once where emails were stored in SQL columns, and many email signatures had pipes in them such as My Name | My Title | My Phone number.
We were able to go back to the client and convince them to do a tilde ( ~ ) delimeted file, and problem solved. Hilarity ensued when they first balked and we asked them which Magic 8-ball we should use to interpret the pipes in signature blocks vs. delimeter pipes.
The additional five grand estimated by me to pull this off was icing on the cake. Anytime you can pit your own sales executives against clients who treat everything as fixed-bid, it is well worth the effort.
I had that happen on a project once where emails were stored in SQL columns, and many email signatures had pipes in them such as My Name | My Title | My Phone number.
We were able to go back to the client and convince them to do a tilde ( ~ ) delimeted file, and problem solved. Hilarity ensued when they first balked and we asked them which Magic 8-ball we should use to interpret the pipes in signature blocks vs. delimeter pipes.
The additional five grand estimated by me to pull this off was icing on the cake. Anytime you can pit your own sales executives against clients who treat everything as fixed-bid, it is well worth the effort.
So quasar_ee, how's it going? Lots of good advice here.
just some additional questions:
- which SQL Server version are you targeting/using?
- Do you have the chance to change the generation of the flat file to implement text qualifiers?
Meaning from this
1|Just a text with an additional | separator inside|2013-10-21
to get that
1|"Just a text with an additional | separator inside"|2013-10-21
(using any other unused character to encapsulate entries which contain the delimiter character)
Thanks.
Rainer