| Author |
Topic |
|
voyager838
Yak Posting Veteran
90 Posts |
 Posted - 2010-04-11 : 16:51:40
|
| How do i import from an textfile that has the follow syntax:data.txt:LogTime \t field1 \t field2 \r\n2010-01-01 13:00:00 \t 13,141 \t -31,12(quick note: \t here means tab-separated fields (0x09)\n here mean 0x0A and \r is 0x0D)...The table isCREATE TABLE dbo.[MyTable](LogTime datetime,field1 float null,field2 float null)And for last the bulk insert command:BULK INSERT MyTable FROM 'c:\Data\data.txt' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' )Simply it doesn't work, It gives error like:"Bulk load data conversion error (type mismatch or invalid character for the specified codepage...."so what am i doing wrong here, or have missing?Thanks!sincerelyV |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2010-04-11 : 18:52:03
|
| HiIt doesn't seems to matter at all. I still got the conversion error.I discovery that it doesn't work when it use type float or real here, of some reason.I guess i have to specify a fmt file? (which i did hope to avoid, but...)Or could the server still give an suggestion type automatically ?/C |
 |
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2010-04-11 : 19:07:09
|
| tkizer:Another things did shows up nowYou can use float type! BUT only if you use '.' instead of ',' in the numeric value!But, how do i let it use ',' instead of '.' ?example)LogTime \t field1 \t field2 \r\n2010-01-01 13:00:00 \t 13,141 \t -31,12which now will have to be like:LogTime \t field1 \t field2 \r\n2010-01-01 13:00:00 \t 13.141 \t -31.12In this case its ok to do it manually, but it is devastating when you have over 200 fields and atleast over thousand records!what can be done in small steps in the bulk import (if it still can) for use ',' instead of '.' to an numeric value like this case?Thanks again/C |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2010-04-13 : 09:13:34
|
quote:
Originally posted by tkizer
13.141 is not the same thing as 13,141. One is 13 and the other is 13 thousand.
Maybe it is to you, but there are plenty of locales where comma is the normal decimal separator. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-13 : 09:56:40
|
quote:
Originally posted by Arnold Fribble
quote:
Originally posted by tkizer
13.141 is not the same thing as 13,141. One is 13 and the other is 13 thousand.
Maybe it is to you, but there are plenty of locales where comma is the normal decimal separator.
Word!Certainly causes about 1 problem a month for us with one of our Spanish clients.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2010-04-13 : 18:37:24
|
| Yes, it seems very common somehow.It causes us atleast 5 problems a month (so i win i guess ;-) )@tkizer: No sry sir, i cant do it manually , it needs to be done automatically unfortunate.I dont want to change over millions of data by hand, im not that good handwriting-mode right now :)Have you guys a hint or tip what can be done?Thx! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-14 : 04:10:39
|
| There's a common technique where you import your file into a staging table all as VARCHAR types. Then you can so your formatting in sql and insert into the real table from there. Something to consider?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2010-04-17 : 12:35:51
|
| Thank you both, for answering!Yes i will test both ideas and see which gives the best performance.If i have time i maby can benchmark the result!Will see.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-17 : 15:13:34
|
quote:
Originally posted by tkizer
Interesting, didn't know that. Commas are only used here to visually separate the data 3 values at a time.
Lots of interesting stuff on =Wikipedia as to why Period (USA) / Decimal point (UK & its Colonies) (a dot aligned vertically with the middle of the line) evolved, plus why the comma was used in France (Period was used in "print" to clarify Roman Numerals)Furthermore, whilst USA/UK use the comma to separate the Thousands (and France et al use the period instead) other countries use narrow spaces, apostrophes and all sorts ... and in India they have a counting unit of 100,000 (lacs) so they write that as 1,00,000 and another unit for 100 lacs [a crore] which is 10,000,000 (in UK/USA format) but which I believe is written as 1,00,00,000 - very confusing!The Japanese have a unit for 10,000 (a "Man") so they talk about "100 Man", which is 1 million in USA/UK parlance, which I found very hard to get my head around when I was living there. But I don't remember how they used commas in the numbers (Wikipedia suggest that both 10,000,000 and 1,000,0000 are valid in Japan!I'll get my coat now ...  |
|
|
|
|
BULK INSERT with differnt datatypes