| Author |
Topic |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2008-04-01 : 17:54:10
|
| Hello all,We have been trying now for the past 2 days to import data from a flat file to sql server database but with no luck.The real issue here is that one of the field names has a very long value.As a result, the import fails because it is unable to truncate the value.We really don't want the value truncated but we have not been able to import the entire data file.We have used nvarchar(max) but it doesn't work.Can someone please let me know if you have encountered this type of issue and how was it resolved?Thanks in advance. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-01 : 19:23:36
|
| check bulk insert in BOL. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-01 : 23:03:09
|
| Did you get any error? |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-04-02 : 08:32:52
|
| Thanks to both of you for your response.sodeep,I used Bulk Insert like you suggested but I still have see some truncation.For instance, on the txt file, I have one record that says: 187234_perald_Lee_jones.pdfAfter the import, it now says: perald_Lee_jones.pdf. The 187234 was completely removed.I am not sure why that is happening.rmiao,Yes, when I used dts with sql 2005, the import would fail indicating that there was an error in truncation but the truncation error was with another field.So, I am really stumped by this.Thanks for the assistance.Any further assistance would be greatly appreciated. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-02 : 09:12:45
|
| How did you do the bulk insert and how big is the column for that field.What you have there is not very big and shouldn't cause a problem.I suspect there's something wrong with your file - invalid data or incorrect delimiters.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-04-02 : 15:45:16
|
| hi nr,This is the bulk insert I used:BULK INSERT dumpTest FROM 'e:\txtFilder\test.txt' WITH ( FIELDTERMINATOR = 't', ROWTERMINATOR = '\n' )and I gave the field name nvarchar(MAX).And I agree with you that that value is not big enough at all.So, I don't know what else to do.Thanks for your assistance. |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-04-02 : 15:48:16
|
| I forgot to indicate that the values in the textfile are tab delimited. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-02 : 22:53:25
|
| Should use FIELDTERMINATOR = '\t' for tab delimitor. |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-04-03 : 08:38:25
|
| that's what I used.I mistakenly left out the \ as in '\t' as I was copying and pasting.I tend to lean more towards nr's response that something is corrupt somewhere.What I would like to know is whether what is corrupt is the flat file that I am extracting data from. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-03 : 22:36:57
|
| >> ROWTERMINATOR = '\n'Ok, tried with ROWTERMINATOR = '\r\n'? |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-04-04 : 08:42:15
|
| I try it with '\r\n' but only 1 row or records is inserted instead of almost 300,000 records |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-05 : 22:13:48
|
| You should double check row delimiter in source data file. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-04-07 : 10:31:43
|
| You may have a text file with one kind of row delimiter on certain rows but other kind on other rows.Here are what I will do, starting from the easiest:1) Ask source file provider about the definition/schema of the file;2) Check the program that creates these files.3) Try to figure it out in some kind of text editors such as UltraEdit.4) Do the trial and error way in SSIS/DTS by cutting down the text file into smaller pieces, row wise or column wise, and preveiw them as you go. |
|
|
|
|
Struggling to import data from flat file to sql db