August 7, 2011 at 11:59 am
NUM FNAME LNAME COUNTRY CITY centuries special wickets runs highest against odi Test
100 sachin Tendulkar india mumbai 100 batsman 50 40000 200 south africa 380 200
101 ganguly sourav india kolkatta 75 batsman 45 35000 189 southafrica 280 175
102 gilicrist adam australia sydney 70 batsman 10 32000 179 pakistan 320 187
103 jayasurya sanath srilanka columbus 75 batsman 100 35000 189 australia 325 200
104 sehwag virendar india delhi 70 batsman 35 30000 175 england 240 169
105 Rahul Dravid india banglore 75 batsman 5 34000 155 westindies 300 200
106 yuvraj singh india punjab 70 batsman 40 32000 168 australia 280 167
107 dhoni mehendra india jarkhand 60 batsman 3 25000 186 srilanka 180 98
actual this is in fixed width format flat file, am trying to load in to table using import export , but i am not able to do . So will any one help that would be great. Any one give me a suggestions hoe to do.
August 7, 2011 at 12:37 pm
Hi sravan
I got the same problem....i have to insert millions of rows in to a table from a fixed width flat file...i have tried it through Import & Export wizard.....i encounter a problem at column alligning...(i.e. inputcolumnwidth and outputcolumnwidth..) ...please help me folks ...am in rush...
August 7, 2011 at 1:13 pm
Couple options...
BCP in
bulk insert
SSIS package.
What have you tried and where are you getting stuck?
August 7, 2011 at 2:47 pm
sravnmaganti
Does the data below, accurately display the data in your flat file?
NUM FNAME LNAME COUNTRY CITY centuries special wickets runs highest odi Test
100 sachin Tendulkar india mumbai 100 batsman 50 40000 200 south africa 380 200
101 ganguly sourav india kolkatta 75 batsman 45 35000 189 southafrica 280 175
102 gilicrist adam australia sydney 70 batsman 10 32000 179 pakistan 320 187
103 jayasurya sanath srilanka columbus 75 batsman 100 35000 189 australia 325 200
104 sehwag virendar india delhi 70 batsman 35 30000 175 england 240 169
105 Rahul Dravid india banglore 75 batsman 5 34000 155 westindies 300 200
106 yuvraj singh india punjab 70 batsman 40 32000 168 australia 280 167
107 dhoni mehendra india jarkhand 60 batsman 3 25000 186 srilanka 180 98
If you would post the table definition for the table that the data is to be inserted into, it would assist those who want to assist you with a tested solution. To see how to post the table definition in a easily consumable format, please click on the first link in my signature block to learn how to do so. (Note the article has sample T-SQL to help you in doing just what I have asked of you).
August 7, 2011 at 7:11 pm
this is the accurate display of the table, and i created the table structure in database. What the problem is when i browse the flat file, after making connection string. When i want to preview the file i am displaying with only one column.
August 7, 2011 at 7:23 pm
I've seen the same issue over an dover when importing in access or excel.
Are you sure you selected the fixed width option instead of delemited (God only knows how many times I did that error!).
August 7, 2011 at 8:03 pm
i selected fixed width option, but i am displayed with only one column in preview
August 7, 2011 at 8:06 pm
I'm off to bed now. But if you could send a screen shot of what you did / and see it would be very helpful to solve this.
TIA.
August 8, 2011 at 6:24 am
Make sure you chose the correct end of line character(s). If the import wizard is looking for <CR><LF> and your data file is delimited by only <LF>, the import will only see this as one big long line.
August 8, 2011 at 6:29 am
sqlmaverick (8/7/2011)
i selected fixed width option, but i am displayed with only one column in preview
Yes, because you then have to go and specify where the columns are (SQL can't sense it with a fixed-width file), you add and move column markers (it's either that screen or the next) and it's mentioned on the screen in question.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2011 at 2:41 pm
I created new flat file connection manager, in the format selected "Fixed width".
Selected {CR}{LF} for header row delimiter.
Under Advanced:
Clicked on New and then added the cols as follows:
Col1 - Inputcolumnwidth:10 and outputcolumnwidth:10; TextQualified: False
Col2 - InputColumnWidth:7 and OutputColumnWidth: 7; TextQualified: False
Col3 - InputColumnWidth:2 and OutputColumnWidth:2; TextQualified: False
..
....
When I loaded the data into the table it did'nt load like I specified in the above InputColumnwidth. Please let me know what I should do to resolve this issue.
Thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply