| Author |
Topic |
|
aakcse
Aged Yak Warrior
570 Posts |
 Posted - 2009-07-13 : 07:52:55
|
| Hi All, I am facing a problem with csv file column inv containing data like 100000000000 (column taken as string DT_STR size 250 in ssis) in table it is coming as 1.00 E + 11how ever I want it as 100000000000, as in raw file.There are 49 such file and I am using for each loop counter to uploadthere are few such records in all of the source file.kindly help |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-13 : 08:03:24
|
| What datatype are you tring to insert this value into? |
 |
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2009-07-13 : 08:25:01
|
| varchar(250) |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2009-07-13 : 08:55:57
|
| any updates on this, do I need to import it to access and then reimport to using ssis..however this will take time.. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-13 : 09:20:39
|
| Sorry, been busy working for a living. How are you importing it currently? However you are doing it is trying to convert it to a float, so you may need to convert it implicitly or if you are using BCP, use a format file. |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2009-07-13 : 09:21:50
|
| I found the below after googling, however how to add the tab char in csv file.. plz help..lets give a try to this,, I have tried importing it into text field, no gain..I've come up with a sort of a work-around for the issue of Excelconverting text fields in a CSV file to an exponential number when youopen it without going through the text import wizard.If you append a tab character (ASCII 9) to the end of the field, rightbefore the comma, Excel will import the field as text. You still havea tab character at the end of the field, but you can't see it. If youcursor past it in the edit bar, you have to press the cursor key toget past it twice and if you copy and paste the field into a texteditor, the tab will come with it. But if all you want to do is lookat it, there isn't a problem.I've tried this only with Excel 2003. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-13 : 09:51:04
|
| You have to do this manually. Why are you even going near Excel?You didn't answer my question. How are you importing the data? What method are you using to get the CSV file into SQL Server? |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2009-07-13 : 09:54:58
|
| I am using SSIS for import. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-13 : 11:09:27
|
| Do you need to use SSIS? i.e. are you doing anything else with the data, or could you just use BULK INSERT to get the data into a table? Also, you could run an OPENROWSET query to get the data from the CSV and still use SSIS? Othwise you neeed to convert that field to the length you want it. |
|
|
|
|
exponent problem with csv file