March 3, 2016 at 1:08 pm
This sounds very basic, but it's a hard problem. However, we are severely limited on what tools we can run in our environment, and there are additional constraints, the Oracle query produces 500+ columns of data and is saved as a valid RFC 4180 compliant CSV file. SQL Server Data Import tool, BCP, BULK INSERT, and SSIS cannot interpret RFC 4180 compliant CSVs with the following:
1. Double-quoted text qualifiers (MSSQL just 'trims' them, does not recognize the actual text as a non-parsed token);
2. Within quoted text, field separators (e.g., commas) are still parsed as field separators;
3. Within quoted text, row separators (e.g., \r) are still parsed as row separators;
4. Within quoted text, escaped double quotes are not unescaped.
Finally, we cannot utilize network connections, we must perform file transfers. The one upside is that if there is a tool that we know, absolutely, is RFC 4180 compliant and can handle 500+ columns, and will import data into SQL Server, we can get it approved and use it. I'm at my end's whit finding this tool. Any help you can provide would be much appreciated.
March 3, 2016 at 1:54 pm
I've managed to solve the first 3 issues using format files. The fourth issue can be solved by cleaning the data in a staging table using T-SQL (REPLACE(ColumnName, '""', '"')).
For more information on how to use format files, check the following: http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/
March 3, 2016 at 3:36 pm
To build the format file I would have to manually configure based off of custom field separators (per field), i.e., one of these four conditions:
",
,"
","
,
That's for 500+ columns. That's simply not a route I'm going to take. I'm shocked this is the only viable solution with the given Microsoft toolset.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply