March 12, 2009 at 4:00 pm
I'm using sql syntax like the following:
CODE
select *
from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\MyDirectory\',
'select top 1 * from MyFile.csv')
This works well.
Except for the case that I sometimes have numeric entries in the '.csv' file that have thousand separators so perhaps I have lines like:
(Suppose the first line is the field/column names in this instance)
CustomerCode,CustomerName,Balance
CUST1,Customer 1,250
CUST2,Customer 2,"1,000"
CUST3,Customer 3,400
When I run the above SQL syntax I get the result set
CUST1 | Customer 1 | 250
CUST2 | Customer 2 | NULL
CUST3 | Customer 3 | 400
So the value with the thousand separator in it ("1,000") is being interpreted as a NULL in my result set.
Can anyone suggest how I can get the required value of 1000?
Is there some property I can set that will enable me to do this?
Thanks in advance.
Steve
March 12, 2009 at 4:09 pm
Flat files are a right pain in the preverbial but one issue here is that I can see is that if you are quote identifying then all columns in all rows need to be quote identified.
You mentioned Excel in the title. You might be beter off using xls rather than csv and ensuring that the cell data types for the rows are explicitely defined.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
March 12, 2009 at 4:22 pm
The issue becomes a little more complicated unfortunately - I kept the example simple. 🙂
The file I'm importing from is of unknown format (i.e. field list is not constant).
So I'm using the 'select * from mtfile.csv' as I'm not sure of the field list - so can't explicitly state them in the sql.
And unfortunately my hands might be tied in regards to the usage of '.csv' files.
Can this issue not be worked around?
Any further suggestions would be greatly appreciated.
Thanks again.
March 12, 2009 at 4:43 pm
OK, I have had this where I have no control over the data format I am receiving.
In the end I had to import the thing as a single text row into a staging table then curse through each row in that, string parsing and counting the columns to determine how to insert them into the real table.
Mine was compounded by there being carriage returns in some columns and runs like a 3 legged cow with concrete shoes during a freak flood of marmite but it's a nightly task and we have been promised better data soon for the last couple of years so...
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
March 12, 2009 at 4:45 pm
Yikes!! I feared I'd have to do something heavy (like that).
Can anyone else advise if there's any other means to achieve the initial goal with this?
March 12, 2009 at 5:15 pm
Well, thinking about it, have you tried making an SSIS import task for it so that you can specify datatypes for the column?
Have a play with the Import Data from all tasks in SSMS and when it works asve as an SSIS task that you can schedule.
Doesn't work for mine due to the carriage returns but may for yours.
One thing to beware of is that the drivers for flat files and Excel take the first few rows and use them to estimate the column format. Even specifying them in SQl server doesn't change the way the import driver recognises them. One way I got round that on another project is to insert several dummy rows at the start with the requred format and remove them after. Only just occurred to me that this might apply.
You could have a file with
CUST-1,Customer -1,"1,000"
CUST-2,Customer -2,"1,000"
CUST-3,Customer -3,"1,000"
Use xcopy to merge that with the incoming one then try importing the result (I used minuses to indicate you want to use IDs that will not interfere but as long as you stick within the contraints of the tables and can delete the dummies (or again use a staging table but without the need for parsing) then it should be swift and less hassle than the rbar.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply