importing fixed width files with 80 fields


Here is the situation. Using the import tool, I have to define 80 fields in order to import the flat file data into my db table. I problem is that I have to define 80 fields for my fixed width file. Do any of you have a better and quicker way of doing this?
LVL 1
jorbroniAsked:
Who is Participating?
 
pivarConnect With a Mentor Commented:
Hi,

You could create an schema.ini file, see
http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353(v=vs.85).aspx
so you don't have to recreate the definition every time.

Example schema.ini:

[test.txt]
Format=FixedLength
ColNameHeader=False
MaxScanRows=0
Col1=col1 Text Width 4
Col2=col2 Text Width 4
Col3=col3 Text Width 4

Open in new window


I'm not sure if it works with flat file source though, but if you have a text-driver that might work.

Otherwise you could run an INSERT like this

INSERT INTO yourtable
SELECT * 
FROM   OPENROWSET('MSDASQL', 
   'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=d:\;', 
   'SELECT * FROM test.txt;' 
) 

Open in new window


/peter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.