| Author |
Topic |
|
dhk11
Starting Member
6 Posts |
 Posted - 2007-05-03 : 12:23:48
|
| Problem:Our business process will be receiving comma delimited text files (.csv), which contain some amount fields that use a double quote for text qualification when the amount is greater than 999.99. Here is an example:DATE,Batch Type,DAmount,Count4/2/07,Adjustment,“4,013.81”,164/2/07,Adjustment,013.81,10Question:How does one use a format file to process fields that only have text qualifiers when necessary, such as in the example above?Note: When one opens the file using Excel, fields are automatically converted; when one uses a DTS package to import the file using the double quote text qualifier option the file imports without error. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-03 : 12:26:59
|
| best bet is to insert into a staging (temporary) table and then process data from there (remove the " etc...)_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
dhk11
Starting Member
6 Posts |
Posted - 2007-05-03 : 12:33:49
|
| Removing the quotes would introduce a new problem, which would be that those amounts greater than 999.99 would have commas in the field causing a process using comma delimitation to see these as columns rather than part of the field contents. So, if I were to send the file to a temp table, I would basically have to write logic to cursor the data. Seems like there has to be an easier and more efficient method! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-03 : 12:39:38
|
| I think what Mladen is suggesting is to remove the quotes AFTER you get the data into a staging/temp table, using an UPDATE with REPLACE.Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
dhk11
Starting Member
6 Posts |
Posted - 2007-05-03 : 12:46:37
|
| so now the temp table has the following:DATE,Batch Type,DAmount,Count4/2/07,Adjustment,4,013.81,164/2/07,Adjustment,013.81,10Notice 4,013.81,4 is now a new column |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-03 : 12:52:48
|
| If you use comma as the delimiter the entire “4,013.81” will be in the cell. Then you run an update to remove the quotes.Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
dhk11
Starting Member
6 Posts |
Posted - 2007-05-03 : 13:30:58
|
| I was thinking that if I use comma as the delimiter withou the double quote qualifier on the initial import, the entire "4,013.81" would not be in the amount column because the process would ignore the double quotes around "4,013.81" and see the comma in the field as a new column. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-03 : 13:35:40
|
| you are right, the value would get cut off at the number 4. I dont think there is any way to massage the data without having to change the way the CSV is being created. Instead of a comma if the file is created with a pipe delimiter it might help.Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
dhk11
Starting Member
6 Posts |
Posted - 2007-05-03 : 13:48:07
|
| Thank you for trying Dinakar It's funny how the DTS Import package has the double quote text qualifier, which handles this issue without error. I have considered passing the file name from a stored procedure to the DTS package, but have not quite figured out how to do this using the global variables within the DTS package. So far, I have created command lines to execute the package:--EXECUTE THE DTS PACKAGE TO IMPORT FILE TO BULK INSERT TABLESET @NString = 'dtsrun /S servername /U UserName /P password /N BulkInsertFND009'exec master..xp_cmdshell @NString |
|
|
|
|
Bulk Insert using format file (with comma delimita